Hibernate connection timeout

Question

We have a problem with the mysql connection in our Server. The WebApp works correct, but after a few hours, we get an error from the connection with mysql. We think that something is wrong with the hibernate Session. As far as we know, the mysql connection is closed while the hibernate tries to use the same session. Maybe we aren't closing the session properly.

We work with Struts 2, and we have defined an Interceptor that manage all the session stuff. Here is the code:

public class OSVStrutsInterceptors implements Interceptor {

private  static SessionFactory sf;

@Override
public void destroy() {

    try
    {
        sf.getCurrentSession().close();
    }
    catch (HibernateException ex)
    {
        throw new RuntimeException(ex);
    }
}

@Override
public void init() {

}

@Override
public String intercept(ActionInvocation ai) throws Exception {
    sf = HibernateUtil.getSessionFactory();
    Session sesion=sf.getCurrentSession();
    try {
    sesion.beginTransaction();
    }
    catch (Exception ex ) {
        System.out.println(new Date()+" Sesion cerrada. Obtenemos una nueva");
        sesion=sf.openSession();
    }

    String result=ai.invoke();
    sesion.getTransaction().commit();

    return result;

}
}

As you can see, we are trying to get the currentSession, but if is not possible, we open a new Session. Also we close the session on the destroy() method of the interceptor.

But We always get this error on our server:

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

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: Broken pipe

STACKTRACE:

java.net.SocketException: Broken pipe

batbaatar · Accepted Answer · 2012-03-15 10:50:17Z

Adding autoReconnect=true is not recommended by MySQL (could not find the reference). Instead add these lines on hibernate.cfg.xml

<!--  c3p0 -->
<property name="hibernate.c3p0.validate">true</property>
<property name="hibernate.connection.provider_class">org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">600</property>
<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.c3p0.preferredTestQuery">SELECT 1;</property>
<property name="hibernate.c3p0.testConnectionOnCheckout">true</property>
<property name="hibernate.c3p0.idle_test_period">3000</property>

And add these libraries to your CLASSPATH

hibernate-c3p0-4.0.0.Final.jar
c3p0-0.9.1.jar (Not sure if this is neccessary)

Perception · Accepted Answer · 2012-03-14 19:56:30Z

1

Communications link failures can happen for any number of reasons, including network outage, router misconfiguration, or other network issues. It's also possible (though not likely) that your connections are simply timing out. In any case, you can attempt to remedy the issue by configuring your MySQL datasource to automatically reconnect. One way of doing this is by specifying the appropriate property on the JDBC URL:

url="jdbc:mysql://localhost:3306/mydb?autoReconnect=true

answered Mar 14, 2012 at 19:56

Perception

80.8k19 gold badges190 silver badges197 bronze badges

Comments

Frizz1977 · Accepted Answer · 2014-01-29 16:12:23Z

I use a custom ConnectionProvider that use org.apache.commons.dbcp.BasicDataSource. in this class you must set:

      setValidationQuery("select 1");
      setTestOnBorrow(true);

NOTE: i wrap connection with net.sf.log4jdbc.ConnectionSpy, but you can do not it

in hibernate.cfg

<property name="hibernate.connection.provider_class">
    example.DBCPConnectionProvider
</property>





public class DBCPConnectionProvider implements ConnectionProvider { 
    private static final long serialVersionUID = -4063997930726759172L;

    private static final Logger log = Logger.getLogger(DBCPConnectionProvider.class);

    static CustomDataSource ds;

    static{
        try {
            ds = new CustomDataSource();    
        } catch (Exception e) {
            log.error("", e);
        }
    }


    @Override
    public void closeConnection(Connection conn) throws SQLException {
        conn.close();
    }

    @Override
    public Connection getConnection() throws SQLException {
        return ds.getConnection();
    }




    @Override
    public boolean supportsAggressiveRelease() { 
        return true;
    }

    @Override
    public boolean isUnwrappableAs(Class arg0) {
        return false;
    }

    @Override
    public <T> T unwrap(Class<T> arg0) {
        return null;
    }

    }









public class CustomDataSource extends BasicDataSource{
    private static final Logger log = Logger.getLogger(CustomDataSource.class);


    public CustomDataSource(){  
        Properties props = HibernateConfiguration.getProperties(); 
          setDriverClassName( props.getProperty("hibernate.connection.driver_class")  );
          setUsername(  props.getProperty("hibernate.connection.username") );
          setPassword( props.getProperty("hibernate.connection.password")  );
          setUrl( props.getProperty("hibernate.connection.url")  ); 
          setValidationQuery("select 1");
          setTestOnBorrow(true);

    }

    @Override
    public Connection getConnection() throws SQLException{ 

        return   new net.sf.log4jdbc.ConnectionSpy( super.getConnection() );
    }
}

Collectives™ on Stack Overflow

Hibernate connection timeout

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related