现象描述
报错:
Caused by: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (2662 > 1024). You can change this value on the server by setting the max_allowed_packet' variable.
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3756)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2449)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2450)
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2006)
... 54 more
打开Navicat for mysql,右键连接,选择命令列界面:
输入:
SHOW VARIABLES LIKE '%max_allowed_packet%';
我这里输出的是1024,这个变量的作用是限制每次插入数据的长度,1024显然太小。
临时修改:
SET GLOBAL max_allowed_packet = 2*1024*1024*16;//修改为32M
一般立即生效,但是Navicat下需重新连接数据库再查询才生效。
永久修改:
修改my.ini文件,增加如下配置:
[mysqld]
max_allowed_packet = 20M
重启MySQL生效。