After upgrading the system to use ORM technologies by implementing hibernate (version 3.2.2), I was very satified. Until one day we realize that system seems to be broken every morning when we try to open our website. It always complained about the lost connection. After restarting the system everything ran smoothly.
I thought it was occurring because of some memory leak in our system that used up all the memory after some time. But it was a different problem.
The typical nature of the problem was “After the system was running, but remained inactive for more than 8 hours, I used to get a broken pipe exception.”
After spending sometime on research, I came up with the root cause and solution to the problem.
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:
I added some lines in the hibernate.cfg.xml file. Below is the part of my xml file
<session-configuration>
<!– other info –!>
<!– C3P0 Stuff –>
<property name=“c3p0.idle_test_period”>14400</property>
<property name=“c3p0.timeout”>25200</property>
<property name=“c3p0.max_size”>5</property>
<property name=“c3p0.min_size”>3</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:
I changed the connection timeout for the MySQL server to few minutes by adding following line to /ect/my.cnf file (linux).
wait_timeout=120
I then restarted the MySQL server. After the test was done, I removed the property so that it defaulted back to 8 hours.
Technology Cares 