第406行的MySQL错误2006(HY000):MySQL服务器已消失 [英] MySQL Error 2006 (HY000) at line 406: MySQL server has gone away

问题描述

我有一个试图从数据库迁移到新数据库服务器的MYSQL转储.当我尝试导入SQL转储时，出现以下错误:

I have a MYSQL dump from a database that I am trying to move to a new db server. When I try to import my sql dump, I receive the following error:

MySQL Error 2006 (HY000) at line 406: MySQL server has gone away

我搜索了该问题，大多数人通过更改wait_timeout的值来解决该问题.但是，我的当前值设置为28800(8小时)，运行导入时不到8秒就会出现错误.

I googled the problem and most people fixed the problem by changing the value of wait_timeout. However, my current value is set to 28800 (8 hours) and the error appears in less than 8 seconds when I run the import.

我还尝试将max_allowed_pa​​cket的值设置为1073741824，但这也不能解决问题.

I also tried setting the value of max_allowed_packet to 1073741824 but that also did not fix the problem.

在mysql转储中，转储中有很多blob列，但总文件大小仅为6 MB.

Looking through the mysql dump, there are quite a few blob columns in the dump, but the overall file size is only 6 MB.

有人对可能还有其他问题有任何想法吗?

Does anyone have any ideas about what else might be the problem?

推荐答案

添加此答案以方便将来的搜索者使用，因为它说明了为什么增加数据包大小可以解决此问题的原因:

Adding this answer for the benefit of future searchers, as it explains why increasing the packet size fixed the problem:

这种情况是，如果客户端发送的SQL语句长于服务器max_allowed_pa​​cket设置，则服务器将简单地断开客户端的连接.从同一客户端实例进行的下一个查询将发现"MySQL服务器已消失".

The situation is that if a client sends a SQL-statement longer than the server max_allowed_packet setting, the server will simply disconnect the client. Next query from the same client instance will find that the ‘MySQL server has gone away’.

...但是，如果出现问题，则返回更大的数据包"错误[Error:2020(CR_NET_PACKET_TOO_LARGE)]当然更可取.

... But it would of course be much preferable to have the ‘got packet bigger’ error [Error: 2020 (CR_NET_PACKET_TOO_LARGE)] returned if that is the problem.

摘录自在OSX 10.7(Lion)上，我创建了一个文件/etc/my.cnf，其内容如下:

On OSX 10.7 (Lion), I created a file, /etc/my.cnf with the following contents:

[mysqld]
max_allowed_packet = 12000000

然后停止mysql服务器:

And then stopped the mysql server:

/usr/local/bin/mysql.server stop

当它自动重新启动时，我能够执行我的插入操作.

When it automatically restarted I was able to execute my inserts.

这篇关于第406行的MySQL错误2006(HY000):MySQL服务器已消失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！