Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table

Question

I'm getting the above mentioned error when backing up with ZRM, which is using mysqldump for backup.

mysqldump --opt --extended-insert --single-transaction --create-options --default-character-set=utf8 --user=" " -p --all-databases > "/nfs/backup/mysql01/dailyrun/20091216043001/backup.sql"

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table TICKET_ATTACHMENT at row: 2286

I have increased the size for 'max_allowed_packet' to be 1G in /etc/my.cnf which is the server setting and for the client side setting I've set it by running this command:

mysql -u -p --max_allowed_packet=1G

And I have verified that on the client and server side they are of the same value.

This is to check the client side value according to this forum posting http://forums.mysql.com/read.php?35,75794,261640

mysql> SELECT @@MAX_ALLOWED_PACKET -> ; +----------------------+ | @@MAX_ALLOWED_PACKET | +----------------------+ | 1073741824 | +----------------------+ 1 row in set (0.00 sec)

And this is the check the server value setting.

mysql> SHOW VARIABLES

| max_allowed_packet | >

1073741824 |

I have ran out of ideas, and tried searching within expert exchange and googling for solutions but so far none has worked.

Reference http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

Anyone please advise, thank you.

Answer 1

That's not 'alternatively'. mysqldump is notorious for ignoring this value in my.cnf, but setting it as the command line parameter always works.

Answer 2

This a reply from expert exchange:

You will have to increase the max_allowed_packet under the mysqldump section in the my.cnf, the default is 25M

[mysqldump] max_allowed_packet = 1G

Alternatively, you can invoke mysqldump with the desired max_allowed_packet value.

mysqldump --max_allowed_packet=1G

Answer 3

Please use the below command to fix your issue.

# mysqldump --max_allowed_packet=1024M -u root -p database > dumpfile.sql 

This will solve your issue

Note: You can set a maximum value as you want. Here we have used 1024M as an example.

Answer 4

There are several places you may need to address this: On the command line, in the [mysqld] section of my.cnf, and the [mysqldump] section of my.cnf. I was just fighting with this and finally got it working by setting all of those to 2G. I have a sneaking suspicion that the lowest one wins...