See this note for the latest update in this issue.

07/20 01:38:26 hibernate.util.JDBCExceptionReporter     - Communications link failure

The last packet successfully received from the server was 7.202.618 milliseconds ago.  The last packet sent successfully to the server was 7.202.618 milliseconds ago.

After several days investigating the problem I've found out that other database worked smoothly with the application, so the problem was in the mysql.

I've copied several configuration values (max threads, log file size, cache size, etc..) from the “good” database to the old, and it's working now.

The lesson: too ambitious values (i.e. big caches, big memory comsumption, big threads can spoil your database performance).

This will show you the current connections:

show status like '%onn%';

Example of output:

mysql> show status like '%onn%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 0     |
| Connections              | 50    |
| Max_used_connections     | 9     |
| Ssl_client_connects      | 0     |
| Ssl_connect_renegotiates | 0     |
| Ssl_finished_connects    | 0     |
| Threads_connected        | 8     |
+--------------------------+-------+
7 rows in set (0.00 sec)

Pay attention to the data “Threads_connected”, which is similar to the result of the show processlist.

show variables like "max_connections";

mysql> show variables like '%imeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 28800    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 28800    |
+----------------------------+----------+
10 rows in set (0.00 sec)

Eventually I've implemented a configuration in the connection pooling mechanism to:

• verify the connection when it enters into the pool
• put a timeout to this connection, releasing it after this period

This will prevent the case when a connection is held but the JDBC driver drops out.

My config parameters:

    <property name="minPoolSize" value="2"/>
    <property name="maxPoolSize" value="4"/>
    <property name="initialPoolSize" value="1"/>
    <!-- good for mysql. For oracle, select * from dual could be good enough -->
    <property name="preferredTestQuery" value="select 1"/>
    <property name="testConnectionOnCheckin" value="true"/>
    <property name="idleConnectionTestPeriod" value="60"/>

And where I've taken them:

http://www.mchange.com/projects/c3p0/

I've put the status of the log to “DEBUG”, and I've seen this:

07/24 12:09:14 hibernate.jdbc.ConnectionManager         - transaction completed on session with on_close connection release mode; be sure to close the session to release JDBC resources!

It seems that the hibernate continues to use their connection manager as he wants, ignoring completely the configuration I have for C3P0….

Reading this:

http://www.mkyong.com/hibernate/how-to-configure-the-c3p0-connection-pool-in-hibernate/

I've discovered that, the configuration for C3P0 goes into the configuration of hibernate.

I have this:

  <bean id="dataSource"
        class="com.mchange.v2.c3p0.ComboPooledDataSource"
        destroy-method="close"
        scope="singleton">
    <property name="driverClass" value="com.mysql.jdbc.Driver"/>
    <property name="jdbcUrl"     value="jdbc:mysql://${mysql-server}:${mysql-port}/${mysql-database}"/>
    <property name="user"        value="${mysql-user}"/>
    <property name="password"    value="${mysql-password}"/>
 
  </bean>
 
   <bean id="sessionFactory"
          class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"
          scope="singleton">
        <property name="dataSource" ref="dataSource"/>
        <property name="annotatedClasses">
    [....]

And it's incorrect. This is the proper configuration:

    <bean id="sessionFactory"
          class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"
          scope="singleton">
        <property name="annotatedClasses">
            <list>
                <value>com.mapfre.dga.mercurio.entity.Detail</value>
                <value>com.mapfre.dga.mercurio.entity.Indicator</value>
                <value>com.mapfre.dga.mercurio.entity.Period</value>
                <value>com.mapfre.dga.mercurio.entity.Struct</value>
                <value>com.mapfre.dga.mercurio.entity.Valoration</value>
                <value>com.mapfre.dga.mercurio.entity.Version</value>
            </list>
        </property>
 
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</prop>
                <!-- for debug, put these values to "true" -->
                <prop key="hibernate.show_sql">${show-sql-in-hibernate}</prop>
                <prop key="hibernate.format_sql">${format-sql-in-hibernate}</prop>
 
		<prop key="hibernate.connection.driver_class">com.mysql.jdbc.Driver</prop>
		<prop key="hibernate.connection.url">jdbc:mysql://${mysql-server}:${mysql-port}/${mysql-database}</prop>
		<prop key="hibernate.connection.username">${mysql-user}</prop>
		<prop key="hibernate.connection.password">${mysql-password}</prop>
 
                 <!--
                  Configuration for fixing the problem:
 
                  The last packet successfully received from 
                  the server was 7.202.618 milliseconds ago.  
                  The last packet sent successfully to the 
                  server was 7.202.618 milliseconds ago.
 
                  -->
                <prop key="hibernate.c3p0.idle_test_period">60</prop>
                <!--
                In mysql, this value MUST be lower than wait_timeout. 
                You can check this out with the mysql command "show variables like '%ait%';"
                -->
                <prop key="hibernate.c3p0.timeout">60</prop>
                <prop key="hibernate.c3p0.min_size">2</prop>
                <prop key="hibernate.c3p0.max_size">5</prop>
		<!-- good for mysql. For oracle, select * from dual could be good enough -->
		<prop key="hibernate.c3p0.preferredTestQuery">select 1</prop>
		<prop key="hibernate.c3p0.testConnectionOnCheckin">true</prop>
                <prop key="hibernate.c3p0.idleConnectionTestPeriod">250</prop>
 
            </props>
        </property>
    </bean>

In other words, Hibernate must take care of configuring C3P0. I was so obsesed with Spring that I've forgot that some libraries do things in their own way.