后台报com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failu问题的解决方案

Maynor

发布于 2021-12-06 19:58:35

1.4K0

发布于 2021-12-06 19:58:35

问题：今早，发现昨天部署的JAVA-WEB服务，不正常启动了，于是查看后台日志，发现

11:07:05.358 [Druid-ConnectionPool-Create-1102883776] ERROR com.alibaba.druid.pool.DruidDataSource - create connection holder error
 com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
The last packet successfully received from the server was 1,003 milliseconds ago. The last packet sent successfully to the server was 1,003 milliseconds ago.
 at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:172) ~[mysql-connector-java-8.0.11.jar:8.0.11]
 at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64) ~[mysql-connector-java-8.0.11.jar:8.0.11]
 at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:1518) ~[mysql-connector-java-8.0.11.jar:8.0.11]
 at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:1487) ~[mysql-connector-java-8.0.11.jar:8.0.11]
 at com.alibaba.druid.pool.DruidConnectionHolder.(DruidConnectionHolder.java:143) ~[druid-1.0.29.jar:1.0.29]
 at com.alibaba.druid.pool.DruidConnectionHolder.(DruidConnectionHolder.java:83) ~[druid-1.0.29.jar:1.0.29]
 at com.alibaba.druid.pool.DruidDataSource.put(DruidDataSource.java:1890) [druid-1.0.29.jar:1.0.29]
 at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2143) [druid-1.0.29.jar:1.0.29]
 Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure
 解决：
 打开navicat，进行解决

1、选择navicat的"工具–>命令列界面"

2、查看WAIT_TIMEOUT的值
 mysql> show global variables like ‘wait_timeout’;
 ±--------------±------+
 | Variable_name | Value |
 ±--------------±------+
 | wait_timeout | 28800 |
 ±--------------±------+
 1 row in set (0.24 sec)
mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in (‘interactive_timeout’,‘wait_timeout’);
 1109 - Unknown table ‘SESSION_VARIABLES’ in information_schema
 mysql> set session WAIT_TIMEOUT=1814400;
 Query OK, 0 rows affected (0.01 sec)
mysql> show global variables like ‘wait_timeout’;
 ±--------------±------+
 | Variable_name | Value |
 ±--------------±------+
 | wait_timeout | 28800 |
 ±--------------±------+
 1 row in set (0.03 sec)
mysql> show global variables like ‘timeout’;
 Empty set
mysql> set global WAIT_TIMEOUT=1814400;
 Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like ‘wait_timeout’;
 ±--------------±--------+
 | Variable_name | Value |
 ±--------------±--------+
 | wait_timeout | 1814400 |
 ±--------------±--------+
 1 row in set (0.04 sec)
mysql>

3、再次刷新JAVA-WEB界面，成功

总结：

控制连接最大空闲时长的wait_timeout参数。
对于非交互式连接，类似于jdbc连接，wait_timeout的值继承自服务器端全局变量wait_timeout（set session WAIT_TIMEOUT=1814400;是不起作用的，只有set global WAIT_TIMEOUT=1814400;才行）对于交互式连接，类似于mysql客户单连接，wait_timeout的值继承自服务器端全局变量interactive_timeout。
判断一个连接的空闲时间，可通过show processlist输出中Sleep状态的时间。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2021/12/03 ，如有侵权请联系 cloudcommunity@tencent.com 删除

java