6

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.

5

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

  • 2
    Usually people edit the wrong .my.cnf file or forget to add the option to [mysqldump] part and put it only in [mysqld] or [client], that is why mysqldump "doesn't read the .my.cnf" – higuita Jan 17 '14 at 12:05
5

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

0

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

  • At this time max_allowed_packet is limited to 1G of RAM. – Wilson Hauck May 8 '18 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.