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)
Subscribe to:
Post Comments (Atom)
6 comments:
I ran across your post and was able to get everything working with the following in my hibernate.cfg (within the <session-factory>
<property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.min_size">2</property>
<property name="hibernate.c3p0.max_size">10</property>
<property name="hibernate.c3p0.acquireRetryAttempts">5</property>
<property name="hibernate.c3p0.acquireIncrement">2</property>
<property name="hibernate.c3p0.idleConnectionTestPeriod">14400</property>
The key piece that I found missing was the specification of the connection provider or else it was not using the connection pool. Then you have to also add the mchange jar file to make the classpath resolve.
Hope that helps others with this annoying problem. Thanks for your post though it got me going in a right direction!
The first solution worked like a charm. Thanks!
Perfect. You have summarised it perfectly. Struggled with this for long and found lots of forums discussing this. Very few identified that the mySQl setting and c3po setting both need to be modified and synced.
WOW , your solution 6 helped me too , thanks !!! I still didn't try long run , but it fixed my very short run problem!
Also solution 6 worked for me too.
Great job with this post.
This post is very well written, thanks. However, there is a problem with the solution 6 that "works". The suggestion is to create your own datasource using c3p0 connection pool. Note that c3p0 is part of Hibernate. cp stands for connection pool. Hibernate provides a very basic and simplistic connection pooling functionality for testing purposes only. So when you write your application you can use this package just to make sure that the code works. In Production systems c3p0 package should not be used. It should be replaced with serious connection pooling package. Therefore to fix the problem by using this package is not advisable. The problem described here is due to the fact that JDBC driver does not validate JDBC connection. In this post solution #3 tries to fix it by adding properties:
<property name="connection.autoReconnect">true</property>
<property name="connection.autoReconnectForPools">true</property>
<property name="connection.is-connection-validation-required">true</property>
That didn't work because JDBC driver needs validation query set so it knows which query to use to test the connection. Without this query set the validation is not done even when the properties above are set. In my application I use dbcp datasource org.apache.commons.dbcp.BasicDataSource provided by apache: http://commons.apache.org/dbcp/. It is configured to use MySQL JSBC driver Connector/J (http://dev.mysql.com/usingmysql/java/). Here is how my datasource is configured:
<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/ums"/>
<property name="username" value="scott"/>
<property name="password" value="some_tiger_password"/>
<property name="testOnBorrow" value="true"/>
<property name="testWhileIdle" value="true"/>
<property name="validationQuery" value="/* ping */ SELECT 1"/>
</bean>
The property "validationQuery" is what resolves the problem. The properties "testOnBorrow" and "testWhileIdle" tell the Datasource when to perform validation. (upon taking connection from pool and periodically while connection is idle in the pool). However, those properties are not mandatory and you may rely on their default values. There is another property that forces validation upon returning connection to pool
Post a Comment