MySQL 错误 1153 - 数据包大于“max_allowed_packet”字节
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/93128/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes
提问by kch
I'm importing a MySQL dump and getting the following error.
我正在导入 MySQL 转储并收到以下错误。
$ mysql foo < foo.sql
ERROR 1153 (08S01) at line 96: Got a packet bigger than 'max_allowed_packet' bytes
Apparently there are attachments in the database, which makes for very large inserts.
显然数据库中有附件，这使得插入非常大。
This is on my local machine, a Mac with MySQL 5 installed from the MySQL package.
这是在我的本地机器上，一台从 MySQL 包安装了 MySQL 5 的 Mac。
Where do I change max_allowed_packetto be able to import the dump?
我在哪里更改max_allowed_packet才能导入转储？
Is there anything else I should set?
还有什么我应该设置的吗？
Just running mysql --max_allowed_packet=32M …resulted in the same error.
只是运行mysql --max_allowed_packet=32M …导致了同样的错误。
回答by Michael Pryor
You probably have to change it for both the client (you are running to do the import) AND the daemon mysqld that is running and accepting the import.
您可能必须为客户端（您正在运行以执行导入）和正在运行并接受导入的守护进程 mysqld 更改它。
For the client, you can specify it on the command line:
对于客户端，可以在命令行中指定：
mysql --max_allowed_packet=100M -u root -p database < dump.sql
Also, change the my.cnf or my.ini fileunder the mysqld section and set:
另外，更改mysqld 部分下的 my.cnf 或 my.ini 文件并设置：
max_allowed_packet=100M
or you could run these commandsin a MySQL console connected to that same server:
或者您可以在连接到同一服务器的 MySQL 控制台中运行这些命令：
set global net_buffer_length=1000000;
set global max_allowed_packet=1000000000;
(Use a very large value for the packet size.)
（使用非常大的数据包大小值。）
回答by Joshua Fox
As michaelpryor said, you have to change it for boththe client andthe daemon mysqld server.
作为michaelpryor说，你必须改变它为双方的客户和守护程序mysqld服务器。
His solution for the client command-line is good, but the ini files don't always do the trick, depending on configuration.
他的客户端命令行解决方案很好，但根据配置，ini 文件并不总是能奏效。
So, open a terminal, type mysql to get a mysql prompt, and issue these commands:
因此，打开终端，输入 mysql 以获取 mysql 提示，并发出以下命令：
set global net_buffer_length=1000000;
set global max_allowed_packet=1000000000;
Keep the mysql prompt open, and run your command-line SQL execution on a second terminal..
保持 mysql 提示符打开，并在第二个终端上运行命令行 SQL 执行。
回答by GHad
This can be changed in your my.inifile (on Windows, located in \Program Files\MySQL\MySQL Server) under the server section, for example:
这可以my.ini在服务器部分下的文件（在 Windows 上，位于 \Program Files\MySQL\MySQL Server）中进行更改，例如：
[mysqld]
max_allowed_packet = 10M
回答by kch
Re my.cnf on Mac OS X when using MySQL from the mysql.com dmg package distribution
使用来自 mysql.com dmg 包分发的 MySQL 时，在 Mac OS X 上重新 my.cnf
By default, my.cnf is nowhere to be found.
默认情况下，my.cnf 无处可寻。
You need to copy one of /usr/local/mysql/support-files/my*.cnfto /etc/my.cnfand restart mysqld. (Which you can do in the MySQL preference pane if you installed it.)
你需要复制一个/usr/local/mysql/support-files/my*.cnf到/etc/my.cnf并重新启动mysqld。（如果您安装了它，您可以在 MySQL 首选项窗格中执行此操作。）
回答by Amirtha Rajan
In etc/my.cnf try changing the max_allowed _packet and net_buffer_length to
在 etc/my.cnf 中尝试将 max_allowed _packet 和 net_buffer_length 更改为
max_allowed_packet=100000000
net_buffer_length=1000000
if this is not working then try changing to
如果这不起作用，请尝试更改为
max_allowed_packet=100M
net_buffer_length=100K
回答by Primoz Rome
The fix is to increase the MySQL daemon's max_allowed_packet. You can do this to a running daemon by logging in as Super and running the following commands.
修复方法是增加 MySQL 守护进程的 max_allowed_packet。您可以通过以 Super 身份登录并运行以下命令来对正在运行的守护程序执行此操作。
# mysql -u admin -p
mysql> set global net_buffer_length=1000000;
Query OK, 0 rows affected (0.00 sec)
mysql> set global max_allowed_packet=1000000000;
Query OK, 0 rows affected (0.00 sec)
Then to import your dump:
然后导入您的转储：
gunzip < dump.sql.gz | mysql -u admin -p database
回答by Mike Castro Demaria
On CENTOS 6 /etc/my.cnf , under [mysqld] section the correct syntax is:
在 CENTOS 6 /etc/my.cnf 上，在 [mysqld] 部分下，正确的语法是：
[mysqld]
# added to avoid err "Got a packet bigger than 'max_allowed_packet' bytes"
#
net_buffer_length=1000000
max_allowed_packet=1000000000
#
回答by jplindstrom
Slightly unrelated to your problem, so here's one for Google.
与您的问题略有关系，因此这是 Google 的问题。
If you didn't mysqldump the SQL, it might be that your SQL is broken.
如果您没有 mysqldump SQL，则可能是您的 SQL 损坏了。
I just got this error by accidentally having an unclosed string literal in my code. Sloppy fingers happen.
我只是因为在我的代码中不小心有一个未关闭的字符串文字而得到这个错误。松懈的手指发生了。
That's a fantastic error message to get for a runaway string, thanks for that MySQL!
对于失控的字符串，这是一个很棒的错误消息，感谢您的 MySQL！
回答by Tomasz Tybulewicz
Use a max_allowed_packetvariable issuing a command like
使用max_allowed_packet发出类似命令的变量
mysql --max_allowed_packet=32M
-u root -p database < dump.sql
mysql --max_allowed_packet=32M
-u root -p database < dump.sql
回答by Grzegorz Brz?czyszczykiewicz
Sometimes type setting:
有时类型设置：
max_allowed_packet = 16M
in my.ini is not working.
在 my.ini 中不起作用。
Try to determine the my.ini as follows:
尝试确定 my.ini 如下：
set-variable = max_allowed_packet = 32M
or
或者
set-variable = max_allowed_packet = 1000000000
Then restart the server:
然后重启服务器：
/etc/init.d/mysql restart
