mysql重连,连接丢失:The last packet successfully received from the server--转载
2023-09-11 14:21:41 时间
原文地址:http://nkcoder.github.io/blog/20140712/mysql-reconnect-packet-lost/
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>
参考
附录分析
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/
相关文章
- (转)Amoeba for MySQL 非常好用的mysql集群软件
- file /usr/lib64/mysql/plugin/dialog.so from install of Percona-Server-server-56-5.6.24-rel72.2.el6.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
- 用navicat连接数据库报错:1130-host ... is not allowed to connect to this MySql server如何处理
- mysql远程连接 Host * is not allowed to connect to this MySQL server
- Mysql错误check the manual that corresponds to your MySQL server version for the right syntax
- 【MySQL进阶-10】mysql语句的执行流程以及集群的高可用
- 【MySQL进阶-02】mysql的explain执行计划以及索引优化
- MySQL root修改普通用户密码及Mysql 修改root密码
- 【Docker】安装Presto连接Hive、mysql、oracle、postgresql、SQL server等7种类型数据库
- Host is not allowed to connect to this MySQL server解决方法
- 【mysql报错】ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket ‘/tmp/mysql.sock‘
- 《SQL初学者指南(第2版)》——1.2 Microsoft SQL Server、MySQL和Oracle
- 《SQL初学者指南》——1.3 Microsoft SQL Server、Oracle和MySQL
- mac ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
- 使用navicat连接mysql连接错误:Lost connection to Mysql server at 'waiting for initial communication packet'
- 转 mysql spool_用MySQL实现类似Oracle SPOOL的功能
- 转发 可设置skip_name_resolve参数 会出现 ERROR 2005 (HY000): Unknown MySQL server host _mysql ...
- python操作mysql数据库系列-操作MySql数据库(二)
- Mysql命令mysql:连接Mysql数据库
- Windows 免安装版mysql,适用于版本MySQL 5.7.7及以上,快速配置教程
- mysql远程连接 Host is not allowed to connect to this MySQL server
- mysql导出数据报错The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
- 【MySQL】mysql更换root密码,全网唯一有用!
- 【sql server压测】sql server使用Diskspd测试SQL Server IO存储
- MySQL性能优化的21个最佳实践 和 mysql使用索引
- MySQL 安装mysql数据库
- Mysql 1290 - The MySQL server is running with the --secure-file-priv option
- ull, message from server: "Host '112.111.61.200' is not allowed to connect to this MySQL server"
- sql server 表2字段更新到表1,mysql
- Navicat连接数据库出现 is not allowed to connect to this MySQL server 报错