记springboot项目启动Communications link failure

admin管理员组

文章数量:1618723

1.项目启动时报如下异常：

2020-02-26 10:09:36.387 ERROR [main] ExApplicationRunListener:223-- 启动失败
com.acooly.module.scheduler.exceptions.SchedulerEngineException: [task1]启动失败:[task1]启动失败:Failure obtaining db row lock: Lock wait timeout exceeded; try restarting transaction
2020-02-26 10:23:37.634 ERROR [main] DruidPooledStatement:357-- CommunicationsException, druid version 1.1.10, jdbcUrl : jdbc:mysql:数据库连接地址?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false&rewriteBatchedStatements=true, testWhileIdle true, idle millis 12627, minIdle 20, poolingCount 4, timeBetweenEvictionRunsMillis 300000, lastValidIdleMillis 12627, driver com.mysql.jdbc.Driver, exceptionSorter com.alibaba.druid.pool.vendor.MySqlExceptionSorter
2020-02-26 10:23:37.635 ERROR [main] DruidDataSource:1602-- discard connection
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 12,624 milliseconds ago.  The last packet sent successfully to the server was 12,624 milliseconds ago.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3562)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3462)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3903)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3188)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3185)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:181)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:228)
	at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:96)
	at org.quartz.impl.jdbcjobstore.DBSemaphore.obtainLock(DBSemaphore.java:113)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.doCheckin(JobStoreSupport.java:3328)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport$ClusterManager.manage(JobStoreSupport.java:3920)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport$ClusterManager.run(JobStoreSupport.java:3957)
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
	at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3014)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3472)
	... 17 common frames omitted

Mysql服务器默认的“wait_timeout”是8小时【也就是默认的值默认是28800秒】，也就是说一个connection空闲超过8个小时，Mysql将自动断开该connection，通俗的讲就是一个连接在8小时内没有活动，就会自动断开该连接。而应用连接池却认为该连接还是有效的(因为并未校验连接的有效性)，当应用申请使用该连接时，就会导致上面的报错。

临时解决方案：
1.数据库使用SHOW PROCESSLIST命令查询连接；

2.根据上述查询结果，初步判断为statistics查询长时间占用导致，使用如下命令解决问题：

kill id;
SELECT * FROM  information_schema.innodb_trx;
kill trx_mysql_thread_id;

永久解决方案

1、按照错误的提示，可以在JDBC URL中使用autoReconnect属性，实际测试时使用了autoReconnect=true&failOverReadOnly=false，不过并未起作用，使用的是5.1版本，可能真像网上所说的只对4之前的版本有效。

2、增大mysql的wait_timeout属性值；(起不到实质性作用)

show variables like '%timeout';

3、最好的解决方法，如果使用了c3p0连接池，可以配置idleConnectionTestPeriod 属性，每多少秒检查所有连接池中的空闲连接，把该值设置的小于mysql的wait_timeout值即可。

本文标签： 项目SpringBootCommunicationsfailureLINK