Languages
 
 
 
 
Follow Me



 
 

Unexpected database connection problem. Hibernate broken pipe.

  Tagged

While using Hibernate, several times I faced the same database communication problem: for some reason after long inactivity time connection was closed. Each time that happens it takes some my time to remember the solution. So this post is more like a note for myself, but also can be helpful for those who can find it in Internet.

This is an example of error:

root cause 

org.hibernate.exception.JDBCConnectionException: could not execute query
    org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:74)
    org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    org.hibernate.loader.Loader.doList(Loader.java:2223)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
    org.hibernate.loader.Loader.list(Loader.java:2099)
    org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
    org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
    org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
    org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
    org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
    org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:811)

root cause 

com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:


** BEGIN NESTED EXCEPTION ** 

com.mysql.jdbc.CommunicationsException
MESSAGE: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: Broken pipe

STACKTRACE:

java.net.SocketException: Broken pipe

The solution is to add additional check query for database connection. This can be added just directly in Hibernate session factory configuration. This is an example of my configuration: hibernate.cfg.xml:

<!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="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost/test</property>
        <property name="hibernate.connection.username">testuser</property>
        <property name="hibernate.connection.password">testing</property>
        <property name="hibernate.connection.pool_size">10</property>
        <property name="hibernate.connection.autocommit">true</property>
        <property name="hibernate.hbm2ddl.auto">update</property>
        <property name="hibernate.show_sql">false</property>
        <property name="hibernate.use_sql_comments">true</property>
       
        <!-- c3p0 -->
        <property name="hibernate.c3p0.acquire_increment">1</property>
        <property name="hibernate.c3p0.idle_test_period">60</property>
        <property name="hibernate.c3p0.timeout">120</property>
        <property name="hibernate.c3p0.max_size">10</property>
        <property name="hibernate.c3p0.min_size">1</property>
        <property name="hibernate.c3p0.max_statements">10</property>
        <property name="hibernate.c3p0.preferredTestQuery">select 1;</property>

        <!-- Connection auto reconnect after long inactivity -->
        <property name="connection.autoReconnect">true</property>
        <property name="connection.autoReconnectForPools">true</property>
        <property name="connection.is-connection-validation-required">true</property>
           
        <!-- Mappings -->
        <mapping class="com.kobyleha.test.User" />
       
    </session-factory>
   
</hibernate-configuration>

Looking at example it's easy to find check query which will be used, instruction to reconnect automatically, and instruction to make connection checks.

This is it. Good luck.

Share this

There is more information

There is more information about this error in a topic of mysql forums

Post new comment

CAPTCHA
Are You Human/Bot?
 
© 2006-2010 kobyleha.com