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:
Entradas
