Packet for query is too large (5,757,009 > 4,194,304).

今天在往MySql中插入1000万数据时，报了这个错误，记录一下，错误日志如下：

### Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (5,757,009 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.

; Packet for query is too large (5,757,009 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.; nested exception is com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (5,757,009 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.] with root cause

com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (5,757,009 > 4,194,304). 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) ~[mysql-connector-java-8.0.16.jar:8.0.16]

at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955) ~[mysql-connector-java-8.0.16.jar:8.0.16]

at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:372) ~[mysql-connector-java-8.0.16.jar:8.0.16]

at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497) ~[druid-1.2.5.jar:1.2.5]

at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46) ~[mybatis-3.4.5.jar:3.4.5]

at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.4.5.jar:3.4.5]

at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.4.5.jar:3.4.5]

at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.4.5.jar:3.4.5]

at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.4.5.jar:3.4.5]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_281]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_281]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_281]

at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_281]

at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) ~[mybatis-3.4.5.jar:3.4.5]

at com.sun.proxy.$Proxy109.update(Unknown Source) ~[na:na]

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198) ~[mybatis-3.4.5.jar:3.4.5]

at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185) ~[mybatis-3.4.5.jar:3.4.5]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_281]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_281]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_281]

at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_281]

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ~[mybatis-spring-1.3.1.jar:1.3.1]

at com.sun.proxy.$Proxy90.insert(Unknown Source) ~[na:na]

at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278) ~[mybatis-spring-1.3.1.jar:1.3.1]

at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:57) ~[mybatis-3.4.5.jar:3.4.5]

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) ~[mybatis-3.4.5.jar:3.4.5]

at com.sun.proxy.$Proxy94.saveMerchantInfoList(Unknown Source) ~[na:na]

at com.lhf.springboot.millionsimport.service.impl.TMerchantInfoAServiceImpl.saveMerchantInfoList(TMerchantInfoAServiceImpl.java:79) ~[classes/:na]

at com.lhf.springboot.millionsimport.service.impl.TMerchantInfoAServiceImpl.addMerchantInfoA(TMerchantInfoAServiceImpl.java:51) ~[classes/:na]

at com.lhf.springboot.millionsimport.service.impl.TMerchantInfoAServiceImpl$$FastClassBySpringCGLIB$$9e6304ed.invoke(<generated>) ~[classes/:na]

at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.1.8.RELEASE.jar:5.1.8.RELEASE]

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749) ~[spring-aop-5.1.8.RELEASE.jar:5.1.8.RELEASE]

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.1.8.RELEASE.jar:5.1.8.RELEASE]

at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295) ~[spring-tx-5.1.8.RELEASE.jar:5.1.8.RELEASE]

错误的大概意思是：

原因：com.mysql.cj.jdbc.exceptions.PacketTooBigException:用于查询的数据包太大（5757009>4194304）。您可以通过设置“max_allowed_packet”变量在服务器上更改此值。

; 用于查询的数据包太大（5757009>4194304）。您可以通过设置“max_allowed_packet”变量在服务器上更改此值。；嵌套异常为com.mysql.cj.jdbc.exceptions.PacketTooBigException:用于查询的数据包太大（5757009>4194304）。您可以通过设置“max_allowed_packet”变量来更改服务器上的此值。]并带有根本原因

解决办法：

1. 先关闭MySQL服务，以免后续的修改不生效。Windows环境下，找到MySQL服务关闭（计算机管理---》服务和应用程序---》服务——》找到MySQL）

右键关闭服务

2. 找到MySQL的安装目录下的my.ini文件，打开添加如下配置，此值你可以根据自己的需要来修改：

MySQL中默认大小是4M

# 设置查询数据包大小

max_allowed_packet=50*1024*1024

也可通过如下命令来设置：

set global max_allowed_packet = 50*1024*1024;

3. 修改完毕保存之后，重新启动MySQL服务，然后使用如下命令来查看设置：

 show VARIABLES like '%max_allowed_packet%';

已修改成功！