Connection pool expires silently in Tomcat 7 but autoReconnect=true doesn't fix it

Question

I have been getting these exceptions for a number of weeks with no solution...

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 179,695,604 milliseconds ago.

The last packet sent successfully to the server was 179,695,604 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

So I have altered my application's Context.xml to have the autoReconnect=true tag set with my databases for connection pooling in Tomcat 7. I have even set wait_timeout to infinity in the context file.

What am I missing? Is this a common problem? It seems to have a small amount of information around on the net, but when I follow these guides, the same thing happens the next day after a period of inactivity.

The more I use the server, the less this happens. I think it is expiration of the pool connections but how can I stop them expiring if wait_timeout is failing? Any ideas on how to diagnose the problem or config files?

Ryley

Could you share some code (especially around the pooling config)? – Varmint

Have you tried increasing MySQL server side wait_timeout to be slightly greater than client side wait_timeout? You can check the existing value with SHOW VARIABLES LIKE 'wait_timeout'. Referring to MySQL Connector-J document under autoReconnect : "...As a last option, investigate setting the MySQL server variable "wait_timeout" to a high value, rather than the default of 8 hours." – Kathyrnkati

Just a comment: it's a good idea to not set the wait_timeout to a very large number in mysql (as a rule of thumb 5 minutes is a good value), the reason is if your app has a large number of connection and it crashes, all those sessions will probably remain open as mysql don't know that the connection has ceased to exist. Could you add-paste the complete configuration of the datasource? – Superstar

<Resource name="jdbc/myResource" auth="Container" type="javax.sql.DataSource" maxActive="30" maxIdle="30" maxWait="-1" removeAbandoned="true" logAbandoned="true" username="username" password="password" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/myResource?autoReconnect=true" /> Hi again! This is what the datasource is configured to – Ryley

Answer 1

The MySQL Connector/J documentation says about autoReconnect:

If enabled the driver will throw an exception for a queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction.

Meaning you will still get exceptions.

Connectors such as JDBI get around this by adding optional test queries on idle, borrow from pool and return to pool. Perhaps you could add something to your own JDBC connection wrapper to do the same. Alternatively, follow the documentation for autoReconnect and properly catch SQLExceptions arising from dead/stale connections.

There are further useful references on this answer using DBCP and c3p0

Celeski

Answer 2

I was facing a similar problem, autoReconnect=true throws the CommunicationsException exception, but then creates a new connection to mysql. So the next request would succeed. This behavior would continue to occur and the first request after an idle time would fail. To add to what Alex has already answered, I added the following params to my JDBC connection string and I don't see the error any more.

 testOnBorrow="true" validationQuery="SELECT 1" validationInterval="60000"

The description of testOnBorrow sufficiently explains it. And the good thing is I do not have to make any changes in my code.

References: https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html http://www.tomcatexpert.com/blog/2010/04/01/configuring-jdbc-pool-high-concurrency

Predigestion