MYSQL5 Big Data error: Packet for query is too large (****** > ******). You can change this value on the server by setting the Max_allowed_packet ' variable.;

MySQL database version used:5.5

When inserting or updating a field with big data (greater than 1M), the following error occurs:

# # # Cause:com.mysql.jdbc.PacketTooBigException:Packet for query is too large (1132484 > 1048576). You can change this value on the server by setting the Max_allowed_packet 'variable.; SQL []; Packet for query is too large (1132484 > 1048576). You can  change this value on the server by setting the Max_allowed_packet' variable.; nested exception is com.mysql.jdbc.PacketTooBigException:Packet for query is too large (1132484 > 1048576). You can change this value on the server by setting the Max_allowed_packet ' variable. With root causecom.mysql.jdbc.PacketTooBigException:Packet to query is too large (1132484 > 1048576).    You can change this value on the server by setting the Max_allowed_packet ' variable. At Com.mysql.jdbc.MysqlIO.send (mysqlio.java:3910)    At Com.mysql.jdbc.MysqlIO.sendCommand (mysqlio.java:2596) at Com.mysql.jdbc.MysqlIO.sqlQueryDirect (Mysqlio.java : 2776) at Com.mysql.jdbc.ConnectionImpl.execSQL (connectionimpl.java:2838) at Com.mysql.jdbc.PreparedStatement.executeInternal (preparedstatement.java:2082) at Com.mysql.jdbc.PreparedStatement.execute (preparedstatement.java:1307) at Com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute (newproxypreparedstatement.java:989) at Org.apache.ibatis.executor.statement.PreparedStatementHandler.update (preparedstatementhandler.java:44) at Org.apache.ibatis.executor.statement.RoutingStatementHandler.update (routingstatementhandler.java:69) at Org.apache.ibatis.executor.SimpleExecutor.doUpdate (simpleexecutor.java:48) at Org.apache.ibatis.executor.BaseExecutor.update (baseexecutor.java:105) at Org.apache.ibatis.executor.CachingExecutor.update (cachingexecutor.java:71) at Org.apache.ibatis.session.defaults.DefaultSqlSession.update (defaultsqlsession.java:152) at Org.apache.ibatis.session.defaults.DefaultSqlSession.insert (defaultsqlsession.java:141) 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:483) at Org.mybatis.spring.sqlsessiontemplate$sqlsessioninterceptor.invoke (sqlsessiontemplate.java:358) at Com.sun.proxy . $Proxy 97.insert (Unknown Source) at Org.mybatis.spring.SqlSessionTemplate.insert (sqlsessiontemplate.java:240) at or G.apache.ibatis.binding.mappermethod.execute (mappermethod.java:51) at Org.apache.ibatis.binding.MapperProxy.invoke (mapperproxy.java:52) at Com.sun.proxy. $Proxy 98.insertClient (Unknown Source) at Com.shiliu.game.service.impl.ExcelUserServiceImpl.insertClient (exceluserserviceimpl.java:35) at Com.shiliu.game.controller.ClientController.batchimport (Clientcontroller.java:171) at Sun.reflect.NativeMethodAccessorImpl.invoke0 (Native Method) at Sun.reflect.NativeMethodAcce Ssorimpl.invoke (nativemethodaccessorimpl.java:62) at Sun.reflect.DelegatingMethodAccessorImpl.invoke ( delegatingmethodaccessorimpl.java:43) at Java.lang.reflect.Method.invoke (method.java:483) at Org.springframework.web.method.support.InvocableHandlerMethod.invoke (invocablehandlermethod.java:219) at Org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest (invocablehandlermethod.java:132 ) at Org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle ( SERVLETINVOCABLEHANDLERMETHOD.JAVA:104) at Org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod ( requestmappinghandleradapter.java:745) at Org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal ( requestmappinghandleradapter.java:686) at Org.springframEwork.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle (abstracthandlermethodadapter.java:80) at Org.springframework.web.servlet.DispatcherServlet.doDispatch (dispatcherservlet.java:925) at Org.springframework.web.servlet.DispatcherServlet.doService (dispatcherservlet.java:856) at Org.springframework.web.servlet.FrameworkServlet.processRequest (frameworkservlet.java:936) at Org.springframework.web.servlet.FrameworkServlet.doPost (frameworkservlet.java:838) at Javax.servlet.http.HttpServlet.service (httpservlet.java:647) at Org.springframework.web.servlet.FrameworkServlet.service (frameworkservlet.java:812) at Javax.servlet.http.HttpServlet.service (httpservlet.java:728) at Org.apache.catalina.core.ApplicationFilterChain.internalDoFilter (applicationfilterchain.java:305) at Org.apache.catalina.core.ApplicationFilterChain.doFilter (applicationfilterchain.java:210) at Org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal (Characterencodingfilter.java:88) at Org.springframework.web.filter.OncePerRequestFilter.doFilter (onceperrequestfilter.java:107) at Org.apache.catalina.core.ApplicationFilterChain.internalDoFilter (applicationfilterchain.java:243) at Org.apache.catalina.core.ApplicationFilterChain.doFilter (applicationfilterchain.java:210) at Org.apache.catalina.core.StandardWrapperValve.invoke (standardwrappervalve.java:222) at Org.apache.catalina.core.StandardContextValve.invoke (standardcontextvalve.java:123) at Org.apache.catalina.authenticator.AuthenticatorBase.invoke (authenticatorbase.java:472) at Org.apache.catalina.core.StandardHostValve.invoke (standardhostvalve.java:171) at Org.apache.catalina.valves.ErrorReportValve.invoke (errorreportvalve.java:99) at Org.apache.catalina.valves.AccessLogValve.invoke (accesslogvalve.java:953) at Org.apache.catalina.core.StandardEngineValve.invoke (standardenginevalve.java:118) at Org.apache.catalina.connector.CoyoteAdapter.service (coyoteadapter.java:408)    At Org.apache.coyote.http11.AbstractHttp11Processor.process (abstracthttp11processor.java:1008) at Org.apache.coyote.abstractprotocol$abstractconnectionhandler.process (abstractprotocol.java:589) at Org.apache.tomcat.util.net.jioendpoint$socketprocessor.run (jioendpoint.java:312) at Java.util.concurrent.ThreadPoolExecutor.runWorker (threadpoolexecutor.java:1142) at Java.util.concurrent.threadpoolexecutor$worker.run (threadpoolexecutor.java:617) at Java.lang.Thread.run ( thread.java:745)

Cause of Error:

Starting to think that the corresponding field is not set correctly, beyond the maximum range of the field, after the online query found that mysql default load data files not more than 1M, You can change this default value by changing the MySQL configuration file my.cnf (Linux, or Windows My.ini) to achieve the purpose of inserting big data.

Workaround:

Set maximum value under 1.SQL queries

SHOW VARIABLES like '%max_allowed_packet% ';

2. Locate the MySQL installation directory to modify the My.ini configuration file

3. Find out if the Max_allowed_packet field exists, and if so, modify its parameters without adding it.

4. Add Max_allowed_packet Field

[Mysqld]

Max_allowed_packet = 20M

5. Restart the database:

6. Re-query whether the settings are successful.

Sometimes the command "SET GLOBAL max_allowed_packet=16*1024*1024" is invalid

You need to use the following command: SET @ @max_allowed_packet =5*1024*1024 ( This I tried is also invalid .) ）

Additional: Three if

　　if the database configuration cannot be modified

　　if you are using MyBatis, do add or modify

　　if you want to know that there are hundreds of thousands of data in the list collection in Java, each of the 100 bars is taken out how to implement

Please click on me!

MYSQL5 Big Data error: Packet for query is too large (****** > ******). You can change this value on the server by setting the Max_allowed_packet ' variable.;