Hibernate, c3p0, and Mysql: the java.io.EOFException

Question

Hello,

I'm using Hibernate with MySQL and c3p0, and when the MySQL connection expires after 8 hours (wait_timeout), Hibernate is unable to reconnect and i get the following exception:

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 
** BEGIN NESTED EXCEPTION ** 

java.io.EOFException

STACKTRACE:

java.io.EOFException
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1963)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2375)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2874)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1403)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1812)
    at org.hibernate.loader.Loader.doQuery(Loader.java:697)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
    at org.hibernate.loader.Loader.doList(Loader.java:2232)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
    at org.hibernate.loader.Loader.list(Loader.java:2124)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)

This is the hibernate.cfg:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"&gt;
<hibernate-configuration>
    <session-factory>

        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/mydb</property>
        <property name="hibernate.connection.username">username</property>
        <property name="hibernate.connection.password">password</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
        <property name="hibernate.query.substitutions">true 1, false 0</property>

        <property name="hibernate.current_session_context_class">thread</property>

        <property name="hibernate.hbm2ddl.auto">validate</property>
        <property name="hibernate.connection.autocommit">false</property> 

        <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>        
        <property name="hibernate.c3p0.min_size">3</property>
        <property name="hibernate.c3p0.max_size">10</property>
        <property name="hibernate.c3p0.timeout">180</property>
        <property name="hibernate.c3po.max_idle_time">180</property> 
        <property name="hibernate.c3p0.idle_test_period">300</property>
        <property name="hibernate.c3p0.acquire_increment">3</property>

        <!-- My mappings here -->
    </session-factory>
</hibernate-configuration>

and this is my c3p0.properties file:

c3p0.preferredTestQuery=SELECT 1

UPDATE:

Below the log file:

INFO - com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getPoolManager(462) | Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@6df9572
c [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@7eeb0844 [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetr
yDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassNam
e -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, debugUnreturnedConnectionStackTraces -> false, factoryClassLocation
 -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> jbnj238aq9q7lecas8ck|79b0d33c, idleConnectionTestPeriod -> 300, initialPoolSize -> 3, 
maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 180, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 0, maxState
mentsPerConnection -> 0, minPoolSize -> 3, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@8ed91d5 [ description -> null, driverClass -> null
, factoryClassLocation -> null, identityToken -> jbnj238aq9q7lecas8ck|445ead9d, jdbcUrl -> jdbc:mysql://localhost:3306/mydb, properties -> {user=******, p
assword=******, autocommit=false} ], preferredTestQuery -> SELECT 1, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false,
 unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null, factoryClassLocation -> null, iden
tityToken -> jbnj238aq9q7lecas8ck|26796e1c, numHelperThreads -> 
3 ]

Any tip is appreciated.
Thanks

Answer 1

This is the part that I caught.

the MySQL connection expires after 8 hours

That shouldn't happen if you are using c3p0. You should be making a new connection and storing them within the pool.

If you aren't adding new connections or refreshing the ones you have you will timeout. If you are timing out with multiple attempts tell c3p0 to rebuild/refresh the connection pool.

Answer 2

Silly question: in your Hibernate configuration, you have the following line:

<property name="hibernate.c3po.max_idle_time">180</property>

The o in c3po should be a 0. Was this just a copy-and-paste mistake? Losing the max_idle_time setting certainly seems like it would cause this problem.

EDIT: After reviewing the c3p0 docs, it looks like the correct setting name is hibernate.c3p0.timeout.

EDIT 2: I just looked at your configuration and you already have hibernate.c3p0.timeout. D'oh. So you can probably ignore this entire answer :-P.

Answer 3

I solved the problem:

when setting the c3p0 properties, the value of hibernate.c3p0.idle_test_period must not be higher of the hibernate.c3p0.timeout. So in my case setting:

<property name="hibernate.c3p0.timeout">180</property>
<property name="hibernate.c3p0.max_idle_time">180</property> 
<property name="hibernate.c3p0.idle_test_period">100</property>

fixed it.