mysqldump error: Got packet bigger than max_allowed_packet'

MysqlMysqldump

Mysql Problem Overview

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 mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table ib_mailbox_backup at row: 3369
 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?

Mysql Solutions

Solution 1 - Mysql

  1. You can add --max_allowed_packet=512M to your mysqldump command.
  2. Or add max_allowed_packet=512M to [mysqldump] section of your my.cnf (thanks @Varun)

Note: it will not work if it is not under the [mysqldump] section...

Solution 2 - Mysql

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.

Solution 3 - Mysql

I had a similar error and would fail with packet size 512M on row 0. It was an innodb table that was apparently damaged (mysqlcheck showed OK). I ended up re-creating the table and then it worked fine with a small packet size of just 128M.

Solution 4 - Mysql

This Work For me.

mysqldump --max_allowed_packet=512M  --routines=true -u [user] [database] > [route and File name].sql
Categories
Recommended Mysql Solutions
Recommended Mysqldump Solutions

Previous Solution:

'cl' is not recognized as an internal or external command,

C++Qt

Next Solution:

Concurrency: Atomic and volatile in C++11 memory model

C++MultithreadingConcurrencyC++11Parallel Processing

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionVarunView Question on Stackoverflow
Solution 1 - MysqlRoman NewazaView Answer on Stackoverflow
Solution 2 - MysqlknippView Answer on Stackoverflow
Solution 3 - MysqlShewchuckView Answer on Stackoverflow
Solution 4 - MysqlManuel de Jesus Ramos SaballosView Answer on Stackoverflow