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

1,202 阅读2分钟

“携手创作,共同成长!这是我参与「掘金日新计划 · 8 月更文挑战」的第8天,点击查看活动详情

问题描述

设置了多线程访问数据库,断点调试的时候,到了断点位置暂停了一会儿后继续运行报错了。

2022/08/04 10:49:22.021 async-task-thread-pool-2 [ERROR] HikariPool (HikariPool.java:593) HikariPool-3 - Exception during pool initialization.
 com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
......

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_241]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_241]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_241]
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_241]
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.19.jar:8.0.19]
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) ~[mysql-connector-java-8.0.19.jar:8.0.19]
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) ~[mysql-connector-java-8.0.19.jar:8.0.19]
	at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167) ~[mysql-connector-java-8.0.19.jar:8.0.19]
	at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:91) ~[mysql-connector-java-8.0.19.jar:8.0.19]
	at com.mysql.cj.NativeSession.connect(NativeSession.java:144) ~[mysql-connector-java-8.0.19.jar:8.0.19]
	at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:956) ~[mysql-connector-java-8.0.19.jar:8.0.19]
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:826) ~[mysql-connector-java-8.0.19.jar:8.0.19]
	... 66 more
Caused by: java.net.ConnectException: 拒绝连接 (Connection refused)
	at java.net.PlainSocketImpl.socketConnect(Native Method) ~[?:1.8.0_241]
	at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350) ~[?:1.8.0_241]
	at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206) ~[?:1.8.0_241]
	at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188) ~[?:1.8.0_241]
	at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392) ~[?:1.8.0_241]
	at java.net.Socket.connect(Socket.java:606) ~[?:1.8.0_241]
	at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:155) ~[mysql-connector-java-8.0.19.jar:8.0.19]
	at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:65) ~[mysql-connector-java-8.0.19.jar:8.0.19]
	at com.mysql.cj.NativeSession.connect(NativeSession.java:144) ~[mysql-connector-java-8.0.19.jar:8.0.19]
	at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:956) ~[mysql-connector-java-8.0.19.jar:8.0.19]
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:826) ~[mysql-connector-java-8.0.19.jar:8.0.19]
	... 66 more

问题解决

在网上查到了下面的解决方法,做此记录,亲测有效!(可能是因为长时间不访问,造成connection失效)

解决方法:

(1)使用JDBC URL中使用autoReconnect属性,url添加

&autoReconnect=true&failOverReadOnly=false&maxReconnects=10

(2)修改MySQL的参数. /etc/my.cnf 添加

wait_timeout=31536000  
interactive_timeout=31536000  

(3)重启MySQL

service mysql restart

(4)可以通过配置,让缓冲池去测试连接是否被回收,如果被回收,则不继续使用,以dbcp为例:

  #SQL查询,用来验证从连接池取出的连接
  dbcp.validationQuery=SELECT 1
  #指明连接是否被空闲连接回收器(如果有)进行检验,如果检测失败,则连接将被从池中去除
  dbcp.testWhileIdle=true
  #在空闲连接回收器线程运行期间休眠的时间值,以毫秒为单位,一般比minEvictableIdleTimeMillis小
  dbcp.timeBetweenEvictionRunsMillis=300000
  #在每次空闲连接回收器线程(如果有)运行时检查的连接数量,最好和maxActive一致
 dbcp.numTestsPerEvictionRun=50
  #连接池中连接,在时间段内一直空闲,被逐出连接池的时间(1000*60*60),以毫秒为单
  dbcp.minEvictableIdleTimeMillis=3600000