Could not toggle autocommit

Question

I have a transaction which runs for many hours in the Java Layer and the when Hibernate tries persist collated data, Exception Stack trace shown below is thrown.

Note: I have also tried the configurations specified at..

http://forums.mysql.com/read.php?39,52805,205216#msg-205216 and http://forums.mysql.com/read.php?39,52805,273371#msg-273371

++++++

ERROR org.hibernate.transaction.JDBCTransaction (JDBCTransaction.java:232) - Could not toggle autocommit 
java.sql.SQLException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error: 


** BEGIN NESTED EXCEPTION ** 

com.mysql.jdbc.CommunicationsException 
MESSAGE: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.io.EOFException 

STACKTRACE: 

java.io.EOFException 
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1903) 
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2349) 
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2860) 
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571) 
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666) 
    at com.mysql.jdbc.Connection.execSQL(Connection.java:2972) 
    at com.mysql.jdbc.Connection.commit(Connection.java:2147) 
    at org.apache.commons.dbcp.DelegatingConnection.commit(DelegatingConnection.java:301) 
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.commit(PoolingDataSource.java:200) 
    at org.hibernate.transaction.JDBCTransaction.commitAndResetAutoCommit(JDBCTransaction.java:170) 
    at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:146) 
    at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:656) 
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754) 
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723) 
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:394) 
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:117) 
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) 
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89) 
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) 
    at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:625) 

Solution

MySQL has a default timeout period after which it terminates idle connections. By default the period is 8 hrs.

The DBCP however is unaware of when the connections are terminated by the MySQL server and so these stale connections are still handed out on demand to the application. Hence you get an Exception.

A fix is to add the following properties to your dbcp config:

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

This tells DBCP to validate the connection with that query.