Main menu

mysqldump: Got packet bigger than ‘max_allowed_packet’ bytes

We often get this error while restoring or dumping a large database on MySQL or MariaDB. it comes when chunk size exceed the MySQL clients variable  max_allowed_packet and you will get an error like this..

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

OR

Warning: option 'max_allowed_packet': unsigned value 5368709120 adjusted to 2147483648

Check the current max_allowed_packet size in MysQL

Login into MySQL console and run show variables command

# mysql -u root -p
 mysql > SHOW VARIABLES LIKE 'max_allowed_packet';

max_allowed_packet while dumping the database

If you are dumping the MySQL you can adjust the parameter in your /etc/my.cnf file for max_allowed_packet to meet you needs.

[mysqldump]
 max_allowed_packet = 500M

You also can pass the max_allowed_packet in your mysqldump

# mysqldump -u root -p --max_allowed_packet=1G poll  > database.sql

max_allowed_packet while restoring the database

If you having the problem while restoring the database you can change the /etc/my.cnf in [mysqld] section  for max_allowed_packet

[mysql]
mysql --max_allowed_packet=32M

Or you can also pass the MySQLl max_allowed_packet parameter while restoring the database

# mysql -u root -p --max_allowed_packet=32M yourdb < dump.sql
FacebookTwitterGoogle+RSS