mysqldump error: Got packet bigger than max_allowed_packet'
Solution 1
- You can add
--max_allowed_packet=512M
to yourmysqldump
command. - Or add
max_allowed_packet=512M
to[mysqldump]
section of yourmy.cnf
(thanks @Varun)
Note: it will not work if it is not under the [mysqldump]
section...
Solution 2
Some of my scripts stopped working after an upgrade to Debian 9 & MariaDB.
MariaDB on Debian introduces a new config file specifically for mysqldump settings (/etc/mysql/conf.d/mysqldump.cnf
). If you had set a max_allowed_packet
<> 16M
in your standard /etc/mysql/my.cnf
previously, the new config file will overwrite that setting. So be sure to check this new config file and either delete the entry or adjust it to your needs.
I'm not sure if the change was introduced by the swap from MySQL to MariaDB or if Debian made a change in how the config files are laid out in V9.
Varun
Updated on July 05, 2022Comments
-
Varun almost 2 years
My application download mails over IMAP and stores them in a MySQL database. Earlier I was supporting mails size upto 10 MB and hence a 'mediumtext' column to store the mail content was enough. Now I need to support mails upto 30MB. So I changed the datatype for the column to 'largetext'. Yesterday a mail with size 25 MB was stored. After that whenever I execute mysqldump command it throws error:
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `ib_mailbox_backup` at row: 3369
Row 3369 contains the 25 MB mail.
In MySQL config I increased the 'max_allowed_packet' from 64M to 512M and it still fails with the same error. Executing the mysqldump command on the same machine where MySQL server is running. How do I solve this?
-
Varun over 12 yearsIt worked. Is there a way to put this in MySQL configuration file?
-
Roman Newaza over 12 yearsIt will not work as this is mysqldump option, but you can create alias if you're under Unix family OS.
-
Varun over 12 yearsCreated a section [mysqldump] and added max_allowed_packet=200M to my conf file and it works.
-
Roman Newaza over 12 yearsGood to know! Thanks for sharing!
-
Martin Nuc about 10 yearsI had to use
--max-allowed-packet=...
as a command line parameter. Check outmysqldump --help
if your version doesn't know--max_allowed_packet
-
Hiphop03199 about 8 yearsCan you explain what the specific problem was here- was it a config issue?
-
knipp almost 7 yearsEditing
my.cnf
might not be enough as new config files specifically for mysqldump could overwrite the setting in my.cnf (see my answer further below). -
Jignesh M. Khatri almost 4 yearsThe
[mysqldump]
section is important here. I had 1G formax_allowed_packet
under[mysqld]
but still it was causing this issue in dump. After adding under[mysqldump]
it worked. Thanks.