Saturday, June 25, 2011

Resolve Hibernate connection timeout issue with MySQL server

This problem has been faced by many of the developers but the fix is not very common. I had to do some research to find out the problem and solve it.
If you are using a MySQL server, the default connection timeout is 8 hours (tested on MySQL 5.2). This causes a problem in production environment as the database could not be connected overnight. Below is the log trace of the problem -

org.hibernate.util.JDBCExceptionReporter: The last packet successfully received from the server was 56697 seconds ago. The last packet sent successfully to the server was 56697 seconds ago, which  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.


As the description says, we can increase the wait_timeout property of MySQL server but it is not a good idea to keep stale connections open for long time. The 'autoReconnect=true' property did not work for me.

I figured out the below solution which worked perfectly for me.
First we need to modify our hibernate.cfg.xml file and add the following lines -

5
20
1800
50
org.hibernate.connection.C3P0ConnectionProvider

You have to add a new file in your classpath called c3p0.properties. The file contains -

c3p0.testConnectionOnCheckout=true

The last but most important part is to add the required jars in the classpath. You must have hibernate.jar (contains org.hibernate.connection.C3P0ConnectionProvider class) and c3p0.jar.

This should solve the auto disconnection issue permanently.

P.S. To test the fix, you can change the wait_timeout property in my.cnf file (/etc/mysql/my.cnf in Linux) of MySQL server. Specify a small timeout value of few minutes.