"java.sql.SQLException: Communication link failure" when using Hibernate, Tomcat and MySQL with Spring

I have recently obtained an error after leaving my Hibernate/Tomcat/MySQL web-application running overnight (>8 hours) with the following root-cause :

Root Cause: java.sql.SQLException: Communication link failure: java.net.SocketException, underlying cause: Software caused connection abort: recv failed

Situation: When first deployed the application works fine. However, when the application is left running for a long time (eg > 8 hours) without any interactions, the connection to the MySQL database is lost and the web-app throws an exception. The full stacktrace of that exception is below, for those interested. I am using Tomcat 6.0.18 and MySQL 5.0.27-community-nt.

Aside: It is perhaps also worth noting that another computer with the same code-base does NOT throw this error - even though the MySQL time-out settings are the same. The main difference appears to be that the other computer is running Tomcat 6.0.14 and MySQL 5.0.18-nt so it is possible that these versions don't require the fix below - but I haven't confirmed that it isn't some other minor difference.

Background:

This is a fairly common problem, but a range of solutions presented elsewhere on the internet didn't fix my problem - and they aren't documented together anywhere I could find - so I'm recording some of these here, along with the one that finally worked for me.

How to test:

The first thing to note is that this is caused by MySQL closing down connections that are idle for a period of time. One fix is to just drastically increase the "Interactive timeout" and "Wait timeout" in the my.ini file that is read on startup in MySQL. Don't forget to restart MySQL each time you change these (and to stop Tomcat before restarting MySQL). A good way to test if this timeout is really your problem is to change these to a short value (eg 40) (that is, 40 seconds) and wait to see if the error occurs in a minute or so. That way you don't have to wait overnight to test whether changes fix your problem.

Proposed Solutions:

NB: Only one of these worked (or was deemed "acceptable") for my case - and that one is the last one, but I record them all here as I couldn't find one location with all these summarized.

1. One solution is to just increase the MySQL Timeout settings as described in the "testing" section above. However, while this fixed one application I was working on, in one (different) case I found that the error was occurring overnight even if I set this to a very large value (eg 604800 sec). In any case, just increasing this value isn't a great solution as you don't want the error to occur ever - so just stalling it for a week isn't great!

2. Another solution that I didn't like, but should mention, is to put a try/catch around the Java code that accesses the database. This isn't really a hibernate solution, but I thought I'd mention it. See the suggestions at the following URL if you want to try this. It's inelegant if you're using Hibernate, but might be good if you are doing your connections manually.
http://forums.mysql.com/read.php?39,55337,139123#msg-139123

3. Another suggestion that I found on the web that didn't work - but might for similar problems - is to put the following into your hibernate config file:

<property name="connection.autoReconnect">true</property>
<property name="connection.autoReconnectForPools">true</property>
<property name="connection.is-connection-validation-required">true</property>

4. A related suggestion is to add autoReconnect=true to jdbc URL. I did try this and it didn't work for me, but it might be worth considering.

5. Another suggestion was to set the C3P0 connection-pooling properties as follows:
<session-configuration>
<property name=“hibernate.c3p0.acquire_increment">3</property>
<property name=“hibernate.c3p0.idle_test_period">14400</property>
<property name=“hibernate.c3p0.timeout">25200</property>
<property name=“hibernate.c3p0.max_size">15</property>
<property name=“hibernate.c3p0.min_size">3</property>
<property name=“hibernate.c3p0.max_statements">0</property>
<property name=“hibernate.c3p0.preferredTestQuery">select 1</property>
</session-configuration>

See https://www.hibernate.org/214.html for more details on C3PO config. This would set C3PO pooling values in the configuration - in particular setting the idle_test_period and timeout properties to be less than the values set for MySQL timeout (see 1 - I had the MySQL timeout set to 691200, so it was much longer). In fact, even before experiencing this problem I did actually already have these values set and yet the error still occurred! Still, this may be necessary /fix the problem for some people and I am still using these settings, above.

Aside: A similar set of properties for dbcp is given at https://forum.hibernate.org/viewtopic.php?f=1&t=933088&view=previous.

6. Solution (Actually worked for me): The final change that actually worked was to modify the Spring data-source to be:

<bean id="dataSource"
class="com.mchange.v2.c3p0.ComboPooledDataSource" abstract="false"
lazy-init="default" autowire="default" dependency-check="default"
destroy-method="close">
<property name="driverClass"> <value>com.mysql.jdbc.Driver</value> </property>
<property name="jdbcUrl"><value>jdbc:mysql://localhost/myApp</value></property>
<property name="user"> <value>myUser</value></property>
<property name="password"><value>myPassword</value></property>
<property name="autoCommitOnClose"> <value>true</value></property>
<property name="idleConnectionTestPeriod"><value>15</value> </property>
<property name="maxIdleTime"> <value>15</value> </property>
</bean>

The key properties here are to set the idleConnectionTestPeriod and maxIdleTime to values that are less than the mySQL timeout values mentioned in (1) above. Setting these two new properties for the datasource in applicationContext-jdbc.xml file in my Spring config settings fixed the problem.



More StackTrace Info for Error:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.TransactionException: JDBC begin failed:
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:408)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:350)
javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:174)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:77)
jcifs.http.CustomNtlmFilter.doFilter(CustomNtlmFilter.java:111)

root cause:

java.sql.SQLException: Communication link failure: java.net.SocketException, underlying cause: Software caused connection abort: recv failed** BEGIN NESTED EXCEPTION ** java.net.SocketExceptionMESSAGE: Software caused connection abort: recv failedSTACKTRACE:java.net.SocketException: Software caused connection abort: recv failed at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.read(Unknown Source) at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1391) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:1538) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1929) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278) at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1224) at com.mysql.jdbc.Connection.execSQL(Connection.java:2248) at com.mysql.jdbc.Connection.execSQL(Connection.java:2208) at com.mysql.jdbc.Connection.execSQL(Connection.java:2189) at com.mysql.jdbc.Connection.setAutoCommit(Connection.java:546) at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:756) at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:63) at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1326) at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:496) at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:322) at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:255) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:102) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)