How to fix Hibernate MySql connection timeout issue (solved)?

When we began creating an application using Struts2 framework with Hibernate and MySQL, we ran into a problem which was related to MySQL timing out its connection after a span of 8 hours when left unused. It conceived a lot of time at our end but we were able to nail down the issue at the end.

Steps that we had taken to correct the issue related to Hibernate MySql connection timeout problem are listed below

– Download Hibernate C3P0 and copy .jar files
– Set c3p0.properties
– Make changes to hibernate.cfg.xml
– Test MySQL connection timeout
– Hibernate, MySQL connection timeout related error messages

Download Hibernate C3P0 (JDBC Data sources/Resource pools)

Copy the following .jar files into WEB-INF/lib folder

– c3p0-0.9.2.jar
– mchange-commons-java-0.2.3.3.jar

Add c3p0.properties file under the folder identifiable by java CLASSPATH (WEB-INF/classes) and add the below line

c3p0.testConnectionOnCheckout=true

Open hibernate.cfg.xml and add the below set of lines

<property name=”c3p0.min_size”>5</property>
<property name=”c3p0.max_size”>20</property>
<property name=”c3p0.timeout”>1800</property>
<property name=”c3p0.max_statements”>50</property>
<property name=”connection.provider_class”>org.hibernate.connection.C3P0ConnectionProvider</property>

Restart tomcat instance.

To test MySQL connection timeout, open /etc/mysql/my.cnf (or my.ini file in windows) and under [mysqld] set (or modify) the wait_timeout value to say 120 seconds (2 minutes) to check how the application behaves after the above setting changes are made. You can reset it back once the testing is over.  Default timeout setting in MySQL is 8 hours.

[mysqld] wait_timeout = 120

Some of the error messages associated with the hibernate mysql reconnect timeout issue are “org.hibernate.connection.C3P0ConnectionProvider cannot be found”, “org.hibernate.util.JDBCExceptionReporter: The last packet successfully received from the server was 73998 seconds ago”, “com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception”

 

Share it onShare on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

Written by kurinchilamp


Website: http://

Leave a Reply

Your email address will not be published. Required fields are marked *

Read previous post:
PEAR setup in WAMP in simple steps

* After WAMP is installed go to the folder where the web server related PHP bin directory is present. Search...

Close