MySQL报错：Packet for query is too large (2,588 > 2,048).

2022-12-30 21:05:27

Packet for query is too large (2,588 > 2,048).

问题现象

最近遇到一个这个奇怪的问题，在批量往数据库中插入数时，报错：

Caused by: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (2,588 > 2,048). You can change this value on the server by setting the 'max_allowed_packet' variable.
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:107)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
        at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
        at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
        at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
        ... 70 more

问题分析

从报错信息可以看到，由于 max_allowed_packet 参数设置过小，引起数据无法正常入库。

通过如下SQL可以查看参数的设置大小，此时查到的值为 2048 B

show variables like ‘%max_allowed_packet%’;

解决方案

1、通过SQL语句修改变量值

（将数据包大小设置为1GB = 1024*1024*1024 B）

set global max_allowed_packet = 1073741824

2、通过配置文件修改变量值

修改my.cnf，在文件中将变量 max_allowed_packet 值设置为1G，如下：

max_allowed_packet = 1G

修改配置文件后，重启 MySQL 服务即可，命令如下：

service mysql start

码农公寓