MySQL Error 1153 - Got a packet bigger than 'm

Question

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 1

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

Answer 2

Sometimes type setting:

max_allowed_packet = 16M

in my.ini is not working.

Try to determine the my.ini as follows:

set-variable = max_allowed_packet = 32M

or

set-variable = max_allowed_packet = 1000000000

Then restart the server:

/etc/init.d/mysql restart

Answer 3

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 

Answer 4

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

Answer 5

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.

Answer 6

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
#