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

12 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. Thank you for benefiting from time to focus on this kind of, I feel firmly about it and also really like comprehending far more with this particular subject matter. In case doable, when you get know-how, is it possible to thoughts modernizing your site together with far more details? It’s extremely useful to me
    Data Science course in Indira nagar
    Data Science course in marathahalli
    Data Science Interview questions and answers
    Data science training in tambaram
    Data Science course in btm layout
    Data science course in kalyan nagar
    Data science course in bangalore

    ReplyDelete
  6. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to article very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.
    DedicatedHosting4u.com

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. It was a wonderful chance to visit this kind of site and I am happy to know. Thank you so much for giving us a chance to have this opportunity..
    data scientist training in hyderabad

    ReplyDelete
  9. Informative blog and knowledgeable content. If you want to become a data science training then follow the below link.
    Data Scientist Training in Hyderabad

    ReplyDelete
  10. navigate to these guys www.dolabuy.ru this link replica gucci go to this web-site dolabuy

    ReplyDelete