MySQL错误1153-收到了一个大于'max_allowed_packet'字节的数据包 [英] MySQL Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes
问题描述
我正在导入MySQL转储，并收到以下错误.
I'm importing a MySQL dump and getting the following error.
$ 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.
这是在我的本地计算机上，这是一台从MySQL软件包中安装了MySQL 5的Mac.
This is on my local machine, a Mac with MySQL 5 installed from the MySQL package.
在哪里更改max_allowed_packet以便能够导入转储?
Where do I change max_allowed_packet to be able to import the dump?
还有什么我应该设置的吗?
Is there anything else I should set?
仅运行mysql --max_allowed_packet=32M …会导致相同的错误.
Just running mysql --max_allowed_packet=32M … resulted in the same error.
推荐答案
您可能必须为客户端(您正在运行以进行导入)和正在运行并接受导入的守护程序mysqld进行更改.
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.
对于客户端，您可以在命令行上指定它:
For the client, you can specify it on the command line:
mysql --max_allowed_packet=100M -u root -p database < dump.sql
此外，更改mysqld部分下的my.cnf或my.ini文件，并设置:
Also, change the my.cnf or my.ini file under the mysqld section and set:
max_allowed_packet=100M
，或者您可以在连接到同一服务器的MySQL控制台中运行以下命令:
or you could run these commands in a MySQL console connected to that same server:
set global net_buffer_length=1000000;
set global max_allowed_packet=1000000000;
(对于数据包大小，请使用非常大的值.)
(Use a very large value for the packet size.)
这篇关于MySQL错误1153-收到了一个大于'max_allowed_packet'字节的数据包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
