今天在往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%';
已修改成功!