MySQL server has gone away

MySQL 服务器已经消失

本节还介绍了相关的Lost connection to server during query错误。

该MySQL server has gone away错误最常见的原因是服务器超时并关闭了连接。在这种情况下，您通常会收到以下错误代码之一（您收到的错误代码取决于操作系统）。

错误代码	描述
CR_SERVER_GONE_ERROR	客户端无法向服务器发送问题。
CR_SERVER_LOST	客户端在写入服务器时没有收到错误，但它没有得到问题的完整答案（或任何答案）。

默认情况下，如果没有发生任何事情，服务器会在八小时后关闭连接。wait_timeout 您可以通过在启动mysqld时设置变量来更改时间限制。请参阅 第 5.1.8 节，“服务器系统变量”。

如果您有脚本，您只需再次发出查询，客户端即可自动重新连接。这假设您在客户端启用了自动重新连接（这是mysql 命令行客户端的默认设置）。

MySQL server has gone away该错误的 其他一些常见原因是：

• 您（或数据库管理员）已使用KILL 语句或mysqladmin kill命令杀死了正在运行的线程。

• 您尝试在关闭与服务器的连接后运行查询。这表明应用程序中存在需要纠正的逻辑错误。

• 在不同主机上运行的客户端应用程序没有从该主机连接到 MySQL 服务器的必要权限。

• 您从客户端的 TCP/IP 连接超时。如果您一直在使用以下命令，则可能会发生这种情况： mysql_options(..., MYSQL_OPT_READ_TIMEOUT,...)或 mysql_options(..., MYSQL_OPT_WRITE_TIMEOUT,...). 在这种情况下，增加超时可能有助于解决问题。

• 您在服务器端遇到了超时，并且客户端中的自动重新连接被禁用（结构中的 reconnect标志 MYSQL等于 0）。

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

  Windows 上的问题是，在某些情况下，MySQL 在写入与服务器的 TCP/IP 连接时不会从操作系统收到错误，而是在尝试从连接中读取答案时收到错误。

  对此的解决方案是， mysql_ping()如果自上次查询以来已经有很长时间（这是连接器/ODBC 所做的），或者 wait_timeout在 mysqld服务器上设置得如此之高，以至于实际上它永远不会超时，则要么在连接上进行操作。

• 如果您向服务器发送不正确或太大的查询，您也可能会收到这些错误。如果 mysqld收到一个太大或乱序的数据包，它会假定客户端出现问题并关闭连接。如果您需要大查询（例如，如果您使用大 BLOB列），您可以通过设置服务器的变量来增加查询限制，该 max_allowed_packet 变量的默认值为 64MB。您可能还需要增加客户端的最大数据包大小。有关设置数据包大小的更多信息，请参见第 B.3.2.8 节“数据包太大”.

  插入大量行 的INSERTor 语句也可能导致这类错误。REPLACE无论要插入的行数如何，这些语句中的任何一个都向服务器发送单个请求；因此，您通常可以通过减少每 INSERT或 发送的行数来避免错误REPLACE。

• 如果主机名查找失败（例如，如果您的服务器或网络所依赖的 DNS 服务器出现故障），也可能会看到此错误。这是因为 MySQL 依赖于主机系统进行名称解析，但无法知道它是否在工作——从 MySQL 的角度来看，这个问题与任何其他网络超时没有区别。

  MySQL server has gone away如果 MySQL 在 skip_networking启用系统变量 的情况下启动， 您也可能会看到错误。

  如果 MySQL 端口（默认 3306）被防火墙阻止，则会出现另一个可能导致此错误的网络问题，从而完全阻止与 MySQL 服务器的任何连接。

• 您也可能在派生子进程的应用程序中遇到此错误，所有这些子进程都尝试使用与 MySQL 服务器的相同连接。这可以通过为每个子进程使用单独的连接来避免。

• 您遇到了服务器在执行查询时死机的错误。

您可以通过执行mysqladmin version并检查服务器的正常运行时间 来检查 MySQL 服务器是否死亡并重新启动。如果由于mysqld崩溃并重新启动而导致客户端连接中断，您应该集中精力查找崩溃的原因。首先检查再次发出查询是否会再次杀死服务器。请参阅第 B.3.3.3 节，“如果 MySQL 不断崩溃怎么办”。

您可以通过启动mysqld并将 系统变量设置为 3 来获取有关丢失连接的更多信息。这会在文件log_error_verbosity中记录一些断开连接消息。hostname.err请参阅 第 5.4.2 节，“错误日志”。

如果要创建有关此问题的错误报告，请确保包含以下信息：

• 指示 MySQL 服务器是否死亡。您可以在服务器错误日志中找到有关此的信息。请参阅 第 B.3.3.3 节，“如果 MySQL 不断崩溃怎么办”。

• 如果一个特定的查询杀死了mysqld并且在你运行查询之前检查了所涉及的表 CHECK TABLE，你能提供一个可重现的测试用例吗？请参阅 第 5.9 节，“调试 MySQL”。

• wait_timeoutMySQL服务器 中系统变量的值是 多少？（mysqladmin variables为您提供此变量的值。）

• 您是否尝试在启用通用查询日志的情况下运行mysqld以确定问题查询是否出现在日志中？（参见 第 5.4.3 节，“一般查询日志”。）

另请参见第 B.3.2.9 节，“通信错误和中止的连接”和 第 1.6 节，“如何报告错误或问题”。

This section also covers the related Lost connection to server during query error.

The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection. In this case, you normally get one of the following error codes (which one you get is operating system-dependent).

Error Code	Description
CR_SERVER_GONE_ERROR	The client couldn't send a question to the server.
CR_SERVER_LOST	The client didn't get an error when writing to the server, but it didn't get a full answer (or any answer) to the question.

By default, the server closes the connection after eight hours if nothing has happened. You can change the time limit by setting the wait_timeout variable when you start mysqld. See Section 5.1.8, “Server System Variables”.

If you have a script, you just have to issue the query again for the client to do an automatic reconnection. This assumes that you have automatic reconnection in the client enabled (which is the default for the mysql command-line client).

Some other common reasons for the MySQL server has gone away error are:

• You (or the db administrator) has killed the running thread with a KILL statement or a mysqladmin kill command.

• You tried to run a query after closing the connection to the server. This indicates a logic error in the application that should be corrected.

• A client application running on a different host does not have the necessary privileges to connect to the MySQL server from that host.

• You got a timeout from the TCP/IP connection on the client side. This may happen if you have been using the commands: mysql_options(..., MYSQL_OPT_READ_TIMEOUT,...) or mysql_options(..., MYSQL_OPT_WRITE_TIMEOUT,...). In this case increasing the timeout may help solve the problem.

• You have encountered a timeout on the server side and the automatic reconnection in the client is disabled (the reconnect flag in the MYSQL structure is equal to 0).

• You are using a Windows client and the server had dropped the connection (probably because wait_timeout expired) before the command was issued.

  The problem on Windows is that in some cases MySQL does not get an error from the OS when writing to the TCP/IP connection to the server, but instead gets the error when trying to read the answer from the connection.

  The solution to this is to either do a mysql_ping() on the connection if there has been a long time since the last query (this is what Connector/ODBC does) or set wait_timeout on the mysqld server so high that it in practice never times out.

• You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 64MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section B.3.2.8, “Packet Too Large”.

  An INSERT or REPLACE statement that inserts a great many rows can also cause these sorts of errors. Either one of these statements sends a single request to the server irrespective of the number of rows to be inserted; thus, you can often avoid the error by reducing the number of rows sent per INSERT or REPLACE.

• It is also possible to see this error if host name lookups fail (for example, if the DNS server on which your server or network relies goes down). This is because MySQL is dependent on the host system for name resolution, but has no way of knowing whether it is working—from MySQL's point of view the problem is indistinguishable from any other network timeout.

  You may also see the MySQL server has gone away error if MySQL is started with the skip_networking system variable enabled.

  Another networking issue that can cause this error occurs if the MySQL port (default 3306) is blocked by your firewall, thus preventing any connections at all to the MySQL server.

• You can also encounter this error with applications that fork child processes, all of which try to use the same connection to the MySQL server. This can be avoided by using a separate connection for each child process.

• You have encountered a bug where the server died while executing the query.

You can check whether the MySQL server died and restarted by executing mysqladmin version and examining the server's uptime. If the client connection was broken because mysqld crashed and restarted, you should concentrate on finding the reason for the crash. Start by checking whether issuing the query again kills the server again. See Section B.3.3.3, “What to Do If MySQL Keeps Crashing”.

You can obtain more information about lost connections by starting mysqld with the log_error_verbosity system variable set to 3. This logs some of the disconnection messages in the hostname.err file. See Section 5.4.2, “The Error Log”.

If you want to create a bug report regarding this problem, be sure that you include the following information:

• Indicate whether the MySQL server died. You can find information about this in the server error log. See Section B.3.3.3, “What to Do If MySQL Keeps Crashing”.

• If a specific query kills mysqld and the tables involved were checked with CHECK TABLE before you ran the query, can you provide a reproducible test case? See Section 5.9, “Debugging MySQL”.

• What is the value of the wait_timeout system variable in the MySQL server? (mysqladmin variables gives you the value of this variable.)

• Have you tried to run mysqld with the general query log enabled to determine whether the problem query appears in the log? (See Section 5.4.3, “The General Query Log”.)

See also Section B.3.2.9, “Communication Errors and Aborted Connections”, and Section 1.6, “How to Report Bugs or Problems”.