MySQL 错误 2006：mysql 服务器已消失

MySQL error 2006: mysql server has gone away

提问by floatleft

I'm running a server at my office to process some files and report the results to a remote MySQL server.

我在我的办公室运行一个服务器来处理一些文件并将结果报告给远程 MySQL 服务器。

The files processing takes some time and the process dies halfway through with the following error:

文件处理需要一些时间，并且过程中途停止并出现以下错误：

2006, MySQL server has gone away

I've heard about the MySQL setting, wait_timeout, but do I need to change that on the server at my office or the remote MySQL server?

我听说过 MySQL 设置wait_timeout，但是我是否需要在我办公室的服务器或远程 MySQL 服务器上更改它？

采纳答案by Niet the Dark Absol

It may be easier to check if the connection and re-establish it if needed.

检查连接并在需要时重新建立连接可能更容易。

See PHP:mysqli_pingfor info on that.

有关这方面的信息，请参阅PHP:mysqli_ping。

回答by George

I've encountered this a number of times and I've normally found the answer to be a very low default setting of max_allowed_packet.

我遇到过很多次这种情况，我通常发现答案是max_allowed_packet.

Raising it in /etc/my.cnf(under [mysqld]) to 8 or 16M usually fixes it. (The default in MySql 5.7 is 4194304, which is 4MB.)

将它/etc/my.cnf（低于[mysqld]）提高到 8 或 16M 通常可以修复它。（MySql 5.7 中的默认4194304值为 4MB。）

[mysqld]
max_allowed_packet=16M

---

---

Note: Just create the line if it does not exist

注意：如果该行不存在，则创建该行

Note: This can be set on your server as it's running.

注意：这可以在您的服务器运行时设置。

Use set global max_allowed_packet=104857600. This sets it to 100MB.

使用set global max_allowed_packet=104857600. 这将其设置为 100MB。

回答by Sathish D

I had the same problem but changeing max_allowed_packetin the my.ini/my.cnffile under [mysqld]made the trick.

我遇到了同样的问题，但更改下max_allowed_packet的my.ini/my.cnf文件就[mysqld]成功了。

add a line

添加一行

max_allowed_packet=500M

max_allowed_pa​​cket=500M

now restart the MySQL serviceonce you are done.

现在restart the MySQL service一旦你完成了。

回答by Geshan Ravindu

I used following command in MySQL command-line to restore a MySQL database which size more than 7GB, and it works.

我在 MySQL 命令行中使用以下命令来恢复大小超过 7GB 的 MySQL 数据库，并且它可以工作。

set global max_allowed_packet=268435456;

回答by Yzmir Ramirez

Error: 2006 (CR_SERVER_GONE_ERROR)

错误：2006 ( CR_SERVER_GONE_ERROR)

Message: MySQL server has gone away

消息：MySQL 服务器已消失

Generally you can retry connecting and then doing the query again to solve this problem - try like 3-4 times before completely giving up.

通常，您可以重试连接，然后再次执行查询以解决此问题 - 在完全放弃之前尝试 3-4 次。

I'll assuming you are using PDO. If so then you would catch the PDO Exception, increment a counter and then try again if the counter is under a threshold.

我假设您正在使用 PDO。如果是这样，那么您将捕获 PDO 异常，增加一个计数器，然后在计数器低于阈值时重试。

If you have a query that is causing a timeout you can set this variable by executing:

如果您有一个导致超时的查询，您可以通过执行来设置这个变量：

SET @@GLOBAL.wait_timeout=300;
SET @@LOCAL.wait_timeout=300;  -- OR current session only

Where 300 is the number of seconds you think the maximum time the query could take.

其中 300 是您认为查询可能花费的最长时间的秒数。

Further information on how to deal with Mysql connection issues.

有关如何处理 Mysql 连接问题的更多信息。

EDIT: Two other settings you may want to also use is net_write_timeoutand net_read_timeout.

编辑：您可能还想使用的另外两个设置是net_write_timeout和net_read_timeout。

回答by uwe

In MAMP (non-pro version) I added

在 MAMP（非专业版）中，我添加了

--max_allowed_packet=268435456

to ...\MAMP\bin\startMysql.sh

到 ...\MAMP\bin\startMysql.sh

Credits and more details here

积分和更多细节在这里

回答by Saurabh Goyal

This error is occur due to expire of wait_timeout .

此错误是由于 wait_timeout 过期而发生的。

Just go to mysql server check its wait_timeout :

只需转到 mysql 服务器检查其 wait_timeout ：

mysql> SHOW VARIABLES LIKE 'wait_timeout'

mysql> set global wait_timeout = 600 # 10 minute or maximum wait time out you need

mysql> SHOW VARIABLES LIKE 'wait_timeout'

mysql> set global wait_timeout = 600 # 10 分钟或您需要的最大等待时间

http://sggoyal.blogspot.in/2015/01/2006-mysql-server-has-gone-away.html

http://sggoyal.blogspot.in/2015/01/2006-mysql-server-has-gone-away.html

回答by jozo

There are several causes for this error.

导致此错误的原因有多种。

MySQL/MariaDB related:

MySQL/MariaDB 相关：

• wait_timeout- Time in seconds that the server waits for a connection to become active before closing it.
• interactive_timeout- Time in seconds that the server waits for an interactive connection.
• max_allowed_packet- Maximum size in bytes of a packet or a generated/intermediate string. Set as large as the largest BLOB, in multiples of 1024.

• wait_timeout- 服务器在关闭连接之前等待连接变为活动状态的时间（以秒为单位）。
• interactive_timeout- 服务器等待交互式连接的时间（以秒为单位）。
• max_allowed_packet- 数据包或生成/中间字符串的最大字节大小。设置为与最大 BLOB 一样大，为 1024 的倍数。

Example of my.cnf:

my.cnf示例：

[mysqld]
# 8 hours
wait_timeout = 28800
# 8 hours
interactive_timeout = 28800
max_allowed_packet = 256M

Server related:

服务器相关：

• Your server has full memory - check info about RAM with free -h

• 您的服务器内存已满 - 检查有关 RAM 的信息 free -h

Framework related:

框架相关：

• Check settings of your framework. Django for example use CONN_MAX_AGE(see docs)

• 检查您的框架的设置。以 Django 为例使用CONN_MAX_AGE（见文档）

How to debug it:

如何调试它：

• Check values of MySQL/MariaDB variables.
  • with sql: SHOW VARIABLES LIKE '%time%';
  • command line: mysqladmin variables
• Turn on verbosity for errors:
  • MariaDB: log_warnings = 4
  • MySQL: log_error_verbosity = 3
• Check docs for more info about the error

• 检查 MySQL/MariaDB 变量的值。
  • 使用 sql： SHOW VARIABLES LIKE '%time%';
  • 命令行： mysqladmin variables
• 为错误打开详细信息：
  • 玛丽亚数据库： log_warnings = 4
  • MySQL： log_error_verbosity = 3
• 检查文档以获取有关错误的更多信息

回答by Kenneth mwangi

On windows those guys using xampp should use this path xampp/mysql/bin/my.ini and change max_allowed_packet(under section[mysqld])to your choice size. e.g

在 Windows 上，那些使用 xampp 的人应该使用此路径 xampp/mysql/bin/my.ini 并将 max_allowed_pa​​cket(under section[mysqld]) 更改为您选择的大小。例如

max_allowed_packet=8M

Again on php.ini(xampp/php/php.ini) change upload_max_filesize the choice size. e.g

再次在 php.ini(xampp/php/php.ini) 上更改 upload_max_filesize 选择大小。例如

upload_max_filesize=8M

Gave me a headache for sometime till i discovered this. Hope it helps.

让我头痛了一段时间，直到我发现了这一点。希望能帮助到你。

回答by Pikamander2

I was getting this same error on my DigitalOcean Ubuntu server.

我在我的 DigitalOcean Ubuntu 服务器上遇到了同样的错误。

I tried changing the max_allowed_packet and the wait_timeout settings but neither of them fixed it.

我尝试更改 max_allowed_pa​​cket 和 wait_timeout 设置，但它们都没有修复它。

It turns out that my server was out of RAM. I added a 1GB swap fileand that fixed my problem.

结果是我的服务器内存不足。我添加了一个1GB 的交换文件并解决了我的问题。

Check your memory with free -hto see if that's what's causing it.

检查你的记忆，free -h看看是否是造成它的原因。