Hibernate Dropping MySQL Connections

A typical problem that occurs in production environment is if your application is not used/access or no db hits are made for a long time (usually more than 8 hours for MySQL) then the underlying conection gets closed. I had such a harrowing experience with my application using Hibernate and MySQL Server. My clients used to complain that once they returned to use the application after a large gap like in the morning after last accessing it in evening or night, the application did not responded, logs told that it got busted. Following was the exception recorded from logs.

*******************

[ERROR] [2009-02-03 10:36:15,737] [http-8080-1] [util.JDBCExceptionReporter] - [No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error

**

BEGIN NESTED EXCEPTION **com.mysql.jdbc.CommunicationsExceptionMESSAGE: Communications link failure......

*******************

The problem was MySQL automatically times out, and closes unused connections after 8 hours. Hibernate does not appropriately test/refreshes its connection pool when connections go stale.This problem was resolved by appropriately using a Connection Pooling mechanism overriding the Hibernate's default which can ping the MySQL Server after specific time so as to maintain a connection state with db.There were several mechanism available for Connection Pooling from which one can choose like dbcp, c3p0 or proxool. After doing some hits on the www, I decided to go for c3p0 as it seems the de-facto for mysql.The below property should be placed inside your persistence.xml or hibernate.cfg.xml after initializing Hibernate default properties.

<!--

acquire_increment : no. of conn. at a time c3p0 tries to acquire when pool is exhausted. min_size : min/max no. of Conn. a pool will maintain at any given time

timeout : no. of Seconds Conn. can remain pooled but unused before being discarded. Zero means idle connections never expire

idle_test_period : c3p0 will test all idle, pooled but unchecked-out connections preferredTestQuery : Defines the query that will be executed for all connection tests

-->

<!-- if using hibernate 3 (specifically 3.3.1 or above) -->
<property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider"/>


<property name="hibernate.c3p0.acquire_increment" value="5" />

<property name="hibernate.c3p0.min_size" value="5"/>

<property name="hibernate.c3p0.max_size" value="15"/>

<property name="hibernate.c3p0.timeout" value="28800"/>

<property name="hibernate.c3p0.idle_test_period" value="14400"/>

<property name="hibernate.c3p0.preferredTestQuery" value="select 1;" />

For testing the above settings try below steps

Reproducing the problem
-------------------------
1. set property wait_timeout = 60 (1 minute) under My.ini file of MySQL, and restart your MySQL Server

2. Accessed your underlying application and leave for about 2-3 minutes (more than the timeout period).

3. Again access the application, below exception should be recorded in logs[ERROR] ...[No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error** BEGIN NESTED EXCEPTION **com.mysql.jdbc.CommunicationsExceptionMESSAGE: Communications link failureLast packet sent to the server was 63 ms ago.STACKTRACE:com.mysql.jdbc.CommunicationsException: Communications link failure Last packet sent to the server was 63 ms ago.atcom.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1070)at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2985)...

Fixing the problem

-------------------------

1. Add above c3p0 settings under persistence.xml or hibernate.cfg.xml in your application and restart it.

2. wait_timeout = 60 (1 minute) still applicable for MySQL

3. Access the application and leave for about 4-5 minutes (more than the timeout period) and even more than previous.

4. Again access the application, application should run fine and no connection type of exception will be recorded in logs. (unless untill you tried hard to write a really Buggy code)

5. removed the wait_timeout=60 value from My.ini and restart MySQL, default value for wait_timeout in MySQL is 8 hours

Another quick fix solution set your MySQL Server wait_timeout to some large value as I did to :

wait_timeout=2592000 (seconds) which is quivalent to 30 days

Hope this post helps anybody in distress. Do post your comments if it helped you.