MySQL mysqldump 错误：数据包大于 max_allowed_packet'
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8815445/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysqldump error: Got packet bigger than max_allowed_packet'
提问by Varun
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 数据库中。早些时候，我支持最大 10 MB 的邮件大小，因此存储邮件内容的“mediumtext”列就足够了。现在我需要支持高达 30MB 的邮件。所以我将列的数据类型更改为“大文本”。昨天存储了一封大小为 25 MB 的邮件。之后，每当我执行 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 配置中，我将“max_allowed_packet”从 64M 增加到 512M，但它仍然失败并出现相同的错误。在运行 MySQL 服务器的同一台机器上执行 mysqldump 命令。我该如何解决这个问题？
回答by Roman Newaza
- You can add
--max_allowed_packet=512Mto yourmysqldumpcommand. - Or add
max_allowed_packet=512Mto[mysqldump]section of yourmy.cnf(thanks @Varun)
- 您可以添加
--max_allowed_packet=512M到您的mysqldump命令中。 - 或添加
max_allowed_packet=512M到[mysqldump]您的部分my.cnf（感谢@Varun）
Note: it will not work if it is not under the [mysqldump]section...
注意：如果它不在该[mysqldump]部分下，它将不起作用...
回答by knipp
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<> 16Min your standard /etc/mysql/my.cnfpreviously, 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)。如果您之前在标准中设置了max_allowed_packet<> ，新的配置文件将覆盖该设置。因此，请务必检查这个新的配置文件，然后删除该条目或根据您的需要进行调整。16M/etc/mysql/my.cnf
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 中配置文件的布局方式进行了更改。
回答by Shewchuck
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 显示正常）。我最终重新创建了表，然后它在一个只有 128M 的小数据包大小的情况下运行良好。
