Friday, August 23, 2013

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

Today I was importing a MySQL database and ran into this Error “Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes”.

Basically , When you dump table data from MySQL, you may end up pulling a large chunk of data and it may exceed the MySQL client’s max_allowed_packet variable. If that happens, you might catch an error like this:

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `tablename` at row: 1627

The default max_allowed_packet size is 25M, and you can adjust it for good within your my.cnf by setting the variable in a section for mysqldump:
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. Keeping the session open create a 2nd session in which to run the import.

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)

Keep the mysql prompt open, and run your command-line SQL execution on a second terminal..