前阵子在进行mysqldump数据导出的时候遇到了Error 2020的问题:
mysqldump: Error 2020: Got packet bigger than ‘max_allowed_packet’ bytes when dumping table `txt_blt_bas_txt` at row: 141529
这个报错是由于数据超出max_allowed_packet的限制导致的报错
下面是这个参数在官方文档给出的解释:
属性 | 值 |
---|---|
动态变量 | 是 |
命令行模式 | –max-allowed-packet=# |
数据类型 | 数值 |
最大值 | 1073741824(1GB) |
最小值 | 1024(1B) |
系统变量 | max_allowed_packet |
范围 | 全局、会话 |
默认值(<=版本5.6.6) | 1048576(1MB) |
默认值(>=版本5.6.6) | 4194304(4MB) |
max_allowed_packet是指 一个包 或 生成的值或中间值 或 通过mysql_stmt_send_log_data ( ) C API 函数发送的参数 的最大值。默认值为4MB。
数据包消息缓冲区初始化为net_buffer_length字节,但是在你需要的时候可以增长,max_allowed_packet的值,默认情况下,这个值设置的很小,不足以捕获较大的包。
如果使用BLOB列或长字符串,则必须增加这个值。它应该与您的大字段所想要使用的值一样大。协议限制max_allowed_packet最大值为1GB。这个值应该是1024的整数倍,非整数倍向下取舍到最接近的倍数。
通过更改max_allowed_packet变量的值来更改消息缓冲区大小时,如果客户度程序允许,还应该更改客户端的缓冲区大小。max_allowed_packet内置于客户端库的默认值为1GB,但单个客户端程序可能会覆盖此值。例如,mysql和mysqldump的默认值分别为16MB和24MB。它们还允许您通过max_allowed_packet在命令行或选项文件中进行设置来更改客户端值。
此变量的会话值是只读的。客户端最多可以接受与会话值一样多的字节。但是,服务器不会像客户端发送比当前全局max_allowed_packet值更多的字节。(如果在客户端连接后更改全局值,则全局值可能小于会话值)
上次在导出的时候发现max_allowed_packet的值为100M
在导出的时候报错了,修改了改参数的值为1G
set global max_allowed_packet=1073741824;
然后继续用mysqldump导出,但导出依旧报错,匪夷所思。
后来采用第三方工具(Navicat)导出后,成功导出。
今天在查看官方文档的时候发现这样的一段话:
也就是说,数据库设置成1G后,用mysql或mysqldump命令进行导出的时候,这个1G的值会被客户端工具的默认值覆盖。
今天在用mysqldump导出的时候加上了 --max-allowed-packet=1073741824参数后就顺利把数据导出了。
结论:
max_allowed_packet的值应该大于等于要导出表的最大字段的最大值,也就是说如果你的表中的某一行的A列的字节为40MB,那么这个值应该大于40MB,否则在导出该行的时候会报错。
在遇到max_allowed_packet的报错时,不仅要考虑是否是数据库的max_allowed_packet值设置的小了,还要考虑客户端工具的默认值,如果调整数据库的值后依旧报错,那么就可能是客户端工具的问题了
官方文档提到,mysql和mysqldump客户端工具的默认值分别为16MB和24MB,而且在使用客户端工具时,这个值会覆盖数据库设置的值,所以在用客户端工具时应加上–max-allowed-packet=xxxx参数。
例:
mysql --max-allowed-packet=1073741824 -uroot -pxxx database_name < xxxx.sql
mysqldump --max_allowed_packet=1073741824 -uroot -pxxx database_name table_name > xxxx.sql