MySQL: Error 1153 – Got a packet bigger than ‘max_allowed_packet’ bytes

Got this error while performing some data transfer from one MySQL server to another.

Error Number: 1153

Error Message: Got a packet bigger than ‘max_allowed_packet’ bytes.

Let’s take a look at how we can get rid of this error and solve the problem.

Error Details

As mentioned above, while receiving data from the client, the MySQL server allows a certain number of packets/bytes in a request. That max allowed packet is defined/set in the system variable “max_allowed_packet“.

If the data sent to the MySQL server is larger than the “max_allowed_packet” then the request will fail with the message “Got a packet bigger than ‘max_allowed_packet’ bytes“.

The solution to this problem is to increase the value of the system variable “max_allowed_packet”.

Before changing the value we need to have a clear idea about the limitations of this system variable. Let’s take a look at the criteria of this system variable first.

System Variable: max_allowed_packet

More details about this system variable can be found here: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_allowed_packet

Now we have a clear idea about the system variable, so let’s proceed to the solutions.

In all of these solutions, we will discuss different ways, how to set the value of max_allowed_packet.

Temporary Solution #1: Change Value of Global Variable

You can change the global setting using MySQL query. Say, you want to change the value to 256MB then execute the following query:

SET GLOBAL max_allowed_packet = 256M;

Or, if you prefer to set the value in Byte then use the value 268,435,456 (256 * 1024 * 1024 = 268,435,456). Use the query like below:

SET GLOBAL max_allowed_packet = 268435456;

Or, just write it like the below:

SET GLOBAL max_allowed_packet = 256 * 1024 * 1024;

Note: when the server is restarted this value will be changed to the default value, or according to the value set in the MySQL config file.

Temporary Solution #2: Start Server With New Value

See the command line parameter –max_allowed_packet in the above table, which will provide a solution to change this variable.

When you start the MySQL server, start it with this parameter and set the value, which will change the value of this system variable. Say, you want to change the value to 256MB, then use the following command below to start the server:

mysqld --max_allowed_packet=256M

Or if you want to set it in byte then use the value 268,435,456 (256 * 1024 * 1024 = 268,435,456) in the command like below:

mysqld --max_allowed_packet=268435456

Note: when the server is restarted this value will be changed to the default value, or according to the value set in the MySQL config file.

Permanent Solution: Change MySQL Config File

The permanent solution is to set the value in the MySQL config file – my.cnf or my.ini. Add the following line under the [mysqld] section, like below:

[mysqld]
max_allowed_packet=256M

Or if you want to set the value in Byte then use:

[mysqld]
max_allowed_packet=268435456

Note: MySQL server needs to be restarted after this change is made in the config file. The new value will be used by the server after the restart.

Note: This setting will remain effective permanently.

If you need to change the value of this system variable for just a few operations temporarily, that you are performing on the server, then use the Temporary Solution #1 mentioned above. It is an easy and very quick solution.

But if you see that, your application is executing queries that have a large request size, then use the permanent solution, by changing the config file.

Related Errors

You may also face a few other MySQL errors due to the same reason, as those errors also have a dependency on the same system variable “max_allowed_packet“. So the same fix mentioned above will work for these MySQL errors too. Here is the list of related errors to MySQL Error: 1153: