mysqldump error:数据包大于max_allowed_packet'

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:

我的应用程序在IMAP上下载邮件，并将它们存储在MySQL数据库中。之前我支持的邮件大小为10mb，因此一个“mediumtext”列存储邮件内容就足够了。现在我需要支持30MB的邮件。因此我将列的数据类型改为“largetext”。昨天储存了一封25mb大小的邮件。当我执行mysqldump命令时，它会抛出错误:

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.

第3369行包含25 MB的邮件。

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 config中，我将'max_allowed_packet'从64M增加到512M，但它仍然失败，并且仍然有相同的错误。在MySQL服务器运行的同一台机器上执行mysqldump命令。我怎么解决这个问题?

3 个解决方案

#1

You can add --max_allowed_packet=512M to your mysqldump command.
您可以向mysqldump命令添加—max_allowed_packet=512M。
Or add max_allowed_packet=512M to [mysqldump] section of your my.cnf (thanks @Varun)
或者将max_allowed_packet=512M添加到my.cnf(感谢@Varun)

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

注意:如果不在[mysqldump]部分中，它将不起作用……

#2

Some of my scripts stopped working after an upgrade to Debian 9 & MariaDB.

在升级到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.

Debian的MariaDB引入了一个新的配置文件，专门用于mysqldump设置(/etc/mysql/conf.d/mysqldump.cnf)。如果您之前在标准/etc/mysql/my.cnf中设置了max_allowed_packet <> 16M，那么新的配置文件将覆盖该设置。所以一定要检查这个新的配置文件，删除条目或者根据需要调整条目。

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.

我不确定这个更改是由从MySQL到MariaDB的交换引入的，还是Debian对V9中配置文件的布局做了更改。

#3

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.

我有一个类似的错误，并且在第0行上的数据包大小为512M时失败。这是一个innodb表，显然被损坏了(mysqlcheck显示为OK)。最后我重新创建了这个表，然后它在一个只有128M大小的小数据包中运行得很好。

#1

You can add --max_allowed_packet=512M to your mysqldump command.
您可以向mysqldump命令添加—max_allowed_packet=512M。
Or add max_allowed_packet=512M to [mysqldump] section of your my.cnf (thanks @Varun)
或者将max_allowed_packet=512M添加到my.cnf(感谢@Varun)