Exceeds Max Allowed Packet for MySQL
Symptoms
When connecting to MySQL, JIRA comes across an exception with exceeding max_allowed_packet size. It's normally found in XML backup importing process:
java.lang.IllegalArgumentException:
Packet is larger than max_allowed_packet from server configuration of **** bytes.
The following error might appear in the atlassian-jira.log:
2015-06-09 11:50:07,836 JIRA-Import-Thread-5 ERROR admin 709x143x1 1d79aun 0:0:0:0:0:0:0:1 /secure/admin/XmlRestore.jspa [jira.action.admin.OfbizImportHandler] Exception importing entity: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Action][updateauthor,username][issue,18922][author,username][created,2015-04-01 16:47:51.363][id,1234][type,comment][body,|---------------------------------------|
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
A lot of text goes here
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
][updated,2015-04-01 16:47:51.363] (SQL Exception while executing the following:INSERT INTO jiraaction (ID, issueid, AUTHOR, actiontype, actionlevel, rolelevel, actionbody, CREATED, UPDATEAUTHOR, UPDATED, actionnum) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Packet for query is too large (21064857 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.))
at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:214)
at org.ofbiz.core.entity.GenericDAO.insert(GenericDAO.java:179)
at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:554)
at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:540)
at org.ofbiz.core.entity.GenericValue.create(GenericValue.java:98)
at com.atlassian.jira.action.admin.OfbizImportHandler.createWithDeadlockRetry(OfbizImportHandler.java:692)
at com.atlassian.jira.action.admin.OfbizImportHandler$1.run(OfbizImportHandler.java:661)
at com.atlassian.jira.util.concurrent.BoundedExecutor$1.run(BoundedExecutor.java:58)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:INSERT INTO jiraaction (ID, issueid, AUTHOR, actiontype, actionlevel, rolelevel, actionbody, CREATED, UPDATEAUTHOR, UPDATED, actionnum) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Packet for query is too large (21064857 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.)
at org.ofbiz.core.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:650)
at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:206)
... 11 more
Caused by: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (21064857 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3592)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2141)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2077)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2062)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.ofbiz.core.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:640)
... 12 more
Cause
A communication packet or a single row that is sent to MySQL server exceeds DB max_allowed_packet restriction (1M by default).
Resolution
Command Line
shell> mysql --max_allowed_packet=32M
MySQL Administrator
If you use MySQL Administrator, you can set this parameter from the Startup Variables section on Windows OS (Options on MacOS) >> Advanced Networking subsection. Set max_allowed_packet option to 32 (MB), which in the screenshot below, is currently set at 1 MB.