connection autoReconnect?

[Jerry Malcolm]

I migrated to Tomcat 7.0 a couple of months ago on several servers. Ever
since moving to 7, I periodically get the following exception on MySql
calls on all of my 7.0 servers:

Exception: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The
last packet successfully received from the server was 71,072,547
milliseconds ago. The last packet sent successfully to the server was
71,072,578 milliseconds ago. is longer than the server configured value of
'wait_timeout'. You should consider either expiring and/or testing
connection validity before use in your application, increasing the server
configured values for client timeouts, or using the Connector/J connection
property 'autoReconnect=true' to avoid this problem.

My data source configuration is:

<Resource name="jdbc/xxxxxxxxxxxxxx.com" auth="Container"
type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000"
removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true"
username="xxxxxxx" password="xxxxxxx"
driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://
127.0.0.1/xxxxxxxxx?autoReconnect=true"/>

I realize that, yes, the 71k seconds is greater than the 10k maxWait and I
could increase maxWait. But that would not be fixing the problem. The
exception text says to use autoReconnect=true. But I already have
autoReconnect=true, and I'm certain this was working back on 5.0 (I went
from 5.0 to 7.0).

I also know I could (and probably should) add defensive code to check for
connection validity. But again, this all worked fine for years on 5.0.

It appears that autoReconnect is no longer working. Has the configuration
syntax changed for autoReconnect since 5.0?

Thx

Jerry

[Daniel Mikusa]

Post by Jerry Malcolm
I migrated to Tomcat 7.0 a couple of months ago on several servers. Ever
since moving to 7, I periodically get the following exception on MySql
Exception: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The
last packet successfully received from the server was 71,072,547
milliseconds ago. The last packet sent successfully to the server was
71,072,578 milliseconds ago. is longer than the server configured value of
'wait_timeout'. You should consider either expiring and/or testing
connection validity before use in your application, increasing the server
configured values for client timeouts, or using the Connector/J connection
property 'autoReconnect=true' to avoid this problem.
<Resource name="jdbc/xxxxxxxxxxxxxx.com" auth="Container"
type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000"
removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true"
username="xxxxxxx" password="xxxxxxx"
driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://
127.0.0.1/xxxxxxxxx?autoReconnect=true"/>
I realize that, yes, the 71k seconds is greater than the 10k maxWait and I
could increase maxWait. But that would not be fixing the problem. The
exception text says to use autoReconnect=true. But I already have
autoReconnect=true, and I'm certain this was working back on 5.0 (I went
from 5.0 to 7.0).

I agree that it looks like you have specified autoReconnect correctly,
but I'm no expert there.

I believe that autoReconnect is a feature of the JDBC driver, so you
might want to check with the MySQL folks to see why that is not working.

Post by Jerry Malcolm
I also know I could (and probably should) add defensive code to check for
connection validity. But again, this all worked fine for years on 5.0.

Have you tried using the validation offered by the connection pool?

If you set "testOnBorrow=true" and "validationQuery=SELECT 1", the pool
should validate connections without requiring any code changes to your
application.

See commons-dbcp configuration docs.

https://commons.apache.org/dbcp/configuration.html

Dan

Post by Jerry Malcolm
It appears that autoReconnect is no longer working. Has the configuration
syntax changed for autoReconnect since 5.0?
Thx

[Christopher Schultz]

Dan,

Post by Daniel Mikusa
I agree that it looks like you have specified autoReconnect
correctly, but I'm no expert there.

+1

autoReconnect has been specified properly, but you have to understand
what it does: autoReconnect causes a failure for the connection
attempt and throws an exception, but also re-connects after the
exception. I think that's totally stupid behavior, but it's what's
documented and actually what happens, so...

Post by Daniel Mikusa
I believe that autoReconnect is a feature of the JDBC driver, so
you might want to check with the MySQL folks to see why that is not
working.

+1

See above. The Connector/J folks both highly recommend against using
autoReconnect
(http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html):

"
autoReconnect:
[...]
The use of this feature is not recommended, because it has side
effects related to session state and data consistency when
applications don't handle SQLExceptions properly, and is only designed
to be used when you are unable to configure your application to handle
SQLExceptions resulting from dead and stale connections properly.
"

and at the same time, obviously recommend using it (based upon the
exception detail message you got, plus all the documentation for
clustering and failover, etc.).

Post by Daniel Mikusa

Post by Jerry Malcolm
I also know I could (and probably should) add defensive code to
check for connection validity. But again, this all worked fine
for years on 5.0.

Have you tried using the validation offered by the connection
pool?
If you set "testOnBorrow=true" and "validationQuery=SELECT 1", the
pool should validate connections without requiring any code changes
to your application.

+1

This is a much better way to do things: DBCP can handle everything for
you, here.

A better validation query is "/* ping */SELECT 1" because newer
versions of the Connector/J driver do a low-effort "ping" to the
server[1] instead of actually issuing an SQL query and all the
overhead that entails. Sure, "SELECT 1" doesn't exactly have a high
cost compared to the query you are likely about to run, but every
little bit of performance helps.

- -chris

[1]
http://dev.mysql.com/doc/refman/5.5/en/connector-j-usagenotes-troubleshooting.html#qandaitem-22-3-11-1-12

[Jerry Malcolm]

Thanks to all for the assistance. I'll try adding those parameters. But
I'm always a bit nervous about possibly just band-aiding a problem that I
don't really understand.

Regarding the defensive coding approach, this error often occurs on the sql
query by the internal TC security authentication code that looks up ids and
passwords for login. I don't own that code. I would think any required
defensive code would be already present in base TC code, yet it's failing
there as well. Basically, if I could figure out how to defend against this
in my code, there's still nothing I can do, coding-wise, to defend against
it in a security auth call to sql, right?

Bear with me for minute and make sure I really understand basically how the
connection pooling works. Basically, TC grabs a handful of connections and
opens them up. Then when an app needs one, a random connection is provided
from the pool. As long as we're hitting all of the connections often
enough, they never time out. But when things get dormant for a while
connections time out and close. And in TC 5, if the luck of the draw gave
me a closed connection, autoReconnect would fire it back up so that I (and
the auth code) would always get a good connection. Apparently, from
interpreting this exception, I am now getting expired connections that are
not reconnecting. This is troubling. My code never did anything to start
up all of those connections to begin with. But for some reason now, I've
got to write code to check each connection I get back to see if it's
connected. If it's not connected, am I just supposed to throw it back into
the pool and try another one, hopefully finding at least one that is still
connected? (seems like sooner or later, there'll be none that work) Or am
I now supposed to make the connection call myself to try to reconnect it?
It just fundamentally seems wrong that the burden should fall on my code to
reconnect it if it timed out.

This started failing regularly on every application on every one of my
servers immediately after upgrading 5.0 --> 7.0 on otherwise untouched
webapp code and an untouched MySql connector where everything had worked
rock solid for years. I'm just very nervous about adding a bunch of
parameters and changing up things even more strictly on a "you might try
this' approach when nobody can really explain the problem. I'll go ahead
and try the new parameters just because I've got to work around this
someway. But I'm sure hoping somebody can tell me what's really changed to
break this.

Thx again

Jerry

Post by Jerry Malcolm

Post by Jerry Malcolm
I migrated to Tomcat 7.0 a couple of months ago on several servers. Ever
since moving to 7, I periodically get the following exception on MySql
Exception: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The
last packet successfully received from the server was 71,072,547
milliseconds ago. The last packet sent successfully to the server was
71,072,578 milliseconds ago. is longer than the server configured value

of

Post by Jerry Malcolm
'wait_timeout'. You should consider either expiring and/or testing
connection validity before use in your application, increasing the server
configured values for client timeouts, or using the Connector/J

connection

Post by Jerry Malcolm
property 'autoReconnect=true' to avoid this problem.
<Resource name="jdbc/xxxxxxxxxxxxxx.com" auth="Container"
type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000"
removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true"
username="xxxxxxx" password="xxxxxxx"
driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://
127.0.0.1/xxxxxxxxx?autoReconnect=true"/>
I realize that, yes, the 71k seconds is greater than the 10k maxWait and

I

Post by Jerry Malcolm
could increase maxWait. But that would not be fixing the problem. The
exception text says to use autoReconnect=true. But I already have
autoReconnect=true, and I'm certain this was working back on 5.0 (I went
from 5.0 to 7.0).

I agree that it looks like you have specified autoReconnect correctly,
but I'm no expert there.
I believe that autoReconnect is a feature of the JDBC driver, so you
might want to check with the MySQL folks to see why that is not working.

Post by Jerry Malcolm
I also know I could (and probably should) add defensive code to check for
connection validity. But again, this all worked fine for years on 5.0.

Have you tried using the validation offered by the connection pool?
If you set "testOnBorrow=true" and "validationQuery=SELECT 1", the pool
should validate connections without requiring any code changes to your
application.
See commons-dbcp configuration docs.
https://commons.apache.org/dbcp/configuration.html
Dan

Post by Jerry Malcolm
It appears that autoReconnect is no longer working. Has the

configuration

Post by Jerry Malcolm
syntax changed for autoReconnect since 5.0?
Thx
Jerry

[Caldarale, Charles R]

Subject: Re: connection autoReconnect?
Basically, TC grabs a handful of connections and
opens them up.

The Tomcat JDBC pool (not sure about the commons one) opens up whatever is configured for initialSize.

Then when an app needs one, a random connection is provided
from the pool.

Don't bet on it being random. It may well have changed from a round-robin to a LIFO mechanism (that would explain your symptoms).

in TC 5, if the luck of the draw gave me a closed connection,
autoReconnect would fire it back up

No - you would get the exception first, then the connection would be recreated. Likely you never got to the point where autoReconnect kicked in.

But for some reason now, I've got to write code to check each
connection I get back to see if it's connected.

I don't think anyone has suggested anything of the sort. Turn off autoReconnect and use testOnBorrow instead. No changes are needed in your code, just your configuration.

- Chuck


THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL and is thus for use only by the intended recipient. If you received this in error, please contact the sender and delete the e-mail and its attachments from all computers.

[Mark Eggers]

----- Original Message -----

Sent: Thursday, January 26, 2012 2:58 PM
Subject: Re: connection autoReconnect?
T hanks to all for the assistance.  I'll try adding those parameters.  But
I'm always a bit nervous about possibly just band-aiding a problem that I
don't really understand.
Regarding the defensive coding approach, this error often occurs on the sql
query by the internal TC security authentication code that looks up ids and
passwords for login.  I don't own that code.  I would think any required
defensive code would be already present in base TC code, yet it's failing
there as well.  Basically, if I could figure out how to defend against this
in my code, there's still nothing I can do, coding-wise, to defend against
it in a security auth call to sql, right?

How are you doing authentication?

Using a straight JDBCRealm for authentication and authorization really creates a lot of problems.

From the document at: http://tomcat.apache.org/tomcat-7.0-doc/config/realm.html



     The JDBC Realm uses a single db connection. This requires that realm
     based authentication be synchronized, i.e. only one authentication can
     be done at a time. This could be a bottleneck for applications with
     high volumes of realm based authentications.

Also if people don't authenticate for a time, then the database's idle timeout will disconnect the database and no one will be able to log in.

What you need is a DataSourceRealm. This provides the capabilities of a database pool along with the Realm interface for authentication and authorization.

The information on how to set this up is scattered across several documents. Fortunately, there's a Wiki page available:

http://wiki.apache.org/tomcat/TomcatDataSourceRealms


It's based on Tomcat 6, so you should check the particulars against Tomcat 7 before blindly copying configuration components.

. . . . just my two cents.
/mde/

[Jerry Malcolm]

Not good news. I changed every resource statement in server.xml to
something like this:

<Resource* testOnBorrow="true" validateQuery="SELECT
1"*name="jdbc/xxxxxxx" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000" removeAbandoned="true"
removeAbandonedTimeout="60" logAbandoned="true" username="xxxxxxxx"
password="xxxxxxxx" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://127.0.0.1/xxxxxxxx"/>

Zero change. I'm still getting the exact same error message telling me the
connection has expired and I should use autoReconnect to fix it.

First question... is the syntax above correct? (I saw some resource tag
examples that used nested <parameter> tags and other examples that use
attributes on resource tag like above. I couldn't find a definitive
specification to use one over the other. Is the way I have it ok?

Second question.... I like to turn on debug/trace for the connector. But
the connector/j doc lists a ton of parameters for debug, and I don't have a
clue how to set all of them. Can someone just give me a canned config I
can add that'll trace what's going on in the connector?

I'm basically at a loss. If the configuration above is correct, and I'm
still getting expired connections, I don't know what else to do. If indeed
TC 7 changed from round-robin to LIFO, it might explain why it started
hitting stale connections. But that still doesn't explain why
testOnBorrow, validateQuery, and autoReconnect=true don't seem to do
anything on stale connections.

Maybe with some logging and tracing, something will become obvious.

Thx.

Jerry

[Mark Eggers]

Answers and comments are inline (mostly).

----- Original Message -----

Sent: Saturday, January 28, 2012 5:32 PM
Subject: Re: connection autoReconnect?
Not good news.  I changed every resource statement in server.xml to
      <Resource* testOnBorrow="true" validateQuery="SELECT
1"*name="jdbc/xxxxxxx" auth="Container"
type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
removeAbandoned="true"
removeAbandonedTimeout="60" logAbandoned="true"
username="xxxxxxxx"
password="xxxxxxxx" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://127.0.0.1/xxxxxxxx"/>
 

Hopefully the asterisks in your Resource element (<Resource* and *name
are artifacts of your copy and paste. If they're in server.xml, I don't
know if Tomcat would even start. If Tomcat does start, it will probably ignore
malformed XML elements. Check your log files for such messages.

Zero change.  I'm still getting the exact same error message telling me the
connection has expired and I should use autoReconnect to fix it.
First question... is the syntax above correct?  (I saw some resource tag
examples that used nested  <parameter> tags and other examples that use
attributes on resource tag like above.  I couldn't find a definitive
specification to use one over the other.  Is the way I have it ok?

When in doubt, always follow the documentation on the Apache Tomcat
site.

From the documentation:

No components may be nested inside a Resources element

So any documentation that you've read which specifies <parameter> inside
of a Resource element is wrong.

Second question.... I like to turn on debug/trace for the connector.  But
the connector/j doc lists a ton of parameters for debug, and I don't have a
clue how to set all of them.  Can someone just give me a canned config I
can add that'll trace what's going on in the connector?
I'm basically at a loss.  If the configuration above is correct, and I'm
still getting expired connections, I don't know what else to do.  If indeed
TC 7 changed from round-robin to LIFO, it might explain why it started
hitting stale connections.  But that still doesn't explain why
testOnBorrow, validateQuery, and autoReconnect=true don't seem to do
anything on stale connections.

I've had nothing but trouble with autoReconnect="true".

Maybe with some logging and tracing, something will become obvious.
Thx.
Jerry

OK, here's a formatted version of your configuration:

<Resource
testOnBorrow="true"
validateQuery="SELECT 1"
name="jdbc/xxxxxxx"
auth="Container"
type="javax.sql.DataSource"
maxActive="100"
maxIdle="30"
maxWait="10000"
removeAbandoned="true"
removeAbandonedTimeout="60"
logAbandoned="true"
username="xxxxxxxx"
password="xxxxxxxx"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://127.0.0.1/xxxxxxxx"/>

Reordering it so that it follows along with the documentation and adding
the defaults where you've not specified leads to:

<Resource
name="jdbc/xxxxxxx"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.mysql.jdbc.Driver"
username="xxxxxxxx"
password="xxxxxxxx"
url="jdbc:mysql://127.0.0.1/xxxxxxxx"
initialSize="0"
maxActive="100"
minIdle="0"
maxIdle="30"
maxWait="10000"
validationQuery="SELECT 1"
validationQueryTimeout="-1"
testOnBorrow="true"
testOnReturn="false"
removeAbandoned="true"
removeAbandonedTimeout="60"
logAbandoned="true" />
There are a number of things to note here.

You did not set the initialSize value. By default it is 0. This means
that there are no initial connections to the database.

You did not set the minIdle value. By default, it is 0. This means that
if all of your connections are idle, the pool can shrink to 0.

The correct parameter to specify a validation query is validationQuery.
validateQuery is not correct, and should be ignored. You should see a
warning to that effect in your catalina.out logs.

So, I'm guessing that if you use your Resource element with a DataSource
Realm, something like the following might happen. I'm speculating here
since I've not looked at this part of the code.

1. Tomcat starts up and complains about validateQuery
2. A pool is created with NO active connections
3. You use a form-based login and a DataSource Realm to authenticate
4. The DataSource Realm asks the Resource ( via a JNDI name) for a data source
5. The pool says - I don't have one, but I'll create one
6. You have a testOnBorrow="true" so the pool will use the validation query
7. The pool does not have a validation query to run (see notes above)
8. The default time out for a validation query is -1 - infinite
9. The pool never returns

That's my guess.

Either that, or the pool sees that there is no validation query and
returns immediately with no database connection since there is nothing
in the pool to start with and the pool could not perform a validation query.

I would do the following:

1. Fix initialSize and set it to some reasonable number

A reasonable number depends on your application and your application's
usage.

2. Fix minIdle and set it to some reasonable number

A reasonable number depends on your application and your application's
usage.

3. Fix validateQuery to be validationQuery

Monitor the connections.

On the MySQL side you can use MySQL workbench if you don't wish to use
the command line client. I believe the command is SHOW PROCESSLIST; in 
the command line client

You can monitor the number of connections with JMX on the Tomcat side.

. . . . just my two cents.
/mde/

PS - You do not have to cc: me when you send to the list. All that means
is I get two copies of the email.

[Christopher Schultz]

Jerry,

Post by Jerry Malcolm
Not good news. I changed every resource statement in server.xml
<Resource* testOnBorrow="true" validateQuery="SELECT
1"*name="jdbc/xxxxxxx" auth="Container"
type="javax.sql.DataSource" maxActive="100" maxIdle="30"
maxWait="10000" removeAbandoned="true" removeAbandonedTimeout="60"
logAbandoned="true" username="xxxxxxxx" password="xxxxxxxx"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://127.0.0.1/xxxxxxxx"/>
Zero change. I'm still getting the exact same error message
telling me the connection has expired and I should use
autoReconnect to fix it.

Try "validationQuery". Precision counts.

Post by Jerry Malcolm
First question... is the syntax above correct? (I saw some
resource tag examples that used nested <parameter> tags and other
examples that use attributes on resource tag like above. I
couldn't find a definitive specification to use one over the other.
Is the way I have it ok?

<parameter> is from old versions of Tomcat. You were reading old
documentation.

Post by Jerry Malcolm
Second question.... I like to turn on debug/trace for the
connector. But the connector/j doc lists a ton of parameters for
debug, and I don't have a clue how to set all of them. Can someone
just give me a canned config I can add that'll trace what's going
on in the connector?

All those configuration parameters go into the JDBC URL just like
autoReconnect does.

Post by Jerry Malcolm
I'm basically at a loss. If the configuration above is correct,
and I'm still getting expired connections, I don't know what else
to do. If indeed TC 7 changed from round-robin to LIFO, it might
explain why it started hitting stale connections. But that still
doesn't explain why testOnBorrow, validateQuery, and
autoReconnect=true don't seem to do anything on stale connections.

Forget about autoReconnect.

- -chris

[Arnaud]

We have been struggling with this MySQL last packet message for some times as
well. And we are still not sure if we have solved it yet. However we did
discover quite a few things.

We minimized our DBCP configuration to only a few parameters
<Context path="">
<Resource auth="Container" driverClassName="com.mysql.jdbc.Driver"
name="jdbc/retailUser" type="javax.sql.DataSource"
username="xxxxxx" password="xxxxx"
url="jdbc:mysql://server/db"
validationQuery="SELECT 1" testOnBorrow="true"/>
</Context>

That's it. All the other parameters have acceptable default values. The
problem, we think, is actually that with the default configuration you will
open to many connections and not use them within the wait_timeout of MySQL.
So, now and then you will retrieve a connection that has timed out. The
validation query solves this.

As far as we know the autoReconnect is a jConnector parameter and only
applies to MySQL. MySQL says in their documentation that it's the
applications job to handle failed connections. For the step of just getting
the connection DBCP is solving this with the validationQuery="SELECT 1"
testOnBorrow="true" settings. But if it happens in the midst of a
transaction, your application should handle it.

If you do not set validationQuery than testOnBorrow will simply not run.

removeAbandoned only removes connections that you forget to close in your
application. You can use this setting for a while together with logAbandoned
to find your application leaks where you forget to close the connection
after use, but when done you should remove logAbandoned because the logging
adds overhead from every connection borrowed.

Most of this we found at tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
<http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html> and at
dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html
<http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html>



--
View this message in context: http://tomcat.10.n6.nabble.com/connection-autoReconnect-tp4340944p4989787.html
Sent from the Tomcat - User mailing list archive at Nabble.com.

[Christopher Schultz]

Arnaud,

Post by Arnaud
We have been struggling with this MySQL last packet message for
some times as well. And we are still not sure if we have solved it
yet.

You can get the "last packet" message for a number of reasons.
Typically, it's because the connection has gone stale due to firewall
or other connection timeout and the connection has been closed but it
is still in the connection pool.

Post by Arnaud
However we did discover quite a few things.
We minimized our DBCP configuration to only a few parameters
<Context path=""> <Resource auth="Container"
driverClassName="com.mysql.jdbc.Driver" name="jdbc/retailUser"
type="javax.sql.DataSource" username="xxxxxx" password="xxxxx"
url="jdbc:mysql://server/db" validationQuery="SELECT 1"
testOnBorrow="true"/> </Context>
That's it. All the other parameters have acceptable default values.
The problem, we think, is actually that with the default
configuration you will open to many connections and not use them
within the wait_timeout of MySQL.

Sounds to me that the default values aren't acceptable, then.

Post by Arnaud
So, now and then you will retrieve a connection that has timed out.
The validation query solves this.

Correct. For MySQL, you'll want to use "/* ping */ SELECT 1" for your
query: it will be fasted with any reasonably-recent version of
Connector/J which does a lightweight ping to the database instead of
actually issuing the query, fetching the results, etc.

Post by Arnaud
As far as we know the autoReconnect is a jConnector parameter and
only applies to MySQL.

That is correct. The product is Connector/J.

Post by Arnaud
MySQL says in their documentation that it's the applications job to
handle failed connections. For the step of just getting the
connection DBCP is solving this with the validationQuery="SELECT
1" testOnBorrow="true" settings. But if it happens in the midst of
a transaction, your application should handle it.

While true, I can't imagine any database that would
commit-on-connection-failure. Most applications are written to simply
throw a SQLException and either abandon the transaction or re-try it.
In either case, the intended behavior is achieved: no half-performed
transactions are committed.

Post by Arnaud
If you do not set validationQuery than testOnBorrow will simply not run.

This is clear from the DBCP documentation.

Post by Arnaud
removeAbandoned only removes connections that you forget to close
in your application.

This isn't strictly true: the abandonedTimeout specifies a window of
opportunity that the client has to return a particular resource. If
the transaction runs long, the connection can be considered
"abandoned" even if the application eventually closes it.

Post by Arnaud
You can use this setting for a while together with logAbandoned to
find your application leaks where you forget to close the
connection after use, but when done you should remove logAbandoned
because the logging adds overhead from every connection borrowed.

This is also clear from the documentation.

Post by Arnaud
Most of this we found at
tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
<http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html> and at
dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html

<http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html>


DBCP != Tomcat-pool

They can be configured in very similar ways, but tomcat-pool is a lot
more flexible and was designed to be higher-performing than DBCP --
though I haven't independently verified that it is actually faster.

- -chris

[Arnaud]

So, yes indeed. The problem would then be solved by making sure that all
connections are rotated at least once within the MySQL wait-timeout right?
How do we do this? Measure the average concurrent number of connections and
then set the initialSize the same?



--
View this message in context: http://tomcat.10.n6.nabble.com/connection-autoReconnect-tp4340944p4989844.html
Sent from the Tomcat - User mailing list archive at Nabble.com.

[Arnaud Kleinveld]

Ok. The validation query solves this problem we concluded. Confirmed with
testing as well.

Our Resource looks like this now
<Resource auth="Container" driverClassName="com.mysql.jdbc.Driver"
name="jdbc/xxxxxxxx" type="javax.sql.DataSource"
username="xxxxxxxx" password="xxxxxxx"
url="jdbc:mysql://localhost:3306/xxxxxxx"
validationQuery="/* ping */ SELECT 1" testWhileIdle="true"/>

Because of using MySQL we have this specific validation query. Which has to
be exactly lower-case and with spaces as above says the documentation
otherwise it will not work. Get the right validation query if you use a
different type of database.

We chose to run the validation/cleaner thread by setting testWhileIdle
instead of testOnBorrow. Which works fine too and prevents your application
to run the validationQuery every single time when processing high loads.

We have done a test by setting wait_timeout to 90 seconds and reusing the
connections with our application after MySQL had closed them. No exceptions
were thrown. Even within the 5 seconds after the connection had be closed.



--
View this message in context: http://tomcat.10.n6.nabble.com/connection-autoReconnect-tp4340944p4989963.html
Sent from the Tomcat - User mailing list archive at Nabble.com.