Could not toggle autocommit

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) 

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.

The connection was most likely dropped by the database. Specify a validation query on DBCP to ensure that the connections in the pool are still alive.

http://commons.apache.org/dbcp/configuration.html

We were having a very similar stacktrace at work when trying to index data, using Lucene, from our Spring application. The problem was the wait_timeout setting in my.cnf. We had just upgraded our MySQL installation and our DBA had forgotten to change wait_timeout from the default setting to what it was previously, 3600.