MySQL reconnection, connection lost: The last packet successfully received from the server

Original address: http://nkcoder.github.io/blog/20140712/mysql-reconnect-packet-lost/

1.1 Error message:

caused By:com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:The last packet successfully received from the  Server was 20,820,001 milliseconds ago. The last packet sent successfully to the server was 20,820,002 milliseconds ago. is longer than the server configured value of ' Wait_timeout '. Should consider either expiring and/or testing connection validity before use in your application, increasing the Serv Er configured values for client timeouts, or using the Connector/j Connection property ' Autoreconnect=true ' to avoid this problem.at sun.reflect.GeneratedConstructorAccessor29.newInstance (Unknown Source) ~[na:na]at Sun.reflect.DelegatingConstructorAccessorImpl.newInstance (delegatingconstructoraccessorimpl.java:45) ~[na:1.7.0 _51]at java.lang.reflect.Constructor.newInstance (constructor.java:526) ~[na:1.7.0_51]at Com.mysql.jdbc.Util.handleNewInstance (util.java:411) ~[mysql-connector-java-5.1.29.jar:na]at Com.mysql.jdbc.SQLError.createCommunicationsException (sqlerror.java:1129) ~[mysql-connector-java-5.1.29.jar:na]at Com.mysql.jdbc.MysqlIO.send (mysqlio.java:3988) ~[ Mysql-connector-java-5.1.29.jar:na]at Com.mysql.jdbc.MysqlIO.sendCommand (mysqlio.java:2598) ~[ Mysql-connector-java-5.1.29.jar:na]at Com.mysql.jdbc.MysqlIO.sqlQueryDirect (mysqlio.java:2778) ~[ Mysql-connector-java-5.1.29.jar:na]at Com.mysql.jdbc.ConnectionImpl.execSQL (connectionimpl.java:2828) ~[ Mysql-connector-java-5.1.29.jar:na]at com.mysql.jdbc.ConnectionImpl.setAutoCommit (connectionimpl.java:5372) ~[ Mysql-connector-java-5.1.29.jar:na]at Com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit ( newproxyconnection.java:881) ~[c3p0-0.9.1.1.jar:0.9.1.1]at Org.quartz.impl.jdbcjobstore.AttributeRestoringConnectionInvocationHandler.setAutoCommit ( attributerestoringconnectioninvocationhandler.java:98) ~[quartz-2.2.1.jar:na]

1.2 Workaround-If you are using JDBC, add it on the JDBC URL ?autoReconnect=trueSuch as:

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

-If you are using DBCP connection pool in spring, add properties in the definition datasource validationQueryAnd testOnBorrowSuch as:

<bean id="vrsRankDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">    <property name="driverClassName" value="${jdbc.driverClassName}" />    <property name="url" value="${countNew.jdbc.url}" />    <property name="username" value="${countNew.jdbc.user}" />    <property name="password" value="${countNew.jdbc.pwd}" />    <property name="validationQuery" value="SELECT 1" />    <property name="testOnBorrow" value="true"/></bean>

-If you are using C3P0 connection pool in spring, when defining DataSource, add properties testConnectionOnCheckinAnd testConnectionOnCheckoutSuch as:

<bean name="cacheCloudDB" class="com.mchange.v2.c3p0.ComboPooledDataSource">    <property name="driverClass" value="${jdbc.driver}"/>    <property name="jdbcUrl" value="${cache.url}"/>    <property name="user" value="${cache.user}"/>    <property name="password" value="${cache.password}"/>    <property name="initialPoolSize" value="10"/>    <property name="maxPoolSize" value="${cache.maxPoolSize}"/>    <property name="testConnectionOnCheckin" value="false"/>    <property name="testConnectionOnCheckout" value="true"/>    <property name="preferredTestQuery" value="SELECT 1"/></bean>

Reference

• MySQL Reconnect issues

Appendix Analysis

When a c3p0-proxied Connection throws an SQLException, C3p0 examines the Exception and the Connection to make a judgement About whether the problem implies, the Connection should no longer is included in the pool. C3P0 tests the Connection, and if the test fails, the Connection would be excluded from the pool. What c3p0 are telling you are here's a Connection that previously signalled an error and then failed a Connection test I s still in use, and have signalled another error.  From C3p0 ' s perspective, this is a non-issue, it just means c3p0 doesn ' t has to does any kind of checks or notifications, The Connection is already gone as far as the pool is concerned. But C3p0 wonders why are you ' d still is using such a Connection, and warns you about it. Usually, if a client continues to use a Connection that c3p0 have correctly identified as broken, all further uses would PR Ovoke such an exception, and the and the fix are to close the Connection and start over if an application ' s ConnectionTurns out to be dead. But, by the error you ' re getting, it looks like your Connection is still live and okay--it's clearly communicating wit H the database. So, the issue was, why did C3P0 deem the Connection dead if it was not? If you turn on the DEBUG level logging (relevant loggers would is com.mchange.v2.c3p0.impl.NewPooledConnection, Com.mchange. V2.c3p0.impl.C3P0PooledConnectionPool, and Com.mchange.v2.c3p0.impl.DefaultConnectionTester, unless you ' ve defined Your own connectiontester), you can trace the testing and invalidation of Connections, and try to understand why CONNECTI ONS that seem okay is testing as broken. That'll give you better information about what's going on. That said, the only cost of this behavior is disconcerting warning messages and somewhat faster churn of Connections thro  Ugh the pool. C3P0 was erring on the side of caution--it had reason to believe a Connection was bad, so it's been excluded from the poo L. It ' d is nice to know why apparently good COnnections was failing Connection tests, but if it was an infrequent occurrence, it's very little to worry about. (If it ' s happening a lot, you should track it down.)
Original address: http://sourceforge.net/p/c3p0/mailman/message/18310863/

MySQL reconnection, connection lost: The last packet successfully received from the server