MySQL Connection Timeout Error in Hibernate

Problem

I am using Hibernate and MySQL in myour project. After our application is started, it just works well. However, if the application is not used for some time (like one day), it throws connection exception when it is used again. Trace of the exception:

com.mysql.jdbc.CommunicationsException: 
The last packet successfully received from the server was seconds ago.
The last packet sent successfully to the server was 407270 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.

I googled this problem, and it seems that many other persons also suffer this problem. I found some really nice blog posts about how to solve this problem.

According to the error message, I set the option autoReconnect (http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html) to true. But it still does not work. I searched in google and it shows this option works in some platforms while it doesn't work in other platforms.

Solution

Basically, the solution is 1) use a connection manager 2) tweak parameters of the connection manager.

C3P0 is a free JDBC connection manager.
Some C3p0 configuration parameters are mapped to Hibernate config. Those mapped configuration parameters are listed at http://www.mchange.com/projects/c3p0/index.html#hibernate-specific.

"Please note that configuration parameters that ARE mapped into hibernate's config file MUST be set within hibernate's configuration, or else they will be overridden by hibernate-specified defaults.
[The hibernate-mapped c3p0 parameters are minPoolSize, maxPoolSize, maxIdleTime, maxStatements, acquireIncrement, testConnectionOnCheckout, and idleConnectionTestPeriod. These map to the fllowing hibernate parameters: hibernate.c3p0.min_size, hibernate.c3p0.max_size, hibernate.c3p0.timeout, hibernate.c3p0.max_statements, hibernate.c3p0.acquire_increment, hibernate.c3p0.validate, and hibernate.c3p0.idle_test_period. DataSources configured in Tomcat should always use c3p0-native parameter names. But pools constructed under the covers by Hibernate must be configured in hibernate's config file with hibernate-defined parameter names where applicable.] "

For those C3p0 parameters that are NOT mapped, they should be specified in file WEB-INF/classes/c3p0.properties.
Note: C3P0 configuration parameter <c3p0.testConnectionsOnCheckout> is mapped to <hibernate.c3p0.validate> only in hibernate 2.x!
Also the configuration parameter c3p0.testConnectionsOnCheckout is really expensive. Users can use c3p0.maxIdleTime (it is mapped to hibernate.c3p0.timeout in hibernate) instead.

My hibernate.cfg.xml includes following configuration:

        <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
        <property name="c3p0.min_size">5</property>
        <property name="c3p0.max_size">30</property>
        <property name="c3p0.timeout">600</property>
        <property name="c3p0.max_statements">0</property>
        <property name="c3p0.acquire_increment">5</property>
        <property name="c3p0.idle_test_period">60</property>

The idle test operation can be optimized by specifying option preferrredTestQuery.
I am using MySQL as underlying RDB and putting c3p0.preferredTestQuery = SELECT 1 into c3p0.properties works fine (this is stolen from post comment http://www.databasesandlife.com/automatic-reconnect-from-hibernate-to-mysql/#comment-3156).

Resources

A really good article about how to reconnect from Hibernate to MySQL: http://www.databasesandlife.com/automatic-reconnect-from-hibernate-to-mysql/
C3P0 (Connection manager): http://www.mchange.com/projects/c3p0/index.html
How to configure C3P0 in Hibernate: https://www.hibernate.org/214.html
A forum thread about C3P0 configuration in Hibernate: https://forum.hibernate.org/viewtopic.php?t=934779&highlight=c3p0