Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 2 down vote accepted

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

share|improve this answer
    
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 at 12:05

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

share|improve this answer

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 sneakiung suspicion that the lowest one wins...

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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