MySQL存盘阻塞“Lost connection to MySQL server during query”

　　公测以来的两次全服维护，都遇到了个别服务器停服时间异常长的情况，具体表现为停服流程阻塞在MySQL存盘，待存储数据一直不减少，持续大概一个小时到两个小时之后继续存储，成功停服，并SQLError报错“Lost connection to MySQL server during query”。

　　这两天同事查询一些资料大致弄清了问题原因，我们服务器停服瞬间会像MySQL发送大量数据进行存储，数据量过大触发了MySQL的net_read_timeout超时，MySQL报错断开连接，而服务器这边无法直接恢复，只能等待网络连接超时断开之后重连成功了继续存储，最终存储完成停服。


　　两个修改方案，一个是按照官网建议增大net_read_timeout超时时间，另一个是缩短odbc.ini的Readtimeout和Writetimeout超时时间。前者的意义在于阀值提高不要触发MySQL的报错断开连接，后者的意义在于报错断开连接之后能尽快恢复重连继续存储。前者需要重启MySQL，维护时存在风险，我们决定先采用第二种方案。

以下为同事记录的总结文档：

一 问题描述

11月4日服务器停服维护，2023服务器存盘阻塞。

11月11日服务器停服维护，2028，2096，10018服务器存盘阻塞。

具体数据如下：

服务器id	报错时间	报错时DBServiceExec慢心跳最长时间
2028	2014-11-11_08:28:01	6013009ms (100min)
2096	2014-11-11_07:06:09	6000006ms(100min)
10018	2014-11-11_08:42:32	6601007ms(110min)
2023	2014-11-04_05:59:47	2950005ms(49min)

 

以上服务器异常表现类似，DBserviceExec慢心跳时间超长，出现下面错误日志后，存储恢复正常，服务器正常停服。

 

[unixODBC][MySQL][ODBC 3.51 Driver][mysqld-5.5.20-rel24.1-log]Lost connection to MySQL server during query

 

根据官网资料，这个问题一般出现在进行存储大量数据时，停服存盘符合这个情况。

详细内容见标红部分。

B.5.2.3 Lost connection to MySQL server

There are three likely causes for this error message.

Usually it indicates network connectivity trouble and you should check the condition of your network if this error occurs frequently. If the error message includes “during query,” this is probably the case you are experiencing.

Sometimes the “during query” form happens when millions of rows are being sent as part of one or more queries. If you know that this is happening, you should try increasing net_read_timeout from its default of 30 seconds to 60 seconds or longer, sufficient for the data transfer to complete.

More rarely, it can happen when the client is attempting the initial connection to the server. In this case, if yourconnect_timeout value is set to only a few seconds, you may be able to resolve the problem by increasing it to ten seconds, perhaps more if you have a very long distance or slow connection. You can determine whether you are experiencing this more uncommon cause by using SHOW GLOBAL STATUS LIKE ‘Aborted_connects‘. It will increase by one for each initial connection attempt that the server aborts. You may see “reading authorization packet” as part of the error message; if so, that also suggests that this is the solution that you need.

If the cause is none of those just described, you may be experiencing a problem with BLOB values that are larger than max_allowed_packet, which can cause this error with some clients. Sometime you may see anER_NET_PACKET_TOO_LARGE error, and that confirms that you need to increase max_allowed_packet.

 

二 解决方法

1 官网给出修改net_read_timeout 配置项。此修改需要重启Mysql。

2 错误日志是Mysql的Client端在Server端无响应超时后报出，可以通过修改Client端的超时配置项，减少响应等待时间。此修改需要修改odbc.ini配置文件。

    Readtimeout=N，N单位为秒，实际等待时间为3*N 秒。

　　Writetimeout=N，N单位为秒，实际等待时间为net_retry_count* N秒，Mysql默认配置net_retry_count为10，TCP/IP默认超时时间为10min，所以，可以看出最大超时时间为100min。和停服异常阻塞时间基本吻合。

根据11月10日2000服日志，总结向数据库写入和读取数据时的时间，建议配置项数值如下：

Readtimeout = 300，单位为秒，实际等待时间为900 秒,因每次连接处理为毫秒级，所以主要作用是减少等待超时时间。

Writetimeout = 180，单位为秒，实际等待时间为1800秒，因每次连接处理为毫秒级，所以主要作用是减少等待超时时间。

  两个配置项具体信息见下表：

readtimeout

The timeout in seconds for attempts to read from the server. Each attempt uses this timeout value and there are retries if necessary,  so the total effective timeout value is three times the option value. You can set the value so that a lost connection can be detected  earlier than the TCP/IP Close_Wait_Timeout value of 10 minutes. This option works only for TCP/IP connections, and only for Windows prior  to MySQL 5.1.12. Corresponds to the MYSQL_OPT_READ_TIMEOUToption of the MySQL Client Library. Added in 3.51.27.

writetimeout

The timeout in seconds for attempts to write to the server. Each attempt uses this timeout value and there are net_retry_countretries  if necessary, so the total effective timeout value isnet_retry_count times the option value. This option works only for TCP/IP  connections, and only for Windows prior to MySQL 5.1.12. Corresponds to the MYSQL_OPT_WRITE_TIMEOUT option of the MySQL Client Library.  Added in 3.51.27.

 

修改readtimeout 和 writetimeout主要是配置socket读写超时，配置项原理如下：

+void vio_timeout(Vio *vio, timeout_type which, uint milliseconds)

 {

 #ifdef __WIN__

-  ulong wait_timeout= (ulong) timeout * 1000;

-  (void) setsockopt(vio->sd, SOL_SOCKET, 

- which ? SO_SNDTIMEO : SO_RCVTIMEO, (char*) &wait_timeout,

-        sizeof(wait_timeout));

+  ulong wait_timeout;

+#else

+  struct timeval tv;

+#endif /* __WIN__ */

+  int optname;

+  switch (which)

+  {

+    case VIO_READ_TIMEOUT:

+      optname = SO_RCVTIMEO;

+      break;

+    case VIO_WRITE_TIMEOUT:

+      optname = SO_SNDTIMEO;

+      break;

+    default:

+      return;

+  }

+#ifdef __WIN__

+  wait_timeout= (ulong) milliseconds;

+  setsockopt(vio->sd, SOL_SOCKET, optname, (char*) &wait_timeout,

+      sizeof(wait_timeout));

+#else

+  tv.tv_sec = milliseconds / 1000;

+  tv.tv_usec = milliseconds % 1000 * 1000;

+  setsockopt(vio->sd, SOL_SOCKET, optname, &tv, sizeof(tv));

 #endif /* __WIN__ */

 }