问题:com.mysql.jdbc.PacketTooBigException: Packet for query is too large (23588610 > 20971520). You can change this value on the server by setting the max_allowed_packet’ variable.
原因: 一次性插入的数据超过了mysql 默认的server接收的数据包大小,因此需要调整mysql 默认的server接收的数据包大小。
-
MySQL会根据配置文件限制server接收的数据包大小
-
在执行比较大数据插入或者更新的时候,会根据配置文件“max_allowed_packet ”参数所限制
解决方案:
修改配置,即是扩大配置限制
1.查看配置
- 进入数据库,执行如下查询,查看MySQL配置
#show VARIABLES like '%max_allowed_packet%';
mysql> show variables like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| max_allowed_packet | 20971520 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)
2、修改配置
- 修改配置,即是扩大配置限制,将原配置20M调整为40M
#临时设置为40M
mysql> set global max_allowed_packet = 4*1024*1024*10;
Query OK, 0 rows affected (0.00 sec)
#关闭数据库连接后,重新连接数据库
#查看配置是否生效
mysql> show variables like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| max_allowed_packet | 41943040 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)
3、修改mysql配置文件,永久生效
max_allowed_packet = 40M