MySQL reconnect issues or ‘The last packet successfully received from the server XX milliseconds ago’ errors

If you encounter problems with stale connections in your pools after long period of inactivity, like this for instance:

2013-07-18 08:40:21,671[ERROR,JDBCExceptionReporter] The last packet successfully received from the server was 50,286,413 milliseconds ago. The last packet sent successfully to the server was 50,286,809 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.
org.hibernate.exception.JDBCConnectionException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:99)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2545)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
at org.hibernate.loader.Loader.list(Loader.java:2271)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)

You will have to follow the following steps:

1. add ?autoReconnect=true to you jdbc URL

jdbc:mysql://:3306/?autoReconnect=true

2a. if you use DBCP as connection pool in Spring, add the following entries to your datasource definition:

<bean id=”datasource” destroy-method=”close”>

<property name=”driverClassName” value=”${.jdbc.driver}” />

<property name=”url” value=”${jdbc.url}” />

<property name=”username” value=”${jdbc.username}” />

<property name=”password” value=”${jdbc.password}” />

<property name=”validationQuery” value=”SELECT 1″ />

<property name=”testOnBorrow” value=”true” />

</bean>

 

2b. if you use c3p0 as connection pool in Spring, add the following entries to your datasource definition:

<bean id=”pooledDataSource” class=”com.mchange.v2.c3p0.ComboPooledDataSource” destroy-method=”close”>

<property name=”driverClass” value=”${driver}”></property>

<property name=”jdbcUrl” value=”${url}” />

<property name=”user” value=”${username}” />

<property name=”password” value=”${password}” />

<property name=”initialPoolSize” value=”20″ />

<property name=”minPoolSize” value=”10″ />

<property name=”maxPoolSize” value=”50″ />

<property name=”checkoutTimeout” value=”1000″ />

<property name=”maxStatements” value=”500″ />

<property name=”testConnectionOnCheckin” value=”false” />

<property name=”testConnectionOnCheckout” value=”true” />

<property name=”maxIdleTime” value=”1800″ />

<property name=”idleConnectionTestPeriod” value=”1000″ />

</bean>

Advertisements
This entry was posted in Enterprise Java and tagged , . Bookmark the permalink.

2 Responses to MySQL reconnect issues or ‘The last packet successfully received from the server XX milliseconds ago’ errors

  1. rlunaro says:

    Hello:
    I had a similar case than yours, and in my case the problem was that I had a open Hibernate Session object too much time. In fact, more time than the value of wait_timetout of the mysql server.

    it is easy to check it, here is an example:

    http://www.supermanhamuerto.com/doku.php?id=java:thelastpacketsuccessfullyreceivedfromserver

    Just if it were the case or the case of other people. It is commom that, when struggling finding a solution, you end in changing wait_timeout and this inadvertenly make the problem dissapear.

  2. Thi Pham says:

    Thank you so much Itellity. It works for me ^^

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s