CODEWITHSUNDEEP
javamysqlhibernate
chrisTina
chrisTina

Reputation: 2368

CommunicationsException when using Hibernate

I have configured a Hibernate DAO in my application. Once a client ping that service, my server will use this DAO to connect to the database and retrieve some data.

This works fine for a couple of hours (or maybe one day), and then some thing goes wrong. I got exception like this:

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 48,761,817 milliseconds ago. The last packet sent successfully to the server was 48,761,899 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

Once I redeploy my service, everything back to normal again. My codes is like this:

public class DeploymentInfoDAO {

private static SessionFactory factory = null;

private DeploymentInfo deploymentInfo = null;

private CheckLockStatusService checkLockStatusService = new CheckLockStatusService();

/**
 * Constructor
 */
public DeploymentInfoDAO() {

}

/**
 * Constructor
 * 
 * @param deploymentInfo
 */
public DeploymentInfoDAO(DeploymentInfo deploymentInfo) {
    this.deploymentInfo = deploymentInfo;
    // this.checkLockStatusService = new CheckLockStatusService();
}

/**
 * SELECT * FROM servicedashboard and converts the results into a list of
 * ServiceRow
 * 
 * @return
 */
public List<ServiceRow> getServiceRows() {
    List<ServiceRow> serviceRows = new ArrayList<ServiceRow>();
    if (factory == null){
        try {
            factory = new Configuration().configure().buildSessionFactory();
        } catch (Throwable ex) {
            System.err.println("Failed to create sessionFactory object." + ex);
            throw new ExceptionInInitializerError(ex);
        }
    }


    // mysql select test
    Session session = factory.openSession();
    Transaction tx = null;
    try {
        tx = session.beginTransaction();
        List<DeploymentInfo> deploymentInfos = session
                .createQuery(
                        "FROM com.test.hibernate.pojo.DeploymentInfo")
                .list();

        for (Iterator<DeploymentInfo> iterator = deploymentInfos.iterator(); iterator
                .hasNext();) {
            DeploymentInfo deploymentInfo = iterator.next();

            ServiceRow serviceRow = this
                    .convertToServiceRow(deploymentInfo);
            serviceRows.add(serviceRow);
        }
        tx.commit();
    } catch (HibernateException e) {

        if (tx != null)
            tx.rollback();
        e.printStackTrace();
    } finally {
        session.close();
    }
    return serviceRows;

}
}

Basically, every client call will create an DeploymentInfoDAO, and it will keep using the same static SessionFactory once it is created until it is not null.

It seems that after several hours, this SessionFactory became invalid but still not null.

What's the best practice to modify the code? And also, in the exception, it suggest using the Connector/J connection property 'autoReconnect=true' to avoid this problem, how to do this?

Upvotes: 0

Views: 266

Answers (3)

Alberto
Alberto

Reputation: 795

Years ago I had the same problem. This is because MySQL close connection after a time of inactivity, it is not problem of your application.

One solution is create a dummy query (SELECT 1) and execute it every hour (or time that you estimate). This avoid MySQL closes connection.

Another it is increase the time limit of MySQL (This solution I don't like it, many times cannot modify database configuration for some reasons).

More information:

Terminating idle mysql connections

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout

I hope this help you.

Upvotes: 0

javadev
javadev

Reputation: 1669

Try to increase the wait timeout in your server or enable the auto reconnect in your JDBC URL.

Upvotes: 0

Pulkit
Pulkit

Reputation: 4084

Actually your server terminates the connection with MySQL after 8 hours of inactivity (8 hours is default) you can use autoReconnect=true in jdbc url

Syntax

jdbc:mysql://<host>:<port>/<db>?autoReconnect=true

Upvotes: 2

Related Questions