[ERR] 1153 - Got a packet bigger than 'max_allowed_packet' bytes 解决方法

简介

max_allowed_packet 表示MySQL客户端和服务器之间，单条消息最大允许传输的数据包大小。

如果单条SQL插入或更新的数据包超过此大小，就会报错：
[ERR] 1153 - Got a packet bigger than 'max_allowed_packet' bytes

官方解释：

max_allowed_packet sets an upper limit on the size of any single message between the MySQL server and clients, including replication slaves. If you are replicating large column values (such as might be found in TEXT or BLOB columns) and max_allowed_packet is too small on the master, the master fails with an error, and the slave shuts down the I/O thread. If max_allowed_packet is too small on the slave, this also causes the slave to stop the I/O thread.

查看max_allowed_packet配置

mysql> SHOW GLOBAL VARIABLES LIKE '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 4194304    |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.02 sec)

mysql> 

max_allowed_packet=4194304，即当前服务器最大的数据包大小为4M。
slave_max_allowed_packet=1073741824，为从服务器(IO线程)设定一个和max_allowed_packet不同的限制，从服务器端最大的数据包大小由这个参数决定，默认大小为1G。

修改max_allowed_packet配置

方法一：SET GLOBAL max_allowed_packet设置

修改max_allowed_packet最大为32M

mysql> SET GLOBAL max_allowed_packet = 32 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 33554432   |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.03 sec)

mysql> 

注意：set global只是全局session生效，重启mysql后就会失效。

如果希望修改配置永久生效，可采用方法二修改my.cnf或my.ini配置。

方法二：修改my.cnf或my.ini配置文件

Linux下为my.cnf文件，通常位置在：/etc/my.cnf；
Windows下为my.ini文件，通常在安装根目录下。

mysqld部分新增max_allowed_packet配置

[mysqld]

max_allowed_packet=32M

修改完重启mysql服务后生效。