Mysql远程连接丢失问题 Communications link failure

有一个远程Mysql数据库，在跑一一个php的应用的时候，发送了 MySQL server has gone away的情况，查了一下网上，大多数都是如下的解答，wait_timeout mysql默认8小时，我就没动那个参数，把max_allowed_packet 改成16M（原来只有1M）之后就没有发生过连接丢失的现象了了。考虑到原来这台服务器上的mysql作为一个java的应用的数据库的时候也经常发生connect lost的情况，不知道是否也是同样的原因。

这两天测试发现那个java应用还是经常发生mysql连接丢失，因为那台mysql服务器是虚拟机上创建的，当时分配的内存也只有4G，CPU也比较慢，

[DEBUG]_2016-04-04 14:45:37 :com.mchange.v2.c3p0.impl.NewPooledConnection@152bf998 handling a throwable.

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

 

The last packet successfully received from the server was 170,289 milliseconds ago.  The last packet sent successfully to the server was 1 milliseconds ago.

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

at java.lang.reflect.Constructor.newInstance(Constructor.java:526)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)

at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3567)

at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3456)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3997)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)

at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)

at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:368)

 

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

 

at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1070)

at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)

at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Thread.java:745)

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:3017)

at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3467)

 

... 157 more

这个问题困扰了我好久，网上很多关于设置connect_timeout,interactive_timeout,wait_timeout也不起作用

无意间show variables看到| net_read_timeout                | 30               |

| net_write_timeout               | 30               |这两个参数，

在/etc/my.cnf里

net_read_timeout = 120

 

net_write_timeout = 120

把设置长一点看看，就把net_read_timeout，net_write_timeout设置成了120.

结果灵验了，运行了很久也没有发生过 Communications link failure了。

 

mysql> show variables like '%time%';

+---------------------------------+-------------------+

| Variable_name                   | Value             |

+---------------------------------+-------------------+

| binlog_max_flush_queue_time     | 0                 |

| connect_timeout                 | 300               |

| datetime_format                 | %Y-%m-%d %H:%i:%s |

| delayed_insert_timeout          | 300               |

| explicit_defaults_for_timestamp | OFF               |

| flush_time                      | 0                 |

| innodb_flush_log_at_timeout     | 1                 |

| innodb_lock_wait_timeout        | 50                |

| innodb_old_blocks_time          | 1000              |

| innodb_rollback_on_timeout      | OFF               |

| interactive_timeout             | 28800             |

| lc_time_names                   | en_US             |

| lock_wait_timeout               | 31536000          |

| long_query_time                 | 10.000000         |

| net_read_timeout                | 120               |

| net_write_timeout               | 120               |

| rpl_stop_slave_timeout          | 31536000          |

| slave_net_timeout               | 3600              |

| slow_launch_time                | 2                 |

| system_time_zone                | CST               |

| time_format                     | %H:%i:%s          |

| time_zone                       | SYSTEM            |

| timed_mutexes                   | OFF               |

| timestamp                       | 1459753718.898598 |

| wait_timeout                    | 28800             |

+---------------------------------+-------------------+

 

25 rows in set (0.00 sec)

 

考虑是不是实际上服务器的性能和网络不稳定的原因问题，

 

slave-net-timeout
The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the MASTER_CONNECT_RETRY option for the CHANGE MASTER TO statement, and the number of reconnection attempts is limited by the --master-retry-count option. The default is 3600 seconds (one hour).

当slave认为连接master的连接有问题时，就等待N秒，然后断开连接，重新连接master

net_read_timeout ：
The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort

在终止读之前，从一个连接获得数据而等待的时间秒数；当服务正在从客户端读取数据时，net_read_timeout控制何时超时。

 

net_write_timeout:
The number of seconds to wait for a block to be written to a connection before aborting the write.When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort。

在终止写之前，等待多少秒把block写到连接；当服务正在写数据到客户端时，net_write_timeout控制何时超时

wait_timeout
The number of seconds the server waits for activity on a noninteractive connection before closing it.

与服务器端无交互状态的连接，直到被服务器端强制关闭而等待的时间

interactive_timeout ：
The number of seconds the server waits for activity on an interactive connection before closing it.

与服务器端无交互状态的连接，直到被服务器端强制关闭而等待的时间

connect_timeout
The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds.

等待一个连接响应的时间

 

connect_timeout：在获取连接阶段（authenticate）起作用
interactive_timeout和wait_timeout：在连接空闲阶段（sleep）起作用
net_read_timeout和net_write_timeout：则是在连接繁忙阶段（query）起作用。

 

获取MySQL连接是多次握手的结果，除了用户名和密码的匹配校验外，还有IP->HOST->DNS->IP验证，任何一步都可能因为网络问题导致线程阻塞。为了防止线程浪费在不必要的校验等待上，超过connect_timeout的连接请求将会被拒绝。

 

即使没有网络问题，也不能允许客户端一直占用连接。对于保持sleep状态超过了wait_timeout（或interactive_timeout，取决于client_interactive标志）的客户端，MySQL会主动断开连接。

 

即使连接没有处于sleep状态，即客户端忙于计算或者存储数据，MySQL也选择了有条件的等待。在数据包的分发过程中，客户端可能来不及响应（发送、接收、或者处理数据包太慢）。为了保证连接不被浪费在无尽的等待中，MySQL也会选择有条件（net_read_timeout和net_write_timeout）地主动断开连接。