'Packet for query is too large' exception you may come across

[Jonathan Lim-Breitbart]

Hi folks, just wanted to report that we debugged a server exception you folks with WISE installations may have come across (or may in the future): "java.sql.BatchUpdateException: Packet for query is too large" (see example below).

Basically, this occurs when trying to insert/retrieve data larger than 1mb, which is the default maximum packet allowed for mysql.  To fix this problem, you can change the max_packet_allowed variable: http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html.  We have now increased the max_packet_allowed on WISE4 production to 2M.

As the link above outlines, there are two ways to do this.  You can either start mysql using 'mysqld --max_allowed_packet=2M' (for example) or you can set this value in a my.cnf config file.

The location of my.cnf depends on your mysql installation, but you can find out where it is by typing 'mysqladmin' in the shell and scrolling up to the line "Default options are read from the following files in the given order:".

After you change the max_packet_allowed value in my.cnf, restart mysql and the problem should be fixed.

The following exception was thrown in the WISE 4.0 Portal on Wed Nov 16 16:00:29 PST 2011

username: xxxxxx
url: http://wise4.berkeley.edu:80/webapp/bridge/getdata.html?userId=xxxxxx&runId=xxxx&getAllWork=true

exception message: org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update

stacktrace:
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
       at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
       at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
       at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
       at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
       at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
       at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:168)
       at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
       at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
       at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
       at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
       at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
       at vle.domain.PersistableDomain.saveOrUpdate(PersistableDomain.java:27)
       at vle.web.VLEGetData.doGet(VLEGetData.java:234)
       at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)

       at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:306)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
       at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:672)
       at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:463)
       at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:402)
       at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:329)

       at org.telscenter.sail.webapp.presentation.web.controllers.BridgeController.handleGet(BridgeController.java:369)
       at org.telscenter.sail.webapp.presentation.web.controllers.BridgeController.handleRequestInternal(BridgeController.java:124)

       at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
       at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
       at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
       at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:807)
       at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)

       at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:501)
       at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)

Caused by: java.sql.BatchUpdateException: Packet for query is too large (1078346 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
       at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2024)
       at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1449)
       at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
       at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
       at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
       ... 76 more
Caused by: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1078346 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
       at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3279)
       at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1971)
       at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
       at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
       at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
       at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
       at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1976)
       ... 80 more

--
Jonathan Lim-Breitbart