Connection pool expires silently in Tomcat 7, but autoReconnect = true does not fix it

Question

I get these exceptions for several weeks without a solution ...

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

The last packet successfully sent to the server was 179,695,604 milliseconds ago. greater than the wait_timeout value set by the server. You should consider expiration and / or verify the connection before using it 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 changed my Context.xml application to set the autoReconnect=true tag with my databases for the connection pool in Tomcat 7. I even set wait_timeout to infinity in the context file.

What am I missing? Is this a common problem? There seems to be a small amount of information on the net, but following these guidelines the same thing happens the day after a period of inactivity.

The more I use the server, the less this happens. I think this is pool pool wait_timeout , but how can I stop them if wait_timeout does not work? Any ideas on how to diagnose the problem or configuration files?

Answer 1

The MySQL Connector / J documentation says about autoReconnect :

If enabled, the driver will throw an exception for requests issued for an outdated or dead connection that is related to the current transaction, but will try to reconnect before the next request issued for the connection in the new transaction.

So you still get exceptions.

Connectors such as JDBI are bypassed by adding optional test idle requests, borrowing from the pool, and returning to the pool. Perhaps you can add something to your own JDBC connection wrapper to do the same. Also, follow the documentation for autoReconnect and correctly understand the SQLExceptions arising from dead / legacy connections.

Below are useful links to this answer using DBCP and c3p0

Answer 2

I had a similar problem, autoReconnect = true throws a CommunicationsException, but then creates a new connection with mysql. Thus, the following request will be successful. This behavior will continue, and the first request after downtime will fail. To add to what Alex answered, I added the following parameters to the JDBC connection string, and I no longer see the error.

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

The description of testOnBorrow sufficiently explains this. And it’s good that I don’t need to make any changes to 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