Hibernate, C3P0, Mysql -- Broken Pipe

Question

MySQL seems to have an 8 hour time out on its connections. I'm running multiple WARs in Tomcat utilizing Hibernate for ORM. After 8 hours (i.e. overnight), I get broken pipes when it picks up an idle connection.

I've already traced through the code and made doubly sure I commit or roleback all transactions.

Here is my hibernate.cfg.xml

<?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="hibernate.bytecode.use_reflection_optimizer">false</property>
    <property name="hibernate.connection.driver_class">org.gjt.mm.mysql.Driver</property>
    <property name="hibernate.connection.password"></property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost/test</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
    <property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
    <property name="hibernate.current_session_context_class">thread</property>
    <!--property name="hibernate.show_sql">true</property>
    <property name="hibernate.format_sql">true</property-->

    <property name="c3p0.min_size">3</property>
    <property name="c3p0.max_size">5</property>
    <property name="c3p0.timeout">1800</property>
    <property name="c3p0.preferredTestQuery">SELECT 1</property>
    <property name="c3p0.testConnectionOnCheckout">true</property>
 <property name="c3p0.idle_test_period">100</property> <!-- seconds -->

    <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
    <property name="cache.use_query_cache">false</property>
    <property name="cache.use_minimal_puts">false</property>
    <property name="max_fetch_depth">10</property>

    <property name="hibernate.hbm2ddl.auto">update</property>

    <!-- classes removed -->

</session-factory>

The parameter I thought would have fixed it was the c3p0.idle_test_period -- It defaults to 0. However, we still have the Broken Pipe issue after 8 hours of running. While there are multiple posts index via Google, none arrive at a satisfactory answer.

Any help would be much appreciated!

Answer 1

Hi Mark. There are two things going on here. You should read this article for more details, but the take-aways are:

1. You can adjust the MySQL wait_timeout setting to something larger than 8 hours, if desired.
2. The Hibernate settings should include "hibernate." before the "c3p0", e.g. hibernate.c3p0.idle_test_period instead of just c3p0.idle_test_period

Answer 2

So it turns out I was missing a key line that enabled c3p0 (the c3p0 parameters I was tweaking were having no effect because Hibernate was using it's built in connection pool -- which it appropriately warns is not suitable for production). In hibernate 2.x, setting the hibernate.c3p0.max_size property enabled c3p0 connection pooling. However, in 3.x you must specify the following property --

<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>

Additionally, here are my final configuration parameters --

<property name="hibernate.c3p0.min_size">3</property>
<property name="hibernate.c3p0.max_size">5</property>
<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.idle_test_period">100</property> <!-- seconds -->

It's rather unfortunate that both Hibernate and c3p0 have abysmal documentation in this regard.

Answer 3

I had the same issue with my app running on tomcat 6. We fixed it by adding the jdbc resource to tomcat jndi in following manner:

        <Resource name="jdbc/mydb" auth="Container" type="javax.sql.DataSource"
            maxActive="50" maxIdle="10" maxWait="10000" validationQuery="SELECT 1"
            testOnBorrow="true" testWhileIdle="true"
            timeBetweenEvictionRunsMillis="10000" minEvictableIdleTimeMillis="60000"
            username="tomcat" password="xxxxxx" driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/gsm" />

And that fixed the Mysql 8 hour timeout issue. I tried passing driver specific parameters to the jdbc url before, but it didn't helped.

Try this and it will definately fix your issue.