Tuesday, April 28, 2009

Hibernate and MySQL Connection Timeouts

Several weeks ago, while trying to finish up a portion of my senior project, my team was having some issues with our persistence layer. The issue had to do with the connection time out in MySQL, and the default C3P0 setup that Hibernate uses. As it took MUCH more research than I think it should have to solve the issue, I thought I would write a blog entry about it. Besides, I haven't set out to give anything back in quite a while. Note that while none of this information is new in any way, I have not been able to find it all gathered together in one place.

Complaint:
After my Spring/Hibernate/MySQL soltuion has been running but inactive for more than 8 hours, I get a broken pipe exception.

Root Cause:
MySQL automatically times out, and closes unused connections after 8 hours, and out of the box, Hibernate does not set up C3P0 to appropriately test/refresh its connection pool when connections go stale.

Solution:
It seemed that as we searched the forums for an answer it wasn't immediately obvious what the right answer would be. We noticed that the C3P0 properties had different names, depending on how your project was configured. The hibernate documentation has some suggestions, but it turns out that the names of the properties are not stated correctly. It is also important to note that if properties are not set in the hibernate configuration they will be overridden by hibernate defaults. Here's a snippet from my hibernate.cfg.xml:


<session-configuration>
<property name="hibernate.c3p0.acquire_increment">3</property>
<property name="hibernate.c3p0.idle_test_period">14400</property>
<property name="hibernate.c3p0.timeout">25200</property>
<property name="hibernate.c3p0.max_size">15</property>
<property name="hibernate.c3p0.min_size">3</property>
<property name="hibernate.c3p0.max_statements">0</property>
<property name="hibernate.c3p0.preferredTestQuery">select 1;</property>
</session-configuration>

The important properties to note above are idle_test_period, and timeout. You want to make sure that C3P0 is configured to test for closed connections and time out unused connections at some rate beneath the threshold set on your MySQL server. With these properties in place you should be good to go.

Testing:
Waiting 8 hours to conduct a test like this would be lame, so let's just change the connection timeout for the MySQL server, re-start the MySQL server, and our application to try it out. You can change the timeout time for MySQL by editing your /ect/my.cnf file (linux) or your my.ini file(windows). You would want to add the following line to the file:

wait_timeout=120

Note that the value after the property is in seconds. Once you're done with your testing, you can remove the property and it will default back to 8 hours.

References:

Configuring C3PO

C3P0 Hibernate Configuration

my_sql options()

Set the MySQL Connection Timeout

6 comments:

  1. Hi,

    I got tis problem some time ago and solved it with dbcp.
    Now I just migrated to hibernate 3 and I am getting that problem again.

    What config did you use to solve your problem ?

    My dbcp config is the following :

    <!--connection pool-->
    <property name="hibernate.dbcp.maxActive">10</property>
    <property name="hibernate.dbcp.whenExhaustedAction">1</property>
    <property name="hibernate.dbcp.maxWait">20000</property>
    <property name="hibernate.dbcp.maxIdle">10</property>

    <!-- prepared statement cache-->
    <property name="hibernate.dbcp.ps.maxActive">10</property>
    <property name="hibernate.dbcp.ps.whenExhaustedAction">1</property>
    <property name="hibernate.dbcp.ps.maxWait">20000</property>
    <property name="hibernate.dbcp.ps.maxIdle">10</property>

    <!-- optional query to validate pooled connections:-->
    <property name="hibernate.dbcp.validationQuery">select 1</property>
    <property name="hibernate.dbcp.testOnBorrow">true</property>
    <property name="hibernate.dbcp.testOnReturn">true</property>

    thanks

    ReplyDelete
  2. I had the same problem, using c3P0, hibernate, tomcat and MySQL.

    For me the issue was that MySQL was configured to close connections before C3p0.

    MySQL had the server variable "wait timeout" set to 50s, while C3p0's property "c3p0.timeout" was set to 1800s.

    Giving "c3p0.timeout" a value below 50s completely fixed the problem.

    ReplyDelete
  3. The information is well presented but this post is almost irrelevant without noting the versions of the software in question.

    ReplyDelete
  4. excellent .. i faced the same issue. changing the c3p0 timeout interval less than my sql timeout interval resolved the issue.

    ReplyDelete
  5. Hibernate Online Training Hibernate Online Training Hibernate Training in Chennai Hibernate Training in Chennai Java Online Training Java Online Training Hibernate Training Institutes in ChennaiHibernate Training Institutes in Chennai

    ReplyDelete