获取 MYSQL 错误：“错误代码：2006 - MySQL 服务器已消失”

Getting MYSQL Error: "Error Code: 2006 - MySQL server has gone away"

提问by meetpd

I am getting following error, when I try to import MYSQL database:

当我尝试导入 MYSQL 数据库时出现以下错误：

Error Code: 2013 - Lost connection to MySQL server during queryQuery:
Error Code: 2006 - MySQL server has gone away

Can someone let me know what is wrong?

有人可以让我知道出了什么问题吗？

采纳答案by Tudor Constantin

Hereyou can read more about this error and various ways to avoid/solve it

在这里您可以阅读有关此错误的更多信息以及避免/解决它的各种方法

From the docs:

从文档：

The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection

MySQL服务器消失错误最常见的原因是服务器超时并关闭了连接

回答by Rafe Hatfield

Investigation shows many solutions correctly talking about setting the max_allowed_packet and wait_timeout for mysql in my.cnf; small addendum that the default install of mysql on mac osx doesn't appear to include this file. You may first need to create it at /etc/my.cnf (this is only an issue if you're using the default install of mysql instead of a mamp stack or similar)

调查显示许多解决方案正确谈论在 my.cnf 中为 mysql 设置 max_allowed_pa​​cket 和 wait_timeout；mac osx 上 mysql 的默认安装似乎不包含此文件的小附录。您可能首先需要在 /etc/my.cnf 中创建它（如果您使用默认安装的 mysql 而不是 mamp 堆栈或类似的，这只是一个问题）

contents of /etc/my.cnf that corrected this issue for me below:

/etc/my.cnf 的内容在下面为我纠正了这个问题：

[mysqld]
max_allowed_packet= 64M
wait_timeout= 6000

回答by CloudyMarble

Try following 2006 Error related fixes :

尝试遵循 2006 错误相关修复：

• Server timed out and closed the connection. How to fix: check that wait_timeout variable in your mysqld's my.cnf configuration file is large enough.

• Server dropped an incorrect or too large packet. If mysqld gets a packet that is too large or incorrect, it assumes that something has gone wrong with the client and closes the connection. You can increase the maximal packet size limit by increasing the value of max_allowed_packet in my.cnf file.

• 服务器超时并关闭连接。如何修复：检查 mysqld 的 my.cnf 配置文件中的 wait_timeout 变量是否足够大。

• 服务器丢弃了不正确或过大的数据包。如果 mysqld 收到一个太大或不正确的数据包，它会假定客户端出现问题并关闭连接。您可以通过增加 my.cnf 文件中 max_allowed_pa​​cket 的值来增加最大数据包大小限制。

回答by Bimal Poudel

It often happens, if your INSERT query is a too big single line statement with several rows.

如果您的 INSERT 查询是一个包含多行的过大的单行语句，则经常会发生这种情况。

回答by andrew

Try to restart mysql server. It`s possible that server is not working correctly but sql notifier displaying that is running, as well.

尝试重新启动 mysql 服务器。服务器可能无法正常工作，但 sql 通知程序也显示正在运行。

回答by Rafael Sudbrack

I tried all the solutions and nothing worked
I use the workbench to remotely connect hostgator
I realized that mysql server hostgator was the 5.5 version and in my workbench is set up to version 5.6
when I set the workbench to 5.5 he started to work

我尝试了所有解决方案但没有任何效果
我使用工作台远程连接hostgator
我意识到mysql服务器hostgator是5.5版本并且在我将工作台设置
为5.5时我的工作台设置为5.6版本他开始工作

edit/preferences/mysql/default target version

编辑/首选项/mysql/默认目标版本

回答by Shaam

Show your mySql Variables

显示你的 mySql 变量

• show variables like 'max%'

• set global max_allowed_packet={PacketRANGE}; // like this 10485760;

• show global variables like 'max_all%';

• 显示像“max%”这样的变量

• 设置全局 max_allowed_pa​​cket={PacketRANGE}; // 像这样 10485760;

• 显示全局变量，如“max_all%”；

Enjoy Your Coding here

在这里享受您的编码

回答by dennisbot

there are many reason that this issue is happening to you, hereyou can find all of the possible reasons, personally I've been struggling with the packet size but I've updated my my.ini file but before to do that, I've checked the max_allowed_packet variable that had given me 1048576B = 1MB and I've updated to 5MB. show variables where variable_name like '%packet%'

这个问题发生在你身上的原因有很多，在这里你可以找到所有可能的原因，我个人一直在努力解决数据包大小问题，但我已经更新了 my.ini 文件，但在此之前，我我检查了给我 1048576B = 1MB 的 max_allowed_pa​​cket 变量，我已经更新到 5MB。显示变量 where variable_name like '%packet%'

select 1048576 / 1024 / 1024

选择 1048576 / 1024 / 1024

select 5242880 / 1024 / 1024

选择 5242880 / 1024 / 1024

回答by alcott80

In MySQL 5.7 this error can be generated by a too large communication packet:

在 MySQL 5.7 中，这个错误可能是由一个太大的通信包产生的：

When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues an ER_NET_PACKET_TOO_LARGE error and closes the connection. With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large.

当 MySQL 客户端或 mysqld 服务器收到大于 max_allowed_pa​​cket 字节的数据包时，它会发出 ER_NET_PACKET_TOO_LARGE 错误并关闭连接。对于某些客户端，如果通信包太大，您也可能在查询错误期间丢失与 MySQL 服务器的连接。

A Packet in MySQL is:

MySQL中的数据包是：

A communication packet is a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a master replication server to a slave.

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

You can found the doc here: DOC
You should try to set the max_allowed_packetto a bigger value (default value is 4MB) to solve if your SQL script is greater than this size. You can set this value within an Option File so you do not have to set it up each time.
On Microsoft Windows Vista and greater, you can set max_allowed_packetinto the file
%PROGRAMDATA%\MySQL\MySQL Server 5.7\my.ini
or
%PROGRAMDATA%\MySQL\MySQL Server 5.7\my.cnf

您可以在此处找到文档：DOC
您应该尝试将max_allowed_pa​​cket设置为更大的值（默认值为 4MB）以解决您的 SQL 脚本是否大于此大小。您可以在选项文件中设置此值，这样您就不必每次都设置它。
在 Microsoft Windows Vista 及更高版本上，您可以将max_allowed_pa​​cket设置到文件
%PROGRAMDATA%\MySQL\MySQL Server 5.7\my.ini
或
%PROGRAMDATA%\MySQL\MySQL Server 5.7\my.cnf 中

where PROGRAMDATA = C:\ProgramData
More info (also for other S.O.) HERE

其中 PROGRAMDATA = C:\ProgramData
更多信息（也适用于其他 SO）在这里

回答by Mukesh Chapagain

Here's an alternative to editing my.cnffile. You can set the MySQL global variables value via logging into the MySQL server.

这是编辑my.cnf文件的替代方法。您可以通过登录 MySQL 服务器来设置 MySQL 全局变量值。

You can check the list of all the MySQL Global Variables and their values with the following command:

您可以使用以下命令检查所有 MySQL 全局变量及其值的列表：

$> mysqladmin variables -u YourMysqlUsername -p

You can also check for these variables value by first logging into MySQL server:

您还可以通过首先登录 MySQL 服务器来检查这些变量值：

$> mysql -u YourMysqlUsername -p

mysql> SHOW VARIABLES;

To check specific variable value:

要检查特定的变量值：

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';

To solve MySQL Server Gone Awayerror, you need to increase the value of max_allowed_packetvariable.

要解决MySQL Server Gone Away错误，您需要增加max_allowed_packet变量的值。

mysql> SET GLOBAL max_allowed_packet=1072731894;
mysql> quit

Now, when you again login to MySQL and check for the max_allowed_packetvalue, you should see the updated value.

现在，当您再次登录 MySQL 并检查该max_allowed_packet值时，您应该会看到更新后的值。

$> mysql -u YourMysqlUsername -p

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1072731136 |
+--------------------+------------+
1 row in set (0.00 sec)