Something wrong with Hibernate DB connection poole

Question

since last post, did all the changes suggested but this problem still haunts me. Here's the error i get:

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 44,499,102 milliseconds ago.

here's my hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>

    <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>        
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>        
    <property name="connection.autoReconnect"> true</property>
    <property name="connection.autoReconnectForPools">true</property>
    <property name="connection.is-connection-validation-required">true</property>

    <property name="hibernate.c3p0.acquire_increment">5</property> 
    <property name="hibernate.c3p0.max_size">150</property>
    <property name="hibernate.c3p0.max_statements">0</property>
    <property name="hibernate.c3p0.min_size">10</property>
    <property name="hibernate.c3p0.timeout">100</property> <!-- seconds --> 
    <property name="hibernate.c3p0.idle_test_period">30</property> <!-- seconds --> 

    <property name="hibernate.connection.url">jdbc:mysql://!secret!autoReconnect=true</property>
    <property name="hibernate.connection.username">!secret!</property>
    <property name="hibernate.connection.password">!secret!</property>


    <!-- <property name="hibernate.connection.pool_size">10</property> -->

    <property name="show_sql">true</property>
    <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="hibernate.hbm2ddl.auto">update</property>
    <property name="current_session_context_class">thread</property>

    <!-- Mapping files -->
    <mapping resource="mappings.hbm.xml"/>
</session-factory>
</hibernate-configuration>

and c3p0.properties

c3p0.preferredTestQuery=select 1 from dual
c3p0.maxConnectionAge=3600
c3p0.testConnectionOnCheckin=true
c3p0.testConnectionOnCheckout=true
c3p0.acquireRetryDelay=1000
c3p0.acquireRetryAttempts=30
c3p0.breakAfterAcquireFailure=false
c3p0.idleConnectionTestPeriod=100

Answer 1

Enter the below line (1) in c3po.properties file in your root derectory.(you should used hibenate-c3po 3.6.10 final.jar) And in the Hibeanate.cfg.xml used add (2) part .

1. c3p0.testConnectionOnCheckout=true

2. <property name="hibernate.c3p0.idle_test_period">100</property> <property name="hibernate.c3p0.max_size">30</property> <property name="hibernate.c3p0.max_statements">10</property> <property name="hibernate.c3p0.min_size">10</property> <property name="hibernate.c3p0.timeout">1800</property> <property name="hibernate.c3p0.validate">true</property> <property>

Answer 2

It seems that the connection to the database has timed out and has been terminated by the database server. You should either increase the time the server can wait or append ?autoReconnect=true to your jdbc connection string.

Answer 3

I think this post might help. If it is the case, increasing the value of wait_timeout only postpones the ocurrence more time, rather than solving the problem.

The problem could be related with this:

• You open an hibernate session
• Do some things that take more time than the value of wait_timeout of your database
• You try to use this hibernate session. The message that you comment here will appear

I've documented a case in here if you are interested.

Answer 4

As for me, you're incorrectly configured c3p0.

Properties like c3p0.preferredTestQuery must be located at c3p0.properties file from your classpath (e.g. WEB-INF/classes).

Below is my example of c3p0.properties file that work nice for Oracle:

c3p0.preferredTestQuery=SELECT 1 from dual
c3p0.maxConnectionAge=3600
c3p0.testConnectionOnCheckout=true
c3p0.acquireRetryDelay=1000
c3p0.acquireRetryAttempts=30
c3p0.breakAfterAcquireFailure=false

See also official doc for c3p0 here.

And please pay your attention to version of c3p0 that you're using. They had an issue connection restoring in early releases of c3p0 0.9.