mysql异常CommunicationsException: Communications link failure的解决方案

喜欢ヅ旅行 2022-07-13 01:09 837阅读 0赞

com.mysql.jdbc.exceptions.jdbc4.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.

原因查找

正常可以连接msql suse 运行以下命令情况如下：

1. 运行命令netstat -talnp验证3306端口是否打开：

test:~ \# netstat -talnp  
Active Internet connections (servers and established)  
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name   
tcp 0 0 0.0.0.0:3306 0.0.0.0:\* LISTEN 4620/mysqld

。。。。。。

2.运行命令 ps aux|grep mysql看mysql服务是否在运行

test:~ \# ps aux|grep mysql  
root 4254 0.0 0.0 2940 1332 ? S 06:05 0:00 /bin/sh /usr/local/mysql/bin/mysqld\_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/test.pid  
mysql 4620 0.1 0.9 301476 37428 ? Sl 06:05 0:13 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/test.err --pid-file=/usr/local/mysql/data/test.pid --socket=/usr/local/mysql/mysql.sock --port=3306  
root 8002 0.0 0.0 2264 680 pts/2 S+ 09:21 0:00 grep mysql

3.查看3306端口运行的进程 lsof -i :3306  
test:~ \# lsof -i :3306  
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME  
mysqld 4620 mysql 12u IPv4 10559 0t0 TCP \*:mysql (LISTEN)

问题机器运行上面运行命令

1.netstat -talnp 看不到绑定到3306端口的tcp连接

2.ps aux|grep mysql 显示服务在运行

3. lsof -i :3306查看mysql是否运行在3306端口，未有显示mysql服务没有运行在3306端口

结论：mysql服务虽然在运行，可以通过teminal操作，但没有运行在3306端口，所以通过jdbc连接连接不上。

4.若不是以上问题，则可能是大量数据访问情况下，mysql connection连接有可能失效 或者长时间不访问，connection会失效

这时需要 修改MySQL的参数. /etc/my.cnf 添加   
\[mysqld\]  
wait\_timeout=31536000  
interactive\_timeout=31536000  
重启mysql   
service mysql restart（5.7版本mysql可能是service mysqld restart)

后查看网络资料mysql配置中skip-networking未注释，参数skip-networking起的作用是：mysql不再在TCP / IP端口上进行监听，与mysqld的所有互动都必须通过Unix套接字或命名管道进行。

配置文件注释如下

\# Don't listen on a TCP/IP port at all. This can be a security enhancement,  
\# if all processes that need to connect to mysqld run on the same host.  
\# All interaction with mysqld must be made via Unix sockets or named pipes.  
\# Note that using this option without enabling named pipes on Windows  
\# (via the "enable-named-pipe" option) will render mysqld useless!

将参数skip-networking注释重启mysql服务，jdbc程序连接mysql正常。

网络遇见同样异常，可能的其它原因：

tomcat程序连接服务器mysql[数据库][Link 1]，报

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure  
The last packet sent successfully to the server was 0 milliseconds ago. The driverhas not received any packets from the server.  
错误，后来用java程序直接连接数据库，还是一样的错误，数据库连接，用户名密码都没有问题。  
在网上查了很多情况，什么数据库用户权限设置，还是数据库连接等待时间(wait\_timeout)设置，都没用。  
而连接本地的数据库就没问题，本地数据库版本：5.0.37，服务器数据库版本：5.0.19，JDBC驱动版本：  
mysql-connector-java-5.1.9-bin.jar。  
这个问题困扰了一天，后来想想换换5.0的驱动看看怎么样，结果在网上找了个高版本比如mysql-connector-java-5.1.14-bin.jar的驱动，结果OK了。

2.以下来自stackoverflow

CommunicationsException: Communications link failure

If you get a SQLException: Connection refused or Connection timed out or a MySQL specific CommunicationsException: Communications link failure, then it means that the DB isn't reachable at all. This can have one or more of the following causes:

IP address or hostname in JDBC URL is wrong.  
Hostname in JDBC URL is not recognized by local DNS server.  
Port number is missing or wrong in JDBC URL.  
DB server is down.  
DB server doesn't accept TCP/IP connections.  
DB server has run out of connections.  
Something in between [Java][] and DB is blocking connections, e.g. a firewall or proxy.

To solve the one or the other, follow the following advices:

Verify and test them with ping.  
Refresh DNS or use IP address in JDBC URL instead.  
Verify it based on my.cnf of MySQL DB.  
Start the DB.  
Verify if [mysql][]d is started without the --skip-networking option.  
Restart the DB and fix your code accordingly that it closes connections in finally.  
Disable firewall and/or configure firewall/proxy to allow/forward the port.

[Link 1]: http://www.2cto.com/database/
[Java]: http://www.2cto.com/kf/ware/Java/
[mysql]: http://www.2cto.com/database/MySQL/