title: Hibernate / MySQL connection timeouts -- Trying to deal with thread-pooled executors not releasing Hibernate connections to C3P0 after exit
body: <p>I have a Tomcat application that uses MySQL, and Hibernate for ORM. The nature of our application demands that we have to pull and aggregate a lot of analytics data from a NoSQL store for each request , so we split the pulling and aggregating for each request into several tasks and delegate those to a thread-pooled executor service. </p>
  
  <p>When each thread performs a task, it needs to query / update MySQL regarding certain things so it borrows Hibernate sessions from C3P0 ( which we use for connection pooling ). </p>
  
  <p>Essential config : </p>
  
  <pre><code>    &lt;property name="current_session_context_class"&gt;thread&lt;/property&gt;
      &lt;property name="connection.provider_class"&gt;org.hibernate.connection.C3P0ConnectionProvider&lt;/property&gt;
      &lt;property name="hibernate.connection.shutdown"&gt;true&lt;/property&gt;
      &lt;property name="hibernate.use_sql_comments"&gt;false&lt;/property&gt;
  
  &lt;!-- C3p0 Performance Improvements --&gt;
      &lt;property name="hibernate.c3p0.acquire_increment"&gt;1&lt;/property&gt;
      &lt;property name="hibernate.c3p0.idle_test_period"&gt;300&lt;/property&gt;
      &lt;property name="hibernate.c3p0.maxConnectionAge"&gt;3600&lt;/property&gt;
      &lt;property name="hibernate.c3p0.timeout"&gt;120&lt;/property&gt;
      &lt;property name="hibernate.c3p0.max_size"&gt;300&lt;/property&gt;
      &lt;property name="hibernate.c3p0.min_size"&gt;1&lt;/property&gt;
      &lt;property name="hibernate.c3p0.max_statements"&gt;100&lt;/property&gt;
      &lt;property name="hibernate.c3p0.preferredTestQuery"&gt;select 1;&lt;/property&gt;
  </code></pre>
  
  <p>The problem is Hibernate requests cause the MySQL / JDBC connection timeout error after 8 hours (our configured value of MySQL's wait_timeout parameter is the default i.e. 8 hours) . I replicated this by setting wait_timeout to 11 minutes but the result is the same for a 8-hour wait_timeout as well : </p>
  
  <pre><code>2013-01-27 20:08:00,088 ERROR [Thread-0] (JDBCExceptionReporter.java:234) - Communications link failure
  
  The last packet successfully received from the server was 665,943 milliseconds ago.  The last packet sent successfully to the server was 6 milliseconds ago.
  org.hibernate.exception.JDBCConnectionException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:99)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.doList(Loader.java:2536)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    at org.hibernate.loader.Loader.list(Loader.java:2271)
    at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716)
    at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347) 
    .....
  Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
  The last packet successfully received from the server was 665,943 milliseconds ago.  The last packet sent successfully to the server was 6 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3102)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2991)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3532)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293)
    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:1953)
    at org.hibernate.loader.Loader.doQuery(Loader.java:802)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    at org.hibernate.loader.Loader.doList(Loader.java:2533)
    ... 9 more
  Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2552)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3002)
    ... 22 more
  
  2013-01-27 20:19:00,179  WARN [Thread-0] (NewPooledConnection.java:487) - [c3p0] Another error has occurred [ com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 1,326,037 milliseconds ago.  The last packet sent successfully to the server was 660,100 milliseconds ago. 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. ] which will not be reported to listeners!
  com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 1,326,037 milliseconds ago.  The last packet sent successfully to the server was 660,100 milliseconds ago. 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.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3364)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1983)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293)
    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:1953)
    at org.hibernate.loader.Loader.doQuery(Loader.java:802)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    at org.hibernate.loader.Loader.doList(Loader.java:2533)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    at org.hibernate.loader.Loader.list(Loader.java:2271)
    at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716)
    at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
    ....
  Caused by: java.net.SocketException: Broken pipe
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(Unknown Source)
    at java.net.SocketOutputStream.write(Unknown Source)
    at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
    at java.io.BufferedOutputStream.flush(Unknown Source)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3345)
    ... 20 more
  </code></pre>
  
  <p>I thought C3P0 wasn't evicting stale connections frequently enough, so that during heavy load a connection would be returned to the pool, go stale and be borrowed again before eviction. So I created a c3p0.properties file and set "c3p0.testConnectionsOnCheckout" to true so that no stale connections would be borrowed from the pool. I got the same error again. </p>
  
  <p>I figured that since my hibernate sessions are configured with session-context "thread" , sessions used by a thread aren't released until a transaction is committed or rolled back [ <a href="http://docs.jboss.org/hibernate/orm/3.6/javadocs/org/hibernate/context/ThreadLocalSessionContext.html" rel="nofollow">http://docs.jboss.org/hibernate/orm/3.6/javadocs/org/hibernate/context/ThreadLocalSessionContext.html</a> ] . So the only explanation I have is that the executor threads make read-only DB calls where there's no committing going on , and once the task is done, the thread returns to the pool , holding the session with it. </p>
  
  <p>What should I do here ? Our application has hibernate querying code inside Data-Access-Object classes, one for each kind of bean. I wish to avoid having to change each and every method in those classes that makes read-only DB calls  so as to have them "commit" unnecessarily. I also wish to minimize the changes to the business logic of our app. </p>
  
  <p>Is there a way I can check for the freshness of the session returned by sessionFactory.getCurrentSession() each time a Data-Access-Object is declared / used (all Data-Access-Object classes inherit a few things from a single base class, so I can change things in that base class constructor) , and maybe return stale sessions to the pool ? Or is there a better way to do it ?</p>
  
  <p>Thanks.</p>