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?

Answer 1

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

Answer 2

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