Link to home
Start Free TrialLog in
Avatar of itontap001
itontap001

asked on

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

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="<username>
" -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<username> -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

Please advise, thank you.
Avatar of ashraf882
ashraf882
Flag of Bangladesh image

Try increasing more size value.
Avatar of itontap001
itontap001

ASKER

Are you referring to increasing the size of max_allowed_packet?

From what I've read in several posting, 1GB is the limit for max_allowed_packet so it is the maximum value.

http://mysql.com/news-and-events/newsletter/2003-08/a0000000216.html
ASKER CERTIFIED SOLUTION
Avatar of db2inst1
db2inst1
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Super!