ERROR 2006 (HY000): MySQL 服务器已经消失

ERROR 2006 (HY000): MySQL server has gone away

提问by bgcode

I get this error when I try to source a large SQL file (a big INSERTquery).

当我尝试获取大型 SQL 文件（大型INSERT查询）时出现此错误。

mysql>  source file.sql
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: *** NONE ***

Nothing in the table is updated. I've tried deleting and undeleting the table/database, as well as restarting MySQL. None of these things resolve the problem.

表中的任何内容都没有更新。我试过删除和取消删除表/数据库，以及重新启动 MySQL。这些东西都不能解决问题。

Here is my max-packet size:

这是我的最大数据包大小：

+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+

Here is the file size:

这是文件大小：

$ ls -s file.sql 
79512 file.sql

When I try the other method...

当我尝试另一种方法时...

$ ./mysql -u root -p my_db < file.sql
Enter password: 
ERROR 2006 (HY000) at line 1: MySQL server has gone away

回答by Kurt Zhong

max_allowed_packet=64M

Adding this line into my.cnffile solves my problem.

将此行添加到my.cnf文件中解决了我的问题。

This is useful when the columns have large values, which cause the issues, you can find the explanation here.

这在列具有大值时很有用，这会导致问题，您可以在此处找到解释。

On Windows this file is located at: "C:\ProgramData\MySQL\MySQL Server 5.6"

On Linux (Ubuntu): /etc/mysql

在 Windows 上，此文件位于：“C:\ProgramData\MySQL\MySQL Server 5.6”

在 Linux (Ubuntu) 上：/etc/mysql

回答by Nanhe Kumar

You can increase Max Allowed Packet

您可以增加最大允许数据包

SET GLOBAL max_allowed_packet=1073741824;

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_allowed_packet

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_allowed_pa​​cket

回答by Mario Peshev

The global update and the my.cnf settings didn't work for me for some reason. Passing the max_allowed_packetvalue directly to the client worked here:

出于某种原因，全局更新和 my.cnf 设置对我不起作用。将max_allowed_packet值直接传递给客户端在这里工作：

mysql -h <hostname> -u username -p --max_allowed_packet=1073741824 <databasename> < db.sql

回答by kenorb

In general the error:

一般来说，错误：

Error: 2006 (CR_SERVER_GONE_ERROR) - MySQL server has gone away

错误：2006 ( CR_SERVER_GONE_ERROR) - MySQL 服务器已消失

means that the client couldn't send a question to the server.

意味着客户端无法向服务器发送问题。

---

---

mysqlimport

mysql进口

In your specific case while importing the database file via mysql, this most likely mean that some of the queries in the SQL file are too large to import and they couldn't be executed on the server, therefore client fails on the first occurred error.

在您通过 导入数据库文件时的特定情况下mysql，这很可能意味着 SQL 文件中的某些查询太大而无法导入并且它们无法在服务器上执行，因此客户端在第一次出现错误时失败。

So you've the following possibilities:

所以你有以下可能性：

• Add force option (-f) for mysqlto proceed and execute rest of the queries.

  This is useful if the database has some large queries related to cache which aren't relevant anyway.

• Increase max_allowed_packetand wait_timeoutin your server config (e.g. ~/.my.cnf).

• Dump the database using --skip-extended-insertoption to break down the large queries. Then import it again.

• Try applying --max-allowed-packetoption for mysql.

• 添加强制选项 ( -f)mysql以继续并执行其余查询。

  如果数据库有一些与缓存相关但无论如何都不相关的大型查询，这将非常有用。

• max_allowed_packetwait_timeout在您的服务器配置中增加和（例如~/.my.cnf）。

• 使用--skip-extended-insert选项转储数据库以分解大型查询。然后再次导入。

• 尝试为 应用--max-allowed-packet选项mysql。

---

---

Common reasons

常见原因

In general this error could mean several things, such as:

通常，此错误可能意味着多种含义，例如：

• a query to the server is incorrect or too large,

  Solution: Increase max_allowed_packetvariable.

  • Make sure the variable is under [mysqld]section, not [mysql].

  • Don't afraid to use large numbers for testing (like 1G).

  • Don't forget to restart the MySQL/MariaDB server.

  • Double check the value was set properly by:

    mysql -sve "SELECT @@max_allowed_packet" # or:
    mysql -sve "SHOW VARIABLES LIKE 'max_allowed_packet'"
    

• You got a timeout from the TCP/IP connection on the client side.

  Solution: Increase wait_timeoutvariable.

• You tried to run a query after the connection to the server has been closed.

  Solution: A logic error in the application should be corrected.

• Host name lookups failed (e.g. DNS server issue), or server has been started with --skip-networkingoption.

  Another possibility is that your firewall blocks the MySQL port (e.g. 3306 by default).

• The running thread has been killed, so retry again.

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

• A client running on a different host does not have the necessary privileges to connect.

• And many more, so learn more at: B.5.2.9 MySQL server has gone away.

• 对服务器的查询不正确或太大，

  解决方法：增加max_allowed_packet变量。

  • 确保变量在[mysqld]section下，而不是[mysql].

  • 不要害怕使用大数字进行测试（如1G）。

  • 不要忘记重新启动 MySQL/MariaDB 服务器。

  • 仔细检查值是否正确设置：

    mysql -sve "SELECT @@max_allowed_packet" # or:
    mysql -sve "SHOW VARIABLES LIKE 'max_allowed_packet'"
    

• 客户端的 TCP/IP 连接超时。

  解决方法：增加wait_timeout变量。

• 您试图在与服务器的连接关闭后运行查询。

  解决方案：应更正应用程序中的逻辑错误。

• 主机名查找失败（例如 DNS 服务器问题），或服务器已使用--skip-networking选项启动。

  另一种可能性是您的防火墙阻止了 MySQL 端口（例如，默认情况下为 3306）。

• 正在运行的线程已被杀死，因此请重试。

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

• 在不同主机上运行的客户端没有必要的连接权限。

• 还有更多内容，请访问：B.5.2.9 MySQL 服务器已消失。

---

---

Debugging

调试

Here are few expert-level debug ideas:

以下是一些专家级的调试思路：

• Check the logs, e.g.

  sudo tail -f $(mysql -Nse "SELECT @@GLOBAL.log_error")
  

• Test your connection via mysql, telnetor ping functions (e.g. mysql_pingin PHP).

• Use tcpdumpto sniff the MySQL communication (won't work for socket connection), e.g.:

  sudo tcpdump -i lo0 -s 1500 -nl -w- port mysql | strings
  

• On Linux, use strace. On BSD/Mac use dtrace/dtruss, e.g.

  sudo dtruss -a -fn mysqld 2>&1
  

  See: Getting started with DTracing MySQL

• 检查日志，例如

  sudo tail -f $(mysql -Nse "SELECT @@GLOBAL.log_error")
  

• 通过mysql,telnet或 ping 函数（例如mysql_ping在 PHP 中）测试您的连接。

• 使用tcpdump嗅探MySQL的通信（会为套接字连接不工作），例如：

  sudo tcpdump -i lo0 -s 1500 -nl -w- port mysql | strings
  

• 在 Linux 上，使用strace. 在 BSD/Mac 上使用dtrace/ dtruss，例如

  sudo dtruss -a -fn mysqld 2>&1
  

  请参阅：DTracing MySQL 入门

Learn more how to debug MySQL server or client at: 26.5 Debugging and Porting MySQL.

了解更多如何调试 MySQL 服务器或客户端：26.5 调试和移植 MySQL。

For reference, check the source code in sql-common/client.cfile responsible for throwing the CR_SERVER_GONE_ERRORerror for the client command.

作为参考，请检查sql-common/client.c负责为CR_SERVER_GONE_ERROR客户端命令抛出错误的文件中的源代码。

MYSQL_TRACE(SEND_COMMAND, mysql, (command, header_length, arg_length, header, arg));
if (net_write_command(net,(uchar) command, header, header_length,
          arg, arg_length))
{
  set_mysql_error(mysql, CR_SERVER_GONE_ERROR, unknown_sqlstate);
  goto end;
}

回答by Luke Schoen

I solved the error ERROR 2006 (HY000) at line 97: MySQL server has gone awayand successfully migrated a >5GB sql file by performing these two steps in order:

ERROR 2006 (HY000) at line 97: MySQL server has gone away通过按顺序执行以下两个步骤，我解决了错误并成功迁移了 >5GB 的 sql 文件：

1. Created /etc/my.cnf as others have recommended, with the following contents:

   [mysql]
   connect_timeout = 43200
   max_allowed_packet = 2048M
   net_buffer_length = 512M
   debug-info = TRUE
   

2. Appending the flags --force --wait --reconnectto the command (i.e. mysql -u root -p -h localhost my_db < file.sql --verbose --force --wait --reconnect).

1. 按照其他人的建议创建/etc/my.cnf，内容如下：

   [mysql]
   connect_timeout = 43200
   max_allowed_packet = 2048M
   net_buffer_length = 512M
   debug-info = TRUE
   

2. 将标志附加--force --wait --reconnect到命令（即mysql -u root -p -h localhost my_db < file.sql --verbose --force --wait --reconnect）。

Important Note: It was necessary to perform both steps, because if I didn't bother making the changes to /etc/my.cnf file as well as appending those flags, some of the tables were missing after the import.

重要说明：有必要执行这两个步骤，因为如果我不费心对 /etc/my.cnf 文件进行更改以及附加这些标志，导入后某些表就会丢失。

System used: OSX El Capitan 10.11.5; mysql Ver 14.14 Distrib 5.5.51 for osx10.8 (i386)

使用的系统：OSX El Capitan 10.11.5；mysql Ver 14.14 Distrib 5.5.51 for osx10.8 (i386)

回答by lesolorzanov

Just in case, to check variables you can use

以防万一，要检查您可以使用的变量

$> mysqladmin variables -u user -p 

This will display the current variables, in this case max_allowed_packet, and as someone said in another answer you can set it temporarily with

这将显示当前变量，在本例中为 max_allowed_pa​​cket，正如有人在另一个答案中所说，您可以使用

mysql> SET GLOBAL max_allowed_packet=1072731894

In my case the cnf file was not taken into account and I don't know why, so the SET GLOBAL code really helped.

在我的情况下，没有考虑 cnf 文件，我不知道为什么，所以 SET GLOBAL 代码确实有帮助。

回答by Sathish D

I had the same problem but changeing max_allowed_packet in the my.ini/my.cnf file under [mysqld] made the trick.

我遇到了同样的问题，但是在 [mysqld] 下的 my.ini/my.cnf 文件中更改 max_allowed_pa​​cket 成功了。

add a line

添加一行

max_allowed_packet=500M

now restart the MySQL service once you are done.

现在完成后重新启动 MySQL 服务。

回答by razzed

You can also log into the database as root (or SUPER privilege) and do

您还可以以 root（或超级权限）身份登录数据库并执行

set global max_allowed_packet=64*1024*1024;

doesn't require a MySQL restart as well. Note that you should fix your my.cnffile as outlined in other solutions:

也不需要重启 MySQL。请注意，您应该my.cnf按照其他解决方案中的说明修复您的文件：

[mysqld]
max_allowed_packet=64M

And confirm the change after you've restarted MySQL:

并在重新启动 MySQL 后确认更改：

show variables like 'max_allowed_packet';

You can use the command-line as well, but that may require updating the start/stop scripts which may not survive system updates and patches.

您也可以使用命令行，但这可能需要更新启动/停止脚本，这些脚本可能无法在系统更新和修补程序中幸存。

As requested, I'm adding my own answer here.Glad to see it works!

根据要求，我在这里添加我自己的答案。很高兴看到它有效！

回答by RGA

The solution is increasing the values given the wait_timeoutand the connect_timeoutparameters in your options file, under the [mysqld]tag.

解决方案是在标签下增加选项文件中给定的值wait_timeout和connect_timeout参数[mysqld]。

I had to recover a 400MB mysql backup and this worked for me (the values I've used below are a bit exaggerated, but you get the point):

我不得不恢复一个 400MB 的 mysql 备份，这对我有用（我在下面使用的值有点夸张，但你明白了）：

[mysqld]
port=3306
explicit_defaults_for_timestamp = TRUE
connect_timeout = 1000000
net_write_timeout = 1000000
wait_timeout = 1000000
max_allowed_packet = 1024M
interactive_timeout = 1000000
net_buffer_length = 200M
net_read_timeout = 1000000
set GLOBAL delayed_insert_timeout=100000

Blockquote

块引用

回答by Chris Henry

A couple things could be happening here;

这里可能会发生一些事情；

• Your INSERTis running long, and client is disconnecting. When it reconnects it's not selecting a database, hence the error. One option here is to run your batch file from the command line, and select the database in the arguments, like so;

• 您INSERT的运行时间很长，客户端正在断开连接。当它重新连接时，它没有选择数据库，因此出现错误。这里的一种选择是从命令行运行批处理文件，然后在参数中选择数据库，如下所示；

$ mysql db_name < source.sql

$ mysql db_name < 源.sql

• Another is to run your command via phpor some other language. After each long - running statement, you can close and re-open the connection, ensuring that you're connected at the start of each query.

• 另一种是通过php或其他语言运行您的命令。在每个长时间运行的语句之后，您可以关闭并重新打开连接，确保在每个查询开始时都已连接。