How to avoid legacy MySQL / Hibernate connections (MySQLNonTransientConnectionException)

Question

I have a Java webapp using Hibernate and MySQL. If the site has not been used for several days, the MySQL connection expires, and I encounter the following exception:

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

From the experience of using raw JDBC, you can configure the connection to try to repair errors or outdated connections, but I do not know how to do this using Hibernate. I don't explicitly call () anywhere (but I bet that Hibernate does somewhere deep in its guts).

Does anyone know what I should do?

Answer 1

What connection pool are you using?

Hibernate , - Commons DBCP C3PO.

Answer 2

. (c3p0) . .

Answer 3

. hibernate.cfg.xml :

<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>

... :

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

"" - 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

, , hibernate mysql . C3P0 :

<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 1.

- . . . (, jboss), - 5-6 . mysql . , / 5-6- . 2-3 40-50 /. C3P0 , , , 4-5 ! , , .

The above problem goes away if we remove the C3P0 configuration. Can anyone suggest that we can do wrong?

Here is the complete sleep configuration 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">
<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 options in your configuration file. This will help you.