Hibernate, MySQL and the EOFException

[Oliver Hanka]

Hi folks,

I am encountering the old problem with MySQL and Hibernate. Whenever I deploy an application, it works fine in the beginning. After a while of inactivity, however, I get the infamous EOFException. Here is the part of the stack trace:

root cause

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)
[...]

I know, this problem has been discussed quite often in different message-boards and on various maillinglist. Unfortunatly I wasn't able the figure out a solution which worked for me. Quite often, tipps given are contrary or do not help at all.

What I figured out so far: It seems that the problem occures because MySQL drops inactive connections while the connection pool doesn't. It is said, that the default value for MySQL dropping these is around 8 hours. I tried to address this issue by telling the connection pool to close inactive connections after 60 minutes. This, however, didn't solve the problem either.

I am using the c3p0 connection pool instead of Hibernates own one. Here is my hibernate.cfg.xml:

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.password">XXX</property>
        <property name="hibernate.connection.url">jdbc:mysql://127.0.0.1/k2?useUnicode=true&connectionCollation=utf8_general_ci&characterSetResults=utf8</property>
        <property name="hibernate.connection.username">XXX</property>
<property name="hibernate.connection.is-connection-validation-required">true</property>

        <property name="hibernate.show_sql">true</property>
        <property name="hibernate.current_session_context_class">thread</property>
 
<!-- configuration pool via c3p0--> 
<property name="hibernate.c3p0.acquire_increment">1</property> 
<property name="hibernate.c3p0.idle_test_period">100</property> <!-- seconds --> 
<property name="hibernate.c3p0.max_size">100</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 --> 

<!--  for all other c3p0 setting see c3p0.properties file -->

   <!--   <property name="hibernate.hbm2ddl.auto">create</property> -->
        
 <!-- some mappings here -->
 
    </session-factory>
</hibernate-configuration>

This is the c3p0.properties file within the root of my classpath:

c3p0.maxConnectionAge=3600
c3p0.preferredTestQuery=SELECT 1
c3p0.testConnectionOnCheckout=true
com.mchange.v2.log.MLog=com.mchange.v2.log.log4j.Log4jMLog

Did someone encounter the same problem? Did someone even solve it? (actually I guess so, since there must be people out there using Hibernate with MySQL)

Any suggestions what to change/add?

These are the software versions I am using:
Tomcat: 6.0.10.0
Hibernate: 3.2.2
MySQL: 5.0.32
JVM Version: 1.5.0_10-b03
[ May 09, 2007: Message edited by: Oliver Hanka ]

[Roger Diller]

I had this problem with my application as well. What I did was setup a timer (javax.swing.Timer) that about every 7 hours regains the connection. All you gotta do is setup up the timer and then add a listener to the timer that makes the call to reset the connection. I have my listener code below.


private class TimerHandler implements ActionListener {
public void actionPerformed(ActionEvent actionEvent) {
dbConnection = getDBConnection();
System.out.println("Regained connection...");
}
}


I think the MySQL default timeout period is exactly 8 hours so if you simply make a call to regain the connection every 7 hours or so it should take care of the problem. You can change the MySQL timeout period in the configuration file to however long you want to but you can't disable timing out to my knowledge.
I hope this helps... I'm sure there other solutions to this problem as well.

[Oliver Hanka]

Thanks's Roger!

What I have figured so far is, there also might be a problem with the way the application opens and closes connections. I added:

c3p0.unreturnedConnectionTimeout=3550

into c3p0.properties (requires c3p0 0.9.1 pre12) and that's what I found in the logs:

12:50:26,719 INFO RendererUtils:789 - Unable to find component 'msg' (calling findComponent on component '_idJsp0:_idJsp9'). We'll try to return a guessed client-id anyways - this will be a problem if you put the referenced component into a different naming-container. If this is the case, you can always use the full client-id.
13:02:28,888 INFO BasicResourcePool:1392 - A checked-out resource is overdue, and will be destroyed: com.mchange.v2.c3p0.impl.NewPooledConnection@1b158db
13:03:48,210 INFO BasicResourcePool:1392 - A checked-out resource is overdue, and will be destroyed: com.mchange.v2.c3p0.impl.NewPooledConnection@1e779a7
16:39:14,614 WARN JDBCExceptionReporter:77 - SQL Error: 0, SQLState: 08003

(Note: There was no request between 12:50 and 4:39)

I started the application and requested it to do a query. No update or insert. Here is my code:

public List getAllEntries(String table) {
Session session = HibernateUtil.getSessionFactory().getCurrentSession();
session.beginTransaction();
String query = "FROM " + table;
Query q = session.createQuery(query);
List returnList = q.list();
return returnList;
}

I also tried to add a "session.getTransaction().commit()" to close the session. With this, however, I wasn't able to retrieve values from a one-to-many association.