Use C3P0 to validate MySql connections in Hibernate apps - Quick Howto

This assumes MySQL wait_timeout is default 8 hours (28800) i.e. MySQL will terminate a connection on its side if it is not used for more than 8 hours. C3P0 will validate each idle connection every 4 hours (14440). This way MySQL will see them as active and will not terminate them.

  1. Download hibernate-c3p0-3.3.1.GA.jar and c3p0-0.9.1.jar into Tomcat lib directory (they will be then available for all webapps) or your web application WEB-INF/lib.
  2. In generic WEB-INF/classes/hibernate.cfg.xml add 3 new properties:
    <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
    <property name="c3p0.idle_test_period">14400</property>
    <property name="c3p0.preferredTestQuery">select 1;</property>
  3. If you were making the changes locally then rebuild WAR and reupload it.
  4. Restart the Tomcat

Symptoms of the problem in Tomcat logs are:

  • Communications link failure
  • java.net.SocketException: Broken pipe
  • Last packet sent to the server was 1 ms ago

and broken webapp after 8 hours of database related inactivity (most commonly after a night or weekend).

Here are links to the jars:
http://repo1.maven.org/maven2/org/hibernate/hibernate-c3p0/3.3.1.GA/hibernate-c3p0-3.3.1.GA.jar
http://repo1.maven.org/maven2/c3p0/c3p0/0.9.1/c3p0-0.9.1.jar

 

Live Demo Panel (user:demo, password:demo2011)

Go Live in Just 10 minutes!