mysql插入数据报错:Packet for query is too large
本文由 小茗同学 发表于 2016-06-23 浏览(1942)
最后修改 2016-12-13 标签:mysql 插入 报错

现象描述

报错:

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生效。