错误2006（HY000）：MySQL服务器已消失

本文翻译自：ERROR 2006 (HY000): MySQL server has gone away

I get this error when I try to source a large SQL file (a big INSERT query). 当我尝试提供大型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. 我尝试删除和取消删除表/数据库，以及重新启动MySQL。 None of these things resolve the problem. 这些都不是解决问题的方法。

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

---

#1楼

参考：https://stackoom.com/question/HX0m/错误-HY-MySQL服务器已消失

---

#2楼

如何像这样使用mysql客户端：

mysql -h <hostname> -u username -p <databasename> < file.sql

---

#3楼

A couple things could be happening here; 这里可能会发生几件事；

• Your INSERT is running long, and client is disconnecting. 您的INSERT运行了很长时间，并且客户端正在断开连接。 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; 这里的一种选择是从命令行运行批处理文件，然后在参数中选择数据库，如下所示：

$ mysql db_name < source.sql $ mysql db_name <source.sql

• Another is to run your command via php or some other language. 另一个是通过php或其他语言运行命令。 After each long - running statement, you can close and re-open the connection, ensuring that you're connected at the start of each query. 在每个长期运行的语句之后，您可以关闭并重新打开连接，以确保在每个查询开始时都已连接。

---

#4楼

If it's reconnecting and getting connection ID 2, the server has almost definitely just crashed. 如果重新连接并获得连接ID 2，则服务器几乎肯定崩溃了。

Contact the server admin and get them to diagnose the problem. 请与服务器管理员联系，并让他们诊断问题。 No non-malicious SQL should crash the server, and the output of mysqldump certainly should not. 没有任何非恶意的SQL应该不会使服务器崩溃，并且mysqldump的输出当然也不会。

It is probably the case that the server admin has made some big operational error such as assigning buffer sizes of greater than the architecture's address-space limits, or more than virtual memory capacity. 服务器管理员可能会犯一些大的操作错误，例如分配的缓冲区大小大于体系结构的地址空间限制，或者大于虚拟内存容量。 The MySQL error-log will probably have some relevant information; MySQL错误日志可能会包含一些相关信息。 they will be monitoring this if they are competent anyway. 无论如何，他们将对此进行监视。

---

#5楼

max_allowed_packet=64M

Adding this line into my.cnf file 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" 在Windows上，此文件位于：“ C：\\ ProgramData \\ MySQL \\ MySQL Server 5.6”

On Linux (Ubuntu): /etc/mysql 在Linux（Ubuntu）上：/ etc / mysql

---

#6楼

I encountered this error when I use Mysql Cluster, I do not know this question is from a cluster usage or not. 我在使用Mysql群集时遇到此错误，我不知道此问题是否来自群集使用情况。 As the error is exactly the same, so give my solution here. 由于错误完全相同，因此请在此处提供解决方案。 Getting this error because the data nodes suddenly crash. 由于数据节点突然崩溃而出现此错误。 But when the nodes crash, you can still get the correct result using cmd: 但是，当节点崩溃时，您仍然可以使用cmd获得正确的结果：

ndb_mgm -e 'ALL REPORT MEMORYUSAGE'

And the mysqld also works correctly.So at first, I can not understand what is wrong. 而且mysqld也能正常工作。因此，起初，我不明白出什么问题了。 And about 5 mins later, ndb_mgm result shows no data node working. 大约5分钟后，ndb_mgm结果显示没有数据节点正常工作。 Then I realize the problem. 然后我意识到了问题。 So, try to restart all the data nodes, then the mysql server is back and everything is OK. 因此，尝试重新启动所有数据节点，然后mysql服务器返回，一切正常。

But one thing is weird to me, after I lost mysql server for some queries, when I use cmd like show tables , I can still get the return info like 33 rows in set (5.57 sec) , but no table info is displayed. 但是对我来说很奇怪，当我丢失了mysql服务器的某些查询后，当我使用cmd这样的show tables ，我仍然可以得到返回信息，例如33 rows in set (5.57 sec) ，但是没有显示任何表信息。