Hibernate and MySQL Connection Timeouts

Several weeks ago, while trying to finish up a portion of my senior project, my team was having some issues with our persistence layer. The issue had to do with the connection time out in MySQL, and the default C3P0 setup that Hibernate uses. As it took MUCH more research than I think it should have to solve the issue, I thought I would write a blog entry about it. Besides, I haven't set out to give anything back in quite a while. Note that while none of this information is new in any way, I have not been able to find it all gathered together in one place.

Complaint:
After my Spring/Hibernate/MySQL soltuion has been running but inactive for more than 8 hours, I get a broken pipe exception.

Root Cause:
MySQL automatically times out, and closes unused connections after 8 hours, and out of the box, Hibernate does not set up C3P0 to appropriately test/refresh its connection pool when connections go stale.

Solution:
It seemed that as we searched the forums for an answer it wasn't immediately obvious what the right answer would be. We noticed that the C3P0 properties had different names, depending on how your project was configured. The hibernate documentation has some suggestions, but it turns out that the names of the properties are not stated correctly. It is also important to note that if properties are not set in the hibernate configuration they will be overridden by hibernate defaults. Here's a snippet from my hibernate.cfg.xml:

<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>

The important properties to note above are idle_test_period, and timeout. You want to make sure that C3P0 is configured to test for closed connections and time out unused connections at some rate beneath the threshold set on your MySQL server. With these properties in place you should be good to go.

Testing:
Waiting 8 hours to conduct a test like this would be lame, so let's just change the connection timeout for the MySQL server, re-start the MySQL server, and our application to try it out. You can change the timeout time for MySQL by editing your /ect/my.cnf file (linux) or your my.ini file(windows). You would want to add the following line to the file:

wait_timeout=120

Note that the value after the property is in seconds. Once you're done with your testing, you can remove the property and it will default back to 8 hours.

References:

Configuring C3PO

C3P0 Hibernate Configuration

my_sql options()

Set the MySQL Connection Timeout