If you are using MySQL and Hibernate, maybe you are familar with an exception like this one:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was79095 seconds ago.The last packet sent successfully to the server was 79095 seconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

To me, this problem occurred whenever my web application was not used for a certain amount of time. For some reason, Hibernate (?) does not close the connection properly, but the database system does after the wait_timeout has passed. Now Hibernate wants to use a connection that is not valid any more.

It took me some effort to overcome this problem, but I figured out a clean solution (setting the ‘wait_timeout’ setting of mysqld to days or weeks is NOT a good approach ;)).

The trick is to use connection pooling with c3p0. This way, you can make sure that a connection is revalidated after being idle for a long time. Configuration is quite easy. Just add the following properties to your Hibernate configuration (persistence.xml or hibernate.cfg.xml):

<property name="hibernate.c3p0.min_size" value="3" />
<property name="hibernate.c3p0.max_size" value="50" />
<property name="hibernate.c3p0.timeout" value="1800" />
<property name="hibernate.c3p0.max_statements" value="50" />
<!– this property forces the revalidation of a connection after the given amount of time (in secs) –>
<!– it must be set to LESS than the wait_timout setting for the mysql server (this setting defaults to 28800 secs (8 hours)) –>
<property name="hibernate.c3p0.idle_test_period" value="28680" />

Furthermore, you have to add the c3p0 jar to the classpath of your application.

Seam, Spring and jBPM integration HowTo
Download Files with Download Dialog
The SQuirreL 100 rows problem

Are you interested in reading more from CodingClues?
Then subscribe to new postings via RSS or via E-Mail.

close Reblog this comment
blog comments powered by Disqus