Software caused connection abort: recv failed with MySQL and Hibernate

If you get "Software caused connection abort: recv failed" after attempting to login to your JBOSS server after a long period of idle time then you need to take a cup of coffee and sit down as this is going to take a while to fix.

In a web application that we developed,  we were using a JBOSS server version 4.04, connecting to MYSQL 5.0.24 with Hibernate 3.1 and we started getting these messages after the server had been sitting idle for some time and a login was attempted. The initial diagnosis was that the MySQL /JDBC connection was getting stale and it could be resolved by updating your MySQL driver to the latest version and adding a few properties to your data-source connection file. We updated the driver to mysql-connector-java-5.0.8 and as explained in this excellent post we added the following lines :

       
 (exception-sorter-class-name)
com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter
(/exception-sorter-class-name)
(valid-connection-checker-class-name)
com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker
(/valid-connection-checker-class-name)        

The basic idea was that the JNDI would ping the DB periodically and keep the data source alive. Well, it didn't work for us but atleast we had the latest driver.

The next approach was to write a custom class that would operate on the database every hour or so and keep the connection fresh. (According to the docs, MySQL marked the connection stale after 8 hours but I was not in a trusting mood). I wrote a Java Timer class that was called by a servlet every 30 minutes. The objective of the class was to check the table which housed our support requests and if found a new request, it would shoot me an email. The Timer class and the Servlet hookup worked fine and I stated getting emails if there was a support request waiting to be serviced but the original problem still remained. The server would still spit out a long stream of exceptions, starting with Software caused connection abort: recv failed if a login was attempted after a few hours of idle time.This was getting annoying!

I then approached the problem in a different way and went over my Hibernate connection pooling setup. We were using C3PO but maybe something was missing? I reset the Hibernate connection pooling parameters to the following :
(property name="connection.provider_class")org.hibernate.connection.C3P0ConnectionProvider(/property)
(property name="c3p0.acquire_increment")1(/property)
(property name="c3p0.idle_test_period")100(/property) (!-- seconds --)
(property name="c3p0.max_size")100(/property)
(property name="c3p0.max_statements")0(/property)
(property name="c3p0.min_size")10(/property)
(property name="c3p0.timeout")100(/property) (!-- seconds --)

and Voila, the exceptions disappeared. We finally had a clean console when attempting login ever after several hours. So while, the Hibernate connection pooling params appear to be the main culprit, I feel that its equally important to update your MySQL driver and make sure that there are no loose ends in the JNDI data-source params.