Lost connection to MySQL server&MySQL server has gone away

最近线上收集到如下两个错误信息，目前没有什么思路解决这两个问题，本文的目的就是先弄明白为什么会有这种情况。

  1. Lost connection to MySQL server
  2. MySQL server has gone away

Lost connection to MySQL server

造成这个错误可能有三个原因。

如果这个错误频繁产生，通常是网络有问题，你应该检查你的网络环境。如果错误信息包含“during query”，那么你可能经历的就是这种情况。

有时候，”during query"会在数百万行的一个或多个查询传送时发生。如果你知道这种情况正在发生，你应该尝试把net_read_timeout从默认的30秒调整到60秒甚至更多，来满足数据完全传输。

更少见的是，它发生在客户端尝试初始化连接的时候，在这种情况下，如果你的connect_timeout值只有几秒，你可以通过增加这个变量到10秒来解决这个问题，如果你的距离太长或者连接慢可能需要增加到更大的时间。你可以通过使用 SHOW GLOBAL STATUS LIKE 'Aborted_connects' 来判断是否碰到的是这种情况，这个数据会在每次服务中断初始化连接的时候增加。你可能会看到错误信息中有“reading authorization packet”，如果是这样的话，你也需要这个解决方案。

如果原因不是上面描述的情况，你可能遇到BLOB值大于max_allowed_packet的情况。它可能会在一些客户端上产生这个错误。有时候你可能看到ER_NET_PACKET_TOO_LARGE错误，这就是说你确实需要增加max_allowed_packet的值了。

上面既然提到了大包的问题，所以把这块也拿出来翻译一下。

Packet Too Large

通讯包是发送到MySQL服务的一条SQL语句，发送到客户端的一行数据，或者是二进制日志从主服务器到从服务器的一个事件。

在MySQL5.7，传输的最大的包是1GB。

当MySQL客户端或服务端接受到的包超过了max_allowed_packet的字节数，会产生一个ER_NET_PACKET_TOO_LARGE的错误并且断开连接。对于一些客户端，你可能也会得到“Lost connection to MySQL server during query”错误。

客户端和服务端有自己的max_allowed_packet变量，所以如果你想去处理大的数据包，就必须增大客户端和服务端的值。

如果你是用的是mysql客户端程序，它的max_allowed_packet值是16M，用下面的语句可以设置成32M：

shell> mysql --max_allowed_packet=32M

服务端的max_allowed_packet默认是4M（但是我的MySQL5.7配置文件默认是16M）。如果服务需要处理大数据包（例如，包含BLOB列），可以用下面的语句设置为16M：

shell> mysqld --max_allowed_packet=16M

当然也可以在配置文件中通过max_allowed_packet配置项进行设置，例如：

[mysqld]
max_allowed_packet=16M

增大这个变量是安全的，因为服务只有在需要的时候才会申请额外的内存。例如，mysqld仅在你面临一个大的查询或者必须返回一个大的结果集的时候才会申请更多的内存。这个变量默认比较小是一种预防措施，一来能捕获客户端和服务端中不正确的大的数据包，二来也是确保不会因为偶然使用的大的数据包导致内存不足。

如果你在处理BLOB值，但是mysqld没有访问足够内存的权限，你也可能得到数据包的奇怪的问题。如果你怀疑是这种情况，尝试添加 ulimit -d 256000 到mysqld_safe脚本的开始处，然后重启mysqld。

MySQL server has gone away

本节也覆盖了“Lost connection to server during query”错误。

“MySQL server has gone away”最通常的原因是服务超时并且关闭了连接。在这种情况下，根据系统的不同，你会得到下面的其中一个错误码：

Error_Code Description
CR_SERVER_GONE_ERROR 客户端不能发送请求到服务端
CR_SERVER_LOST 客户端在写入的时候没有错误，但是没有得到任何回应或者得到了不完整的回应

默认的，服务端会在连接没有做任何事情的情况下， 八小时 后关闭连接。你可以在启动mysqld的时候通过wait_timeout变量改变这个设置。

如果有脚本，则只需要从客户端重新发起查询来进行重连，前提是你启用了客户端的自动重连（这里指的是mysql命令行客户端，客户端语言有相应的api）。

对于这个错误，还有一些其他的原因：

  • 某人用KILL语句或mysqladmin kill命令杀掉了正在运行的进程，

  • 尝试在关闭到服务的连接之后运行查询。这表示应用逻辑有问题，应该进行纠正。

  • 客户端应用和MySQL服务运行在不同的主机上，并且客户端从所在的主机连接到MySQL服务缺少必要的权限。

  • 从客户端的TCP/IP连接超时。这可能发生在正在使用命令：mysql_options(..., MYSQL_OPT_READ_TIMEOUT,...) 或者 mysql_options(..., MYSQL_OPT_WRITE_TIMEOUT,...) ，在这种情况下，增加超时时间可能解决问题。

  • 遇到了服务端超时，并且自动重连被客户端禁用（重连标志在MySQL架构里面是0）。

  • 你正在使用Windows客户端，在发出命令之前服务已经断开连接（可能是wait_timeout过期）。

    Windows上的这个问题，在某些情况下，MySQL在通过TCP/IP连接向服务写数据的时候没有获取到错误，但是在尝试去读取应答的时候会得到错误。

    如果上次查询到现在有一段时间了，解决这个问题的办法是在连接上执行mysql_ping()（这就是Connector/ODBC所做的），或者设置wait_timeout大一些，这样在实践的时候就不会超时了。

  • 如果你发送的查询不正确或者太大，也可能得到这个错误。如果mysqld获取到的包太大或者out of order，服务端会认为这个客户端出问题了，并且关闭连接。如果你需要大的查询（例如，BLOB列），你可以通过设置服务端的max_allowed_packet变量来增大这个限制，这个值默认是4M。你也可以增加客户端的最大的包限制。具体可看上面一节。

    一个涉及很多数据的INSERT或REPLACE语句也会导致这种错误。无论插入的行数是多少，这里面的每一个语句都会向服务发送一个请求；因此，可以通过减少INSERT和REPLACE发送的行数来避免这个错误。

  • 如果主机名查找失败也可能看见这个错误（例如，在你的服务器上的DNS服务gone down）。这是因为MySQL依赖主机系统来做名称解析，但是没有办法来知道它是否在正常工作。从MySQL的角度看这个问题和其他的网络超时是无法区分的。

    如果你的MySQL以–skip-networking启动，你也可能得到MySQL server has gone away错误。

    如果你的防火墙阻止了MySQL的端口（默认是3306），因此阻止了任何到MySQL服务的连接。所以也可能导致这个问题。

  • 在应用中fork子进程也可能遇到这种错误，因为所有的进程都使用同一个连接来连接到MySQL服务。可以通过为每个子进程使用独立的连接来避免。

  • 你可能遇到MySQL在执行查询的时候挂掉的bug。

你可以通过运行mysqladmin version来检查MySQL服务正常运行的时间，从而来判断MySQL服务是否挂掉并且重启了，如下。如果客户端连接因为msyqld的崩溃和重启而断开，那么你应该集中精力来寻找崩溃的原因，首先你应该再次运行一下查询看是否会再次杀死服务。

shell:~$ mysqladmin version -h127.0.0.1 -uroot -p
Enter password:
mysqladmin  Ver 8.42 Distrib 5.7.21, for Linux on x86_64
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version		5.7.21-0ubuntu0.16.04.1
Protocol version	10
Connection		127.0.0.1 via TCP/IP
TCP port		3306
Uptime:			41 min 29 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 107  Flush tables: 1  Open tables: 26  Queries per second avg: 0.002

你可以在启动mysqld的时候设置log_error_verbosity为3来得到关于丢失连接更多的信息，这样会在hostname.err里面记录更多的信息。

（完）