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.;

Source: Internet
Author: User

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.;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.