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

Solution 1

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)

Solution 2

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