关于MySQL的wait_timeout连接超时问题报错解决方案

Mysql服务器默认的“wait_timeout”是8小时【也就是默认的值默认是28800秒】，也就是说一个connection空闲超过8个小时，Mysql将自动断开该connection，通俗的讲就是一个连接在8小时内没有活动，就会自动断开该连接。 wait timeout的值可以设定，但最多只能是2147483，不能再大了。也就是约24.85天  所以即使你MySQL通过my.ini 在  # The TCP/IP Port the MySQL Server will listen on port=3306下面添加  # this is myown dinifition for mysql connection timeout wait_timeout=31536000  interactive_timeout=31536000  无论超过最大限度多大的数值，只能被MySQL解析为2147483，2147483天后你的程序该出什么错还是什么错，避免不了的

1.1 错误信息：

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'. 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.
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 解决方法

- 如果使用的是JDBC，在JDBC URL上添加?autoReconnect=true，如：

jdbc:mysql://10.10.10.10:3306/mydb?autoReconnect=true
- 如果是在Spring中使用DBCP连接池，在定义datasource增加属性validationQuery和testOnBorrow，如：

<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>
- 如果是在Spring中使用c3p0连接池，则在定义datasource的时候，添加属性testConnectionOnCheckin和testConnectionOnCheckout，如：

<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>
参考

MySQL reconnect issues


附录分析

When a c3p0-proxied Connection throws an SQLException, c3p0 examines 
the Exception and the Connection to make a judgement about whether 
the problem implies that the Connection should no longer be included 
in the pool. c3p0 tests the Connection, and if the test fails,  the 
Connection will be excluded from the pool.

What c3p0 is telling you here is that a Connection that previously 
signalled an error and then failed a Connection test is still in use, 
and has signalled another error. From c3p0's perspective, this is a 
non-issue, it just means c3p0 doesn't have to do any kind of checks 
or notifications, the Connection is already gone as far as the pool 
is concerned. But c3p0 wonders why you'd still be using such a 
Connection, and warns you about it.

Usually, if a client continues to use a Connection that c3p0 has 
correctly identified as broken, all further uses will provoke such an 
exception, and the fix is to close the Connection and start over when 
an application's Connection turns 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 with the database. So, the issue is, 
why did c3p0 deem the Connection dead if it is not?

If you turn on DEBUG level logging (relevant loggers would be 
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 Connections 
that seem okay are testing as broken. That will 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 through the pool. 
c3p0 is erring on the side of caution -- it has reason to believe a 
Connection is bad, so it's been excluded from the pool. It'd be nice 
to know why apparently good Connections are failing Connection tests, 
but if it is an infrequent occurrence, it's very little to worry 
about. (If it's happening a lot, you should track it down.)
原文地址：http://sourceforge.net/p/c3p0/mailman/message/18310863/


mysql重连，连接丢失：The last packet successfully received from the server--转载

http://www.cnblogs.com/davidwang456/p/4425913.html