Lost Connection in MySQL

onur · **Joined:** Thu Jan 26, 2006 6:19 am **Posts:** 19

Hello guys,
I have a problem in mysql with hibernate.
I guess mysql automatically kills open connections after there are not used for a given time. Since my singleton session factory cannot re-open the connection, all my queries fail.
I faced this problem both in mysql 4.1 and mysql 5.0

here is the exception,

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:1934)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2380)

My hibernarte-config file is like below, I've tried all autoReconnect stuff but still no solution! Can anyone suggest a solution?

PS: mysqlconnector version is : 3.1.11

Code:

<hibernate-configuration>
    <session-factory>
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="show_sql">false</property>
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql:///test?autoReconnect=true</property>
        <property name="connection.username">test</property>
        <property name="connection.password">test</property>
        <property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
        
        <property name="current_session_context_class">thread</property>
        <property name="connection.autoReconnect">true</property>
        <property name="connection.autoReconnectForPools">true</property>
      <property name="connection.is-connection-validation-required">true</property>
        <property name="connection.charset">UTF-8</property>
        <property name="c3p0.min_size">10</property>
        <property name="c3p0.max_size">100</property>
        <property name="c3p0.timeout">18000</property>
        <property name="c3p0.acquireRetryAttempts">30</property>

        <property name="c3p0.acquireIncrement">5</property>
        <property name="c3p0.automaticTestTable">C3P0TestTable</property>

        <property name="c3p0.idleConnectionTestPeriod">36000</property>

        <property name="c3p0.initialPoolSize">20</property>
        <property name="c3p0.maxPoolSize">100</property>
        <property name="c3p0.maxIdleTime">1200</property>
        <property name="c3p0.maxStatements">50</property>
        <property name="c3p0.minPoolSize">10</property>

        <property name="connection.charset">UTF-8</property>
</hibernate-configuration>

best regards

onur · **Joined:** Thu Jan 26, 2006 6:19 am **Posts:** 19

Any guesses?

DaleCooper82 · **Joined:** Sat Dec 09, 2006 8:14 pm **Posts:** 4

Hi,

I do not know whether this helps or whether this applies to you as I am using version 5 series of the driver.

However I faced similar issue with 5.0.4 driver and it was resolved after I have updated to 5.0.5. Or at least it did not happen again ever since. So far :)

Dale

bg4 · **Joined:** Fri May 18, 2007 12:29 pm **Posts:** 7

Any luck with this? I'm having the exact same issue. I've configured DBCP but it still fails once the connection is closed by the server. Our production MySQL server closes any connection which is idle for more than 10 minutes.

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

Have a look into c3p0 settings.

bg4 · **Joined:** Fri May 18, 2007 12:29 pm **Posts:** 7

Ok, I've done both DBCP and c3p0 and I'm still getting the same problem. I've gone through the trace logs and not seeing any errors.

Here is my properties:


<property name="c3p0.acquire_increment">1</property>
<property name="c3p0.idle_test_period">100</property> 
<property name="c3p0.max_size">20</property>
<property name="c3p0.max_statements">0</property>
<property name="c3p0.min_size">1</property>
<property name="c3p0.timeout">100</property> 
<property name="c3p0.acquireRetryAttempts">30</property>
<property name="c3p0.acquireRetryDelay">1000</property>
<property name="c3p0.breakAfterAcquireFailure">false</property>

Any insight is greatly appreciated!

Thanks

onur · **Joined:** Thu Jan 26, 2006 6:19 am **Posts:** 19

Currently, I found a workaround solution:
before my login page is displayed,
I create a thread to make some db queries,
so, if there would be a connection problem,
it will be faced, by thread, not the user..

DaleCooper82, I havent tried the 5.0.5 driver, but i will soon.
Thanks:)

pablogmuller · **Joined:** Mon Sep 11, 2006 4:50 pm **Posts:** 10

Hi,
I'm watching this topic because I have the same problem.... You said something about a loing page.... Are you using Tomcat or JBoss? If so, our workaround is to create a datasource in the application server, this way it keeps working even after long periods, and there are no extra threads needed...
Anyway... it would be nice to know how to fix that problem with hibernate/c3p0.
Bye.

onur · **Joined:** Thu Jan 26, 2006 6:19 am **Posts:** 19

From Mysql Connector documentation:

Quote:

1.5.3.4: I have a servlet/application that works fine for a day, and then stops working overnight
MySQL closes connections after 8 hours of inactivity. You either need to use a connection pool that
handles stale connections or use the "autoReconnect" parameter (see Section 1.4.1, “Driver/Datasource
Class Names, URL Syntax and Configuration Properties for Connector/J”).
Also, you should be catching SQLExceptions in your application and dealing with them, rather than
propagating them all the way until your application exits, this is just good programming practice.
MySQL Connector/J will set the SQLState (see java.sql.SQLException.getSQLState() in
your APIDOCS) to "08S01" when it encounters network-connectivity issues during the processing of a
query. Your application code should then attempt to re-connect to MySQL at this point.

bg4 · **Joined:** Fri May 18, 2007 12:29 pm **Posts:** 7

I'm able to recover from this issue now but I could not pin down the nature of why it's happening.

Now, when I catch an InfrastructreException in my application I call my HibernateUtil.closeSession(); method and retry what I was doing.

It happens once about 7-8 minutes after I start my application and then never again.