Variable;max_allowed_packet
Configuration;Supported
Scope;Session, Global
Status;Dynamic
Data Type; BIGINT UNSIGNED
Default Value;16777216
Minimum Value;1024
Maximum Value;1073741824
The max_allowed_packet is the maximum size of a MySQL network protocol that can be read or created by the server. The parameter comes with default, minimum, and maximum values that you can change according to your requirements.
max_allowed_packet – Usage
There are several ways of changing the max_allowed_packet value. It is primarily set to the default value, which is 16MB.
To change it, follow this: $> mysql --max_allowed_packet=32M
This will set the client's value to 32 MB. You can change the value according to your requirements.
Changing the server value is also important, especially when you are dealing with large queries. Usually, the server's default value is 16 MB. Let's double the server's value, as we did to the client's program: $> mysqld --max_allowed_packet=32M
Lastly, you can also set the value using an configuration file. Here's how it is done: [mysqld] max_allowed_packet=32M
This will change the server size to 32 MB, thus enabling it to transfer larger queries without any error.
When is max_allowed_packet Changed?
So, what's the need for changing max_allowed_packet? When a client server receives larger packet bytes, an error occurs. The connection faces ER_NET_PACKET_TOO_LARGE error and closes instantly.
Another error can be Lost Connection to Server During Query Error. It has the same reason, i.e., a large communication packet.
This happens when the set value for max_allowed_packet is less than the size of uploading files. To perform the transaction successfully, you would have to change the value in both client and server.
Ready to optimize your MySQL performance? Try Releem today for FREE! No credit card required.