Communications link failure between MySQL and Hibernate after many hours

The Problem:

I was using hibernate and MySql with apache-commons-dhcp. At the beginning everything works ok, but usually the first user who tray to login in the application in the morning get this exception:

2010-02-25 10:58:15,855 ERROR [org.hibernate.transaction.JDBCTransaction] - JDBC begin failed

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 10.228 milliseconds ago. The last packet sent successfully to the server was 16 milliseconds ago.

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2497)

at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2949)

... 47 more

25-feb-2010 10:58:15 org.apache.catalina.core.StandardWrapperValve invoke

GRAVE: Servlet.service() para servlet default lanzó excepción

org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin failed:

at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:599)

at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:374)

at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:263)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:101)

Caused by: org.hibernate.TransactionException: JDBC begin failed:

at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:68)

at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1326)

at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:558)

... 36 more

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 10.228 milliseconds ago. The last packet sent successfully to the server was 16 milliseconds ago.

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)

Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2497)

at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2949)

... 47 more

The Cause:

MySQL server has a default timeout period after which it terminates the idle connections.

This period is 8 hours by default.

Searching on the Internet I found many solutions:

• Disable SQL_CACHE on mysql (http://forums.mysql.com/read.php?39,203407,217196#msg-217196)
• Use c3p0 in hibernate (http://hibernatedb.blogspot.com/2009/05/automatic-reconnect-from-hibernate-to.html)
• Many others..

But always looks a big change in the application for a simple problem, finally

The Solution:

Add the following properties to dbcp configuration:

• validationQuery=”SELECT 1″
• testOnBorrow=”true”

How it Works:

• validationQuery --> Before returning the connection from pool to the application, dbcp runs the “SELECT 1 ” query on the connection to see it it is still live.
• testOnBorrow --> you tell dbcp to perform the check before returning the connection to application.

Other link with the same solution:

http://amitcodes.wordpress.com/2008/07/26/16/