Reproduce com.mysql.jdbc.exceptions.jdbc4.CommunicationsException with a setup of Spring, hibernate and C3P0

Question

I got this error from the production code:

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

And now I'm trying to reproduce the problem locally and fix it. I setup the spring context as following:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
    destroy-method="close" p:driverClass="com.mysql.jdbc.Driver"
    p:jdbcUrl="jdbc:mysql://localhost:3306/test?useUnicode=yes&amp;characterEncoding=UTF-8&amp"
    p:idleConnectionTestPeriod="120" p:initialPoolSize="1" p:maxIdleTime="1800"
    p:maxPoolSize="1" p:minPoolSize="1" p:checkoutTimeout="1000"
    
/>

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="hibernateProperties">
        <value>
            hibernate.connection.provider_class = org.hibernate.connection.C3P0ConnectionProvider
            hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
            hibernate.default_schema=platform_server_original
            hibernate.show_sql=false
        </value>
    </property>
    <property name="mappingResources">
        <list>
            <value>sometables.hbm.xml</value>
        </list>
    </property>
</bean>

Then I set my mysql wait_timeout to 10 seconds, then run my test, which is basically open a connection, do a query, close it, so it returns to the pool, then sleep the thread for 15 seconds, and then open a connection again, and do a query again, so it will break. However, I got a similar error only:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 16 ms ago.

So I wonder are these two errors the same, or they are different? I did some researches, and it seems both errors came down to the same solution: using a property "testConnectionOnCheckout=true". However, according to c3p0 doc, this is a very expensive check. It advises the use of "idleConnectionTestPeriod", but I'm already setting that to 120 seconds. What value should I use it so it can properly verify the idle connection?

So I'm basically ask two things:

1. how do I reproduce the error I got in production code?
2. how do I fix it?

Thanks!

Answer 1

I had similar problems with MySQL and a connection pool. The problem is you tell the connection pool that an idle timeout is 30 minutes, but the database cuts the connection after 10 seconds. Since your idle connection check period is 120 sec, it leaves a little under 110 secs for the pool to use a broken connection!

I'd use the following settings for production:

MySQL:
wait_timeout=75
C3P0:
maxIdleTime=60
idleConnectionTestPeriod=55