Wednesday 7 November 2012

Handle MySQL max_allowed_packet error

Problem:

My web application keeps giving the error "#1153 – Got a packet bigger than ‘max_allowed_packet’ bytes" and fails.

What's happening:

This error occurs when a statement to the MySQL server exceeds a certain size. For instance, if the max_allowed_packet is set to 2M but a statement attempts to INSERT a 5MB BLOB, the query will fail.

Solution:

There are two solutions around this problem, so long as you have sufficient privileges to access the configuration of your MySQL server, and are comfortable with either SSH or editing configuration files. Additionally, you should know how to safely restart your MySQL server.

  1. The first solution is to log into your server via SSH, then connect to your MySQL. Assuming you want the maximum packet size to be 16MB, when logged into MySQL type the following command into the prompt: mysqld --max_allowed_packet=16M
  2. The other solution is to find your MySQL configuration file, usually named my.cnf (though if not, you can easily Google how to find the location of this file on your current OS version). Assuming again that you want a max packet size of 16M, inside this file under the section [mysqld], add or change the following parameter to max_allowed_packet=16M

Once this is completed, you may have to restart your MySQL server to see the change. Note that this is a global change and every script that accesses the database on this server will now, by default, have a maximum allowed packet size to what you set it to.

1 comment: