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.

MySQL Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes

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

Error Number1153
Error SymbolER_NET_PACKET_TOO_LARGE
SQLSTATE08S01
MessageGot a packet bigger than ‘max_allowed_packet‘ bytes
DependencySystem Variable “max_allowed_packet
ReasonPacket Too Large
Error TypeServer-Side Error

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

System Variable Namemax_allowed_packet
Command Line Parameter–max-allowed-packet=…
ScopeGlobal, Session
TypeInteger
Default Value67,108,864This value is represented in Byte.
67,108,864 Byte = 64MB
Minimum Value1,024This value is represented in Byte.
1,024 Byte = 1MB
Maximum Value1,073,741,824This value is represented in Byte.
1,073,741,824 Byte = 1024 MB = 1GB
Block Size1,024This value is represented in Byte.
1,024 Byte = 1MB

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:

Error NumberError SymbolSQLSTATEMessageError Type
1162ER_TOO_LONG_STRING42000Result string is longer than ‘max_allowed_packet’ bytesServer-Side
1301ER_WARN_ALLOWED_PACKET_OVERFLOWEDHY000Result of %s() was larger than max_allowed_packet (%ld) – truncatedServer-Side
3957ER_CLONE_NETWORK_PACKETHY000Clone needs max_allowed_packet value to be %u or more. Current value is %uServer-Side
MY-010085ER_WASTEFUL_NET_BUFFER_SIZEHY000net_buffer_length (%lu) is set to be larger than max_allowed_packet (%lu). Please rectify.Server-Side
MY-013125ER_SERVER_NET_PACKET_TOO_LARGEHY000Got a packet bigger than ‘max_allowed_packet’ bytesServer-Side
2006CR_SERVER_GONE_ERRORMySQL server has gone awayClient-Side

Leave a Comment


The reCAPTCHA verification period has expired. Please reload the page.