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.
- 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.
- 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>
- If you were making the changes locally then rebuild WAR and reupload it.
- 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!
