Hibernate mySQL connection lost overnight

Posted on September 19, 2010


Using tomcat + mySQL + Hibernate …

Your application works fine … then you wake up in the morning and try to use it … it crashes. You restart Tomcat … it works fine again. There are a lot of posts on the internet about this 8 hour problem

To fix it …

1. Change wait_timeout and interactive_timeout in my.cnf for mySql (/etc/my.cnf) … it is defaulted to 28800 (that’s 8 hours in seconds) …. change it to something larger (say 24x60x60 = 86400)

2. Setup c3po connection pooling for Hibernate … add the jar (see optional libraries in hibernate) and add the following to your hibernate.cfg.xml file

<!–****** c3po settings …–>
<property name=”c3p0.acquire_increment”>3</property>
<property name=”c3p0.min_size”>5</property>
<property name=”c3p0.max_size”>20</property>
<property name=”c3p0.max_statements”>50</property>
<property name=”c3p0.timeout”>14400</property>
<property name=”c3p0.idle_test_period”>14400</property>

Then create a file “c3p0.properties” which must be in the root of the classpath (i.e. no way to override it for particular parts of the application):

# c3p0.properties
c3p0.preferredTestQuery=select 1

<!– c3p0.acquire_increment Determines how many connections at a time c3p0 will try to acquire when the pool is exhausted.–>
<!– c3p0.c3p0.idle_test_period If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds.–>
<!–=c3p0.timeout The seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire.–>

The key to resolving this issue is to configure both the database and hibernate settings.

For more details see useful links I learned from  below …

Automatic reconnect from Hibernate to MySQL

java.sql.SQLException: Communication link failure” when using Hibernate, Tomcat and MySQL

HowTo configure the C3P0 connection pool

mySql Server System Variables

Posted in: Hibernate