Hibernate and the famous 'Communications link failure' and 'last packet sent successfuly' issue solved with C3P0

Published on Nov 27 2012 in Java JBoss Tomcat

With pure Hibernate applications longer period of database inactivity can cause first database connection to fail. Subsequent web page refreshes will usually work fine. Why it happens?

Usually, MySQL global parameter wait_timeout is set to 28800 (i.e. 8 hours). If your page is not accessed for more than 8 hours (e.g. in the night) MySQL will close the connection on its side. If your database connection config (in a Java app) is not prepared for such case your page may display error message and also the error will be logged. For example:

org.hibernate.SessionException: Session is closed!
    Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure 
    The last packet successfully received from the server was 22,761 milliseconds ago. The last packet sent successfully to the server was 9 milliseconds ago.
    java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

Usually the connection will be also dropped on your application side and a new one created so subsequent queries may work. But what if we want to be sure our site visitors will not ever see the error? We need to enable idle connection testing also referred to as connection validation.

How to configure database connection parameters in your code to test connection before use? Here goes an example for Hibernate. C3P0 project - an extension of JDBC - will help us to achieve it.

You can verify MySQL wait_timeout in your environment with:

mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 | 
+---------------+-------+

To correct the issue in a generic application that uses hibernate-core 3.3.1.GA perform the following 3 steps:

Download hibernate-c3p0-3.3.1.GA.jar and c3p0-0.9.1.jar into Tomcat’s lib or application’s WEB-INF/lib
In generic WEB-INF/classes/hibernate.cfg.xml add 3 new properties:

// tell hibernate to use C3P0
<property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property> 
  
// If this is a number greater than 0, C3P0 will test all idle, pooled but unchecked-out connections, every this number of seconds
// Set this to something below your MySQL wait_timeout
<property name="c3p0.idle_test_period">14400</property> 
    
// a query used to test connections
<property name="c3p0.preferredTestQuery">select 1;</property>

Here is full hibernate.cfg.xml from our application.

Restart the Tomcat and enjoy!

Other settings that should also be set in Hibernate config file (parameter names differ from these used in c3p0.properties) are listed here. Note, that if you do not set them in Hibernate config file - hibernate defaults for these paramters will override c3p0.properties counterparts.

Spring, Hibernate and C3P0 - a different approach

If you want to use c3p0 with Hibernate and Spring there is a catch. If you keep the default org.hibernate.connection.C3P0ConnectionProvider as hibernate.connection.provider_class in SessionFactory it does ignore c3p0 settings. For it to work you need to move c3p0 properties from SessionFactory bean so that these are not handled by Hibernate anymore. When they are in DataSource bean they are handled by Spring and things work as expected. Pool management class in DataSource is changed to com.mchange.v2.c3p0.ComboPooledDataSource.

See example beans from applicationContex.xml:

<bean id="myDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
 <property name="user" value="${datasource.username}"/>
 <property name="password" value="${datasource.password}"/>
 <property name="driverClass" value="${datasource.driver}"/>
 <property name="jdbcUrl" value="${datasource.url}"/>

 <property name="idleConnectionTestPeriod" value="14400"/>
 <property name="preferredTestQuery" value="select 1"/>

<!--  <property name="testConnectionOnCheckout" value="true"/>
 <property name="testConnectionOnCheckin" value="true"/>
 <property name="maxPoolSize" value="10"/>
 <property name="minPoolSize" value="5"/> -->
</bean>

<bean id="mySessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
 <property name="dataSource" ref="myDataSource" />
 <property name="hibernateProperties">
  <props>
   <prop key="hibernate.dialect">${datasource.dialect}</prop>
   <prop key="hibernate.show_sql">true</prop>
   <prop key="hibernate.hbm2ddl.auto">validate</prop>
   <prop key="hibernate.auto_close_session">true</prop>
<!-- below line may be important for some apps for the releasing to work properly -->
   <prop key="hibernate.connection.release_mode">after_transaction</prop> 
  </props>
 </property>
</bean>

Simple J/Connector solution

When using simple J/Connector JDBC string you can try using session variables in your JDBC string like this

jdbc:mysql://hostname:3306/dbname?sessionVariables=wait_timeout=99999999,interactive_timeout=99999999

Both wait_timeout and interactive_timeout are variables with SESSION scope and can be used this way.

Next planned article will handle the same issue with DBCP.

References:
http://www.mchange.com/projects/c3p0/index.html
https://developer.jboss.org/wiki/HowToConfigureTheC3P0ConnectionPool

Articles