Overcoming hibernate/mysql connection reset issue

One of the projects I have been working on uses Java1.6/JSP/Servlet/Hibernate3.2/Tomcat/MySQL5. Since it is just a prototype, I initially used the Hibernate's native connection pool management mechanism (which is not recommend for a production level deployment).

Every now and then, when we try to connect to the database server, it threw a connection reset exception. This happens because MySQL drops connections after every configured wait_timeout. But when I try to connect the second time, it works. It is not acceptable to have a piece of software that works in the second attempt! So, I tried different fixes.

I added the following property to hibernate.cfg.xml:

<property name="hibernate.connection.autoReconnect">true</property>

However it did not solve the connection reset problem. Still the first attempt failed. Apparently, the Hibernate's connection pooling library does not support this property.

From Hibernate (Jboss):
Hibernate's own connection pooling algorithm is, however, quite rudimentary. It is intended to help you get started and is not intended for use in a production system, or even for performance testing. You should use a third party pool for best performance and stability.

(It would be helpful for people to inform what is working and what's not. But can't complain these are free stuff.)


There are three possible avenues:
1. modify mysql.cfg to have a longer wait_timeout
2. use Tomcat managed connections
3. use a third-party connection pooling library

The first two options are out of my control and we only have limited privileges to mysql and tomcat instances. So, the only option was to look into #3.

I downloaded c3p0 and added the following configurations to hibernate.cfg.xml file have a basic setting (I did not try to optimize these figures just used the numbers that worked for others since the objective is not performance tuning, but to get it working.):

<!-- Min pool size -->
<property name="c3p0.min_size">5</property>

<!--Max pool size -->
<property name="c3p0.max_size">20</property>

<!-- Max idle time -->
<property name="c3p0.timeout">1800</property>

<!--Max statements - size of the prepared statement cache -->
<property name="c3p0.max_statements">50</property>

<!-- Set the pooling implementation to c3p0 -->
<property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>

Those are the basic pool settings. Still, the problem of first time failure is not solved. We need to tell c3p0 swallow the first failure and transparently connect in the second attempt. This does have a performance issue - every time when you want to connect, it does this.

You have to set an extra c3p0 property using c3p0.properties file. Add the file c3p0.properties to the root of the class path (in classes or WEB-INF classes for example) and turn on the c3p0.testConnectionOnCheckout property in that file.

c3p0.testConnectionOnCheckout=true

Note from Hibernate:
Don't use c3p0.testConnectionOnCheckout, this feature is very expensive. If set to true, an operation will be performed at every connection checkout to verify that the connection is valid. A better choice is to verify connections periodically using c3p0.idleConnectionTestPeriod.

As you can see, they do recommend a polling based approach where Hibernate periodically checks for idle connections. But I guess this also depends on the how frequently the hibernate layer is accessed. In our case, it is not that frequent. I didn't try that option but it should work.

Other pooling libraries such as Apache DBCP, Proxool should also work. But I didn't have time to check them out.

References: 1, 2, 3, 4, 5