C/C++教程

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

本文主要是介绍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)

http://img2.sycdn.imooc.com/6129ea0500012d4514980654.jpg

右键关闭服务


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

MySQL中默认大小是4M

# 设置查询数据包大小

max_allowed_packet=50*1024*1024

http://img2.sycdn.imooc.com/6129ef2700013f8f05400261.jpg

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

set global max_allowed_packet = 50*1024*1024;


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

 show VARIABLES like '%max_allowed_packet%';

http://img1.sycdn.imooc.com/6129f0f500016da405650202.jpg

已修改成功!











这篇关于Packet for query is too large (5,757,009 4,194,304).的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!