How to avoid stale MySQL/Hibernate connections (MySQLNonTransientConnectionException)

Question

Hi,

I have a Java webapp using Hibernate and MySQL. If the site isn't used for a few days, the MySQL connection goes stale, and I am met with the following exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.

From experience using raw JDBC it is possible to configure the connection to attempt to recover from errors or stale connections, but I don't know how to do this using Hibernate. I am not explicitly calling close() anywhere (but I am betting Hibernate does somewhere deep down in its guts).

Does anybody know what I should do?

Answer 1

What connection pool are you using?

The Hibernate suggestion is not to use the built-in pooling, but use the application server datasource or something like Commons DBCP or C3PO.

Answer 2

I had that problem. Using connection pooling (c3p0) made it go away. It is also a good idea in general to use some connection pooling.

Answer 3

Thanks for the advice. For posterity's sake, I changed the hibernate.cfg.xml from the following:

<property name="connection.url">jdbc:mysql://localhost/FooDB</property>
<property name="connection.username">root</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="connection.password">secret</property>
<property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>

... to the following:

<property name="connection.datasource">java:/comp/env/jdbc/FooDB</property> 
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>

This also required adding a standard 'context' entry in my web app's context.xml:

<Resource name="jdbc/FooDB"
          auth="Container"
          type="javax.sql.DataSource"
          maxActive="100"
          maxIdle="30"
          maxWait="10000"
          username="root"
          password="secret"
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/ss?autoReconnect=true" />

Answer 4

We had a similar problem of hibernate mysql connection getting timed out. So we tried C3P0, with the following configuration:

<property name=c3p0.acquire_increment>1</property>
<property name=c3p0.idle_test_period>3600</property> 
<property name=c3p0.max_statements>0</property> 
<property name=c3p0.min_size>1</property> 
<property name=c3p0.timeout>3605</property> 
<property name=hibernate.c3p0.preferredTestQuery>select 1;</property>

Hibernate connection_pool size was set to 1.

This made the timeout problem go away. But we started facing another problem. Long waits. We have a service (servlet running on jboss), which receives something like 5-6 requests per second. Every request needs to connect to mysql through hibernate. Most of our requests do selects, with an insert/update every 5th-6th request. Normally the request serve time for us is 2-3ms for select and 40-50ms for insert/update. But, after using the above C3P0 configuration, we saw that every request completing after an update was taking almost 4-5 minutes! From our logs, it seemed that randomly a select request will get stuck and will be able to complete only after an update request was received and served.

Above problem goes away if we remove the C3P0 config. Can somebody suggest what we could be doing wrong?

Here is the complete hibernate config for reference:

<?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="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://xxx.xxx.xxx</property>
        <property name="connection.username">xxx</property>
        <property name="connection.password">xxx</property>
        <property name="connection.pool_size">1</property>
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="current_session_context_class">thread</property>
        <property name="hibernate.cache.use_query_cache">false</property>
        <property name="hibernate.cache.use_second_level_cache">false</property>
        <property name="show_sql">true</property>
        <!-- Transaction isolation 2 = READ_COMMITTED -->
        <property name="connection.isolation">2</property>
        <property name="connection.autocommit">true</property>
        <!-- configuration pool via c3p0-->
        <property name="c3p0.acquire_increment">1</property>
        <property name="c3p0.idle_test_period">3600</property> <!-- seconds -->
        <property name="c3p0.max_size">1</property>
        <property name="c3p0.max_statements">0</property>
        <property name="c3p0.min_size">1</property>
        <property name="c3p0.timeout">3605</property> <!-- seconds -->
        <property name="hibernate.c3p0.preferredTestQuery">select 1;</property>
    </session-factory>
</hibernate-configuration>

Answer 5

<property name="c3p0.acquire_increment">1</property> 
<property name="c3p0.idle_test_period">120</property> <!-- seconds --> 
<property name="c3p0.max_size">100</property> 
<property name="c3p0.max_statements">0</property> 
<property name="c3p0.min_size">10</property> 
<property name="c3p0.timeout">180</property> <!-- seconds -->

override these settings on your config file. It ll helps to you.