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

0 votes
asked Sep 18, 2008 by kch

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.

12 Answers

0 votes
answered Sep 18, 2008 by ghad

This can be changed in your my.ini file (on Windows, located in \Program Files\MySQL\MySQL Server) under the server section, for example:

[mysqld]

max_allowed_packet = 10M
0 votes
answered Sep 18, 2008 by tomasz-tybulewicz

Use a max_allowed_packet variable issuing a command like

mysql --max_allowed_packet=32M -u root -p database < dump.sql

0 votes
answered Sep 18, 2008 by markr

Set max_allowed_packet to the same (or more) than what it was when you dumped it with mysqldump. If you can't do that, make the dump again with a smaller value.

That is, assuming you dumped it with mysqldump. If you used some other tool, you're on your own.

0 votes
answered Sep 19, 2008 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.

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

0 votes
answered Sep 19, 2008 by kch

Re my.cnf on Mac OS X when using MySQL from the mysql.com dmg package distribution

By default, my.cnf is nowhere to be found.

You need to copy one of /usr/local/mysql/support-files/my*.cnf to /etc/my.cnf and restart mysqld. (Which you can do in the MySQL preference pane if you installed it.)

0 votes
answered Sep 6, 2009 by joshua-fox

As michaelpryor said, you have to change it for both the client and the daemon mysqld server.

His solution for the client command-line is good, but the ini files don't always do the trick, depending on configuration.

So, open a terminal, type mysql to get a mysql prompt, and issue these commands:

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

0 votes
answered Sep 21, 2010 by jplindstrom

Slightly unrelated to your problem, so here's one for Google.

If you didn't mysqldump the SQL, it might be that your SQL is broken.

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!

0 votes
answered Sep 2, 2013 by mike

On CENTOS 6 /etc/my.cnf , under [mysqld] section the correct syntax is:

[mysqld]
# added to avoid err "Got a packet bigger than 'max_allowed_packet' bytes"
#
net_buffer_length=1000000 
max_allowed_packet=1000000000
#
0 votes
answered Sep 28, 2013 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 -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
0 votes
answered Sep 3, 2014 by amirtha-rajan

In etc/my.cnf try changing the max_allowed _packet and net_buffer_length to

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 
Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...