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

转载 2017年01月03日 15:47:20

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增加属性validationQuerytestOnBorrow,如:

<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的时候,添加属性testConnectionOnCheckintestConnectionOnCheckout,如:

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

参考

 

附录分析

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

问题原因: 其实上面的提示中已经给出了一部分的简要说明,简单来说就是: 程序启动时,在跟DB首次交互时,获得了相应的DB Connection资源,从而进行正常的DB读写操作。但是在下次进行DB...
  • dxswzj
  • dxswzj
  • 2015年01月16日 15:43
  • 2111

MySQL之——重连,连接丢失:The last packet successfully received from the serve

最近,项目中经常遇到MySQL连接丢失的问题,研究了下解决方法,现共享出来,大家可以参考一下,下面我们就进入正题。 1、错误日志 Caused by: com.mysql.jdbc.exception...

The last packet successfully received from the server was 30,516,920 milliseconds ago.

今天早上发了一笔交易 发现服务器上报错 可见这笔交易是9点半左右发出的 入库的时候发现连接已经断掉,第一反应先计算了一下断掉的时间30516920/3600/1000 = 8.476922222222...

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications ...
  • fuxuejun
  • fuxuejun
  • 2011年04月07日 08:43
  • 23574

The last packet successfully received fro m the server was 63,020,509 milliseconds ago

自己的小网站在测试机器上长时间不访问后(默认8小时过期),再次访问发现有如下错误: Caused by: com.mysql.jdbc.exceptions.jdbc4.Communications...

The last packet sent successfully to the server was 0 milliseconds ago.

今天在使用JDBC操作mysql时遇到下面的异常信息:  引用 The last packet sent successfully to the server was 0 millisec...

The last packet sent successfully to the server was 0 milliseconds ago问题的解决

这个异常”The last packet sent successfully to the server was xxx milliseconds ago.“有一部分原因是由于数据库回收了连接,而系统...

mysql Communications link failure Last packet sent to the server was X ms ago

想必大家在用MySQL时都会遇到连接超时的问题,如下图所示: 就是这个异常(com.mysql.jdbc.exceptions.jdbc4.Communication***ception:...

关于activiti连接数据库超时问题—— The driver has not received any packets from the server

The last packet sent successfully to the server was 0 milliseconds ago

基于tungsten API 同步mysql binlog出现EOF packet received的问题解决

tungsten是一个开源的数据库同步工具,详细可参考官网(http://en.wikipedia.org/wiki/Tungsten)         项目需要,需要实时知道mysql更新的数据,...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:mysql重连,连接丢失:The last packet successfully received from the server--转载
举报原因:
原因补充:

(最多只允许输入30个字)