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=ut f8 --user="<username>
" -p --all-databases > "/nfs/backup/mysql01/daily run/200912 16043001/b ackup.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.
mysqldump --opt --extended-insert --single-transaction --create-options --default-character-set=ut
" -p --all-databases > "/nfs/backup/mysql01/daily
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.
Try increasing more size value.
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
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
Ya, max is 1 GB. Look here also-
http://dev.mysql.com/doc/refman/5.1/en/mysqld-safe.html
http://dev.mysql.com/doc/refman/5.1/en/mysqld-safe.html
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Super!