5

I am getting the dreaded MySQL JDBC stale connection exceptions:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 243,263,541 milliseconds ago.  The last packet sent successfully to the server was 243,263,541 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.

It seems like everyone agrees that this is fixed by using validationQuery + testOnBorrow, but this is not solving the problem.

I am using the following software MySQL 5.1.41-3ubuntu12.10 Connector/J 5.1.18 Tomcat 6.0.24

Here is how the connection is defined in server.xml, we are using the tomcat-dbcp to pool the connections.

 <Resource
       auth="Container"
       driverClassName="com.mysql.jdbc.Driver"
       factory="org.apache.commons.dbcp.BasicDataSourceFactory"
       logAbandoned="true"
       maxActive="75"
       maxIdle="20"
       maxWait="10000"
       name="jdbc/jndiname"
       password="password"
       removeAbandoned="true"
       removeAbandonedTimeout="60"
       validationQuery="/* ping */SELECT 1"
       testOnBorrow="true"
       testOnReturn="true"
       timeBetweenEvictionRunsMillis="10000"
       testWhileIdle="true"
       scope="Shareable"
       type="javax.sql.DataSource"
       url="jdbc:mysql://host:3306/schema"
       username="username" />
user763648
  • 88
  • 1
  • 7
  • In what situation are you getting those stale connection exceptions? Are you sending a live query, and that is the result? Did you just create the JDBC connection as you did the query, or are you grabbing them from a connection pool? – technocrat Jan 30 '12 at 19:59
  • The connection exceptions happen each morning when a user connects to our web app for the first time. The connections come from the Tomcat-DBCP pool. We can fix the issue by restarting Tomcat each day but that is only masking the real issue. – user763648 Jan 30 '12 at 20:06
  • When you restart Tomcat all you do is make the connection pool set itself back up again. Do you believe the fix to this is going to be tomcat configuration, mysql configuration, or a tomcat-dbcp pool code change?.. Just so I know which direction to look for answers. – technocrat Jan 30 '12 at 22:47
  • And have you viewed this post: http://stackoverflow.com/questions/15949/javatomcat-dying-database-connection – technocrat Jan 30 '12 at 22:49
  • Some more background: in the past we used connector/J version 5.0.8 and encountered the stale connections. At that time, we fixed the issue by using the validationQuery trick. Recently we had reason to upgrade connector/J to 5.1.18 and the stale connection issue returned. Updating Tomcat or tomcat-dbcp is not an option for us. I have looked over every possible server.xml and MySQL variable I can think of and have ran out of ideas. – user763648 Jan 30 '12 at 23:00
  • When you say updating tomcat or tomcat-dbcp, you mean the version tomcat and tomcat-dbcp, or the code which uses tomcat-dbcp? – technocrat Jan 30 '12 at 23:08
  • Yes, I mean we must use the Tomcat 6.0.24 version that is supported by Ubuntu 10.04 LTS. It uses a spinoff of commmons-dbcp 1.2 – user763648 Jan 31 '12 at 02:33
  • I was looking at the revisions of connector/J from 5.0.8 to 5.1.18.. They are pretty verbose about the changes. Anyway - Can you revert back to 5.0.8 and verify that the connector/J version is the issue? That'll make this a little easier to solve. Changelog: http://dev.mysql.com/doc/refman/5.1/en/cj-news.html – technocrat Jan 31 '12 at 14:49
  • We have multiple build environments and we have verified that the only difference between the ones that show the stale connections and the ones that do not is the difference in Connector/J version. This morning I tried each Connector/J version from 5.1.5 to 5.1.18 and all exhibited the same behavior. – user763648 Jan 31 '12 at 16:37
  • So I guess the next logical step is to figure out what changed between version 5.0.8 and 5.1.5 that is affecting your build, and see what work around can be applied. – technocrat Jan 31 '12 at 20:11
  • Looks like version 5.1.1 made a number of incompatible changes to the ConnectionDataPool interface.. source: http://dev.mysql.com/doc/refman/5.1/en/cj-news-5-1-1.html – technocrat Jan 31 '12 at 20:16
  • Can you be specific about which of the last versions caused the error... What about versions 5.1.0, 5.1.1, 5.1.2, and 5.1.3? – technocrat Jan 31 '12 at 20:42

3 Answers3

0

Your validation query is incorrect. Remove the "select 1" and leave ping only.

Mike
  • 1
  • Not true, see http://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-j2ee-concepts-connection-pooling.html To use this feature, specify a validation query in your connection pool that starts with /* ping */ `protected static final String PING_MARKER = "/* ping */";` `...` `if (sql.charAt(0) == '/') {` `if (sql.startsWith(PING_MARKER)) {` `doPingInstead();` `}` – user763648 Sep 16 '15 at 02:59
  • I based my response on this: https://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-tomcat.html The following XML snippet illustrates how to select this option: validationQuery /* ping */ Note that /* ping */ has to be specified exactly. – Mike Oct 13 '15 at 16:25
0

In my.cnf of your mysql, set the following properties to a big value like 365 days -

wait_timeout = 31536000
interactive_timeout = 31536000

The session wait_timeout value will be initialized with global wait_timeout value for non-interactive connections and with global interactive_timeout value for interactive connections.

PS - Both values are in seconds.

Naveen Kumar
  • 109
  • 2
  • 12
0

I might check the wait_timeout in your my.cnf file. The default is 28800 seconds or 8 hours. It has a max of 31536000 seconds or 365 days.

For the first exception you note: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException, I have in the past wrapped that in a try/catch block. In the catch, for that exception, I had the connection reconnect, and then resent the query. In knowing that I didn't want to have to do that very often, and still maintain an open connection, I also increased the default wait_timeout to something reasonable for my application.

See the manual reference at: http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_wait_timeout

technocrat
  • 3,513
  • 5
  • 25
  • 39
  • 1
    Our wait_timeout is set to the default 28800 seconds but we would rather fix the root cause of the issue than just reducing the frequency. – user763648 Jan 30 '12 at 20:08
  • It will at least give you some breathing room until you can fix it. What that value is saying is the following: If my connection goes unused for 28800 seconds, terminate it! On your system, if your Tomcat-DBCP pool does not keep it's connection active (as in a DDL or DML statement/query/insert/select..) then MySQL will terminate the connection after 8 hours, you may not realize it yet though until you try to perform said DDL/DML. Can you post the code where you setup the connection pool? Similar issue at MySQL: http://forums.mysql.com/read.php?39,501441,501692#msg-501692 – technocrat Jan 30 '12 at 22:44
  • I have updated the question to include our full connection attribute from server.xml. All our database logic is initiated from the Hibernate layer, down through tomcat-dbcp to the Connector/J driver. – user763648 Jan 30 '12 at 23:02
  • So, had you finally found `the root cause`? Seems this puzzle with dbcp is not getting older in years :) – dmitry Jan 24 '13 at 09:32
  • We set the timeout to the maximum value and called it a day. Still get timeouts from the server once in a while but its not a showstopper. – user763648 Jan 25 '13 at 20:17