Error 2020 Mysqldump
Contents |
October 11, 2007 By Major Hayden 7 Comments mariadb max_allowed_packet When you dump table data from MySQL, you may end up pulling a mysqldump: got errno 28 on write large chunk of data and it may exceed the MySQL client's max_allowed_packet variable. If that happens, you might catch an error like this: mysqldump: Error 2020: Got packet
Mysqldump Quick
bigger than 'max_allowed_packet' bytes when dumping table tablename at row: 1627 The default max_allowed_packet size is 25M, and you can adjust it for good within your my.cnf by setting the variable in a section for mysqldump: [mysqldump]
max_allowed_packet = 500M Share this post:TwitterGoogleLinkedInRedditEmailPrintTagged With: database, mysql Send to Email Address Your Name Your Email Address Cancel Post was not sent - check your email addresses! Email check failed, please try again Sorry, your blog cannot share posts by email.
Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us
Set Global Max_allowed_packet
Learn more about Stack Overflow the company Business Learn more about hiring developers man mysqldump or posting ads with us Server Fault Questions Tags Users Badges Unanswered Ask Question _ Server Fault is a question and mysqldump single-transaction answer site for system and network administrators. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up https://major.io/2007/10/11/mysqldump-got-packet-bigger-than-max_allowed_packet-bytes/ and rise to the top Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table up vote 4 down vote favorite 1 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 http://serverfault.com/questions/102564/error-2020-got-packet-bigger-than-max-allowed-packet-bytes-when-dumping-table 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. mysql share|improve this question asked Jan 14 '10 at 7:37 Imagineer 155416 add a comment| 3 Answers 3 active oldest votes 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 answered Apr 13 '10 at 21:43 Shr Usually people edit the wrong .my.cnf file or
11:18 — Shinguz We recently run into some troubles with max_allowed_packet size problems during backups with the FromDual Backup/Recovery Manager and thus I investigated a bit more in the http://www.fromdual.com/impacts-of-max_allowed_packet-size-problems-on-your-mysql-database symptoms of such problems. Read more about: max_allowed_packet. A general rule for max_allowed_packet size to avoid problems is: All clients and the server should have set the same value for max_allowed_packet size! I prepared some data for the test which looked as follows: mysql> SELECT id, LEFT(data, 30), LENGTH(data), ts FROM test; +----+--------------------------------+--------------+------+ | id | left(data, 30) | length(data) error 2020 | ts | +----+--------------------------------+--------------+------+ | 1 | Anhang | 6 | NULL | | 2 | Anhang | 6 | NULL | | 3 | Anhangblablablablablablablabla | 2400006 | NULL | | 4 | Anhang | 6 | NULL | +----+--------------------------------+--------------+------+Max_packet_size was set to a too small value then: mysql> SHOW GLOBAL VARIABLES WHERE variable_name = 'max_allowed_packet'; +--------------------+---------+ | Variable_name | error 2020 mysqldump Value | +--------------------+---------+ | max_allowed_packet | 1048576 | +--------------------+---------+The first test was to retrieve the too big row: mysql> SELECT * FROM test WHERE id = 3; ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes mysql> SELECT CURRENT_USER(); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 6 Current database: testWe got an error message AND we were disconnected from the server. This is indicated with the message MySQL server has gone away which is basically wrong. We were disconnected and not the server has died or similar in this case. A further symptom is that we get an entry in the MySQL error log about this incident: [Warning] Aborted connection 3 to db: 'test' user: 'root' host: 'localhost' (Got an error writing communication packets)So watching carefully such error messages in your MySQL error log with the script check_error_log_mysql.pl from our Nagios/Icinga plugins would be a good idea... The mysqldump utility basically does the same as a SELECT command so I tried this out and got the same error: shell> mysqldump -u root test