kch kch - 4 months ago 18
MySQL Question

MySQL Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes

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.




This is on my local machine, a Mac with MySQL 5 installed from the MySQL package.

Where do I change
max_allowed_packet
to be able to import the dump?

Is there anything else I should set?

Just running
mysql --max_allowed_packet=32M …
resulted in the same error.

Answer

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

Also, change the my.cnf or my.ini file under the mysqld section and set:

max_allowed_packet=100M

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.)