You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Jerry Malcolm <2n...@gmail.com> on 2012/01/26 16:46:38 UTC
connection autoReconnect?
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
Re: connection autoReconnect?
Posted by Arnaud Kleinveld <ar...@ybo.com.sg>.
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.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: connection autoReconnect?
Posted by Arnaud <ar...@ybo.com.sg>.
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.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: connection autoReconnect?
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Arnaud,
On 11/20/12 4:23 AM, Arnaud wrote:
> 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.
> 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.
> 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.
> As far as we know the autoReconnect is a jConnector parameter and
> only applies to MySQL.
That is correct. The product is Connector/J.
> 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.
> If you do not set validationQuery than testOnBorrow will simply not
> run.
This is clear from the DBCP documentation.
> 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.
> 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.
> 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
-----BEGIN PGP SIGNATURE-----
Version: GnuPG/MacGPG2 v2.0.17 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with undefined - http://www.enigmail.net/
iEYEARECAAYFAlCr+28ACgkQ9CaO5/Lv0PAG4gCfS225GnljSNwwAaOuVMuRqWD1
umEAnAuYOb5YML3B4Vbxvcj4cjm/Wfu9
=Y/Ij
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: connection autoReconnect?
Posted by Arnaud <ar...@ybo.com.sg>.
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.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: connection autoReconnect?
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Jerry,
On 1/28/12 8:32 PM, Jerry Malcolm wrote:
> 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.
Try "validationQuery". Precision counts.
> 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.
> 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.
> 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
-----BEGIN PGP SIGNATURE-----
Version: GnuPG/MacGPG2 v2.0.17 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAk8oVh0ACgkQ9CaO5/Lv0PB29wCgvnS0K0wEWljXyoegypnthtZw
+d4AoKtGtseydVs1U8THTqd+l/SJJLTW
=4sWz
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: connection autoReconnect?
Posted by Mark Eggers <it...@yahoo.com>.
Answers and comments are inline (mostly).
----- Original Message -----
> From: Jerry Malcolm <2n...@gmail.com>
> To: Tomcat Users List <us...@tomcat.apache.org>; Mark Eggers <it...@yahoo.com>
> Cc:
> Sent: Saturday, January 28, 2012 5:32 PM
> Subject: Re: connection autoReconnect?
>
> 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"/>
>
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.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: connection autoReconnect?
Posted by Jerry Malcolm <2n...@gmail.com>.
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
Re: connection autoReconnect?
Posted by Mark Eggers <it...@yahoo.com>.
----- Original Message -----
> From: Jerry Malcolm <2n...@gmail.com>
> To: Tomcat Users List <us...@tomcat.apache.org>
> Cc:
> 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/
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: connection autoReconnect?
Posted by "Caldarale, Charles R" <Ch...@unisys.com>.
> From: Jerry Malcolm [mailto:2ndgenfilms@gmail.com]
> 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.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: connection autoReconnect?
Posted by Jerry Malcolm <2n...@gmail.com>.
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
On Thu, Jan 26, 2012 at 11:45 AM, Daniel Mikusa <dm...@vmware.com> wrote:
> On Thu, 2012-01-26 at 07:46 -0800, Jerry Malcolm wrote:
> > 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 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.
>
> >
> > 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
>
>
> >
> > It appears that autoReconnect is no longer working. Has the
> configuration
> > syntax changed for autoReconnect since 5.0?
> >
> > Thx
> >
> > Jerry
>
Re: connection autoReconnect?
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Dan,
On 1/26/12 12:45 PM, Daniel Mikusa wrote:
> 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...
> 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.).
My recommendation is to stop using it, and instead use:
>> 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
-----BEGIN PGP SIGNATURE-----
Version: GnuPG/MacGPG2 v2.0.17 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAk8hnuIACgkQ9CaO5/Lv0PB7qACcCObIxAcTCk/FNHEhsZP/xqzL
d4sAoIF3ay+0lORb7l12c5J/tUPZj6R6
=OcHQ
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: connection autoReconnect?
Posted by Daniel Mikusa <dm...@vmware.com>.
On Thu, 2012-01-26 at 07:46 -0800, Jerry Malcolm wrote:
> 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 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.
>
> 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
>
> It appears that autoReconnect is no longer working. Has the configuration
> syntax changed for autoReconnect since 5.0?
>
> Thx
>
> Jerry