Unable to Save Build Results to the Database due to Error 'Packet for query is too large'
Symptoms
There are errors saving builds to the database. Errors like the following appear in the logs:
2010-06-27 16:12:22,804 WARN [326882977@qtp-1882365757-0] [JDBCExceptionReporter] SQL Error: 0, SQLState: S1000
2010-06-27 16:12:22,804 ERROR [326882977@qtp-1882365757-0] [JDBCExceptionReporter] Packet for query is too large (48799425 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
2010-06-27 16:12:22,805 WARN [326882977@qtp-1882365757-0] [JDBCExceptionReporter] SQL Error: 0, SQLState: S1000
2010-06-27 16:12:22,805 ERROR [326882977@qtp-1882365757-0] [JDBCExceptionReporter] Packet for query is too large (48799425 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
2010-06-27 16:12:22,805 ERROR [326882977@qtp-1882365757-0] [SessionImpl] Could not synchronize database state with session
2010-06-27 16:12:22,806 WARN [326882977@qtp-1882365757-0] [BambooStAXMappingHelperAbstractImpl] Exception during processing builds.build #8
net.sf.hibernate.exception.GenericJDBCException: could not insert collection: [com.atlassian.bamboo.resultsummary.BuildResultsSummaryImpl.customBuildData#11174762]
.....
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:520)
Caused by: java.sql.BatchUpdateException: Packet for query is too large (48799425 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2007)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1443)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
at net.sf.hibernate.impl.BatchingBatcher.addToBatch(BatchingBatcher.java:34)
at net.sf.hibernate.collection.AbstractCollectionPersister.recreate(AbstractCollectionPersister.java:526)
... 171 more
This error can also cause XML restore to fail while saving a large build result to the database.
Cause
The MySQL packet size setting is too low.
Resolution
A common problem with MySQL is the max packet size restriction.
If you are using MySQL 4 and prior, you may come across a problem with max_allowed_packet size.
ERROR [sf.hibernate.util.JDBCExceptionReporter] logExceptions Packet for query is too large (1259485 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
To resolve these problems, you need to either specify or increase the value for max_allowed_packet. Make sure to set the packed size when starting the server, not the client.
Prior MySQL 4.0, use this syntax instead:
shell> mysqld --set-variable=max_allowed_packet=16M
In MySQL 3.23, the largest possible packet is 16MB, due to limits in the client/server protocol. In MySQL 4.0.1 and up, the limit is 1GB.
From MySQL 4.0, use this syntax
shell> mysqld --max_allowed_packet=32M
For more information, please refer to MySQL manual:
http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html
If you use MySQL Administrator, you can set this parameter from the Startup Variables section on Windows OS (Options on MacOS) > Advanced Networking subsection. You can set max_allowed_packet option to 16 (MB), which in the screenshot below, is currently set at 1 MB.
Screenshot: Setting the max_allowed_packet option via the MySQL Administrator
