In MySQL, a communication packet or packet refers to a single SQL statement sent to a MySQL server. It also refers to an individual row that has replied to the client or binary log from MySQL Server.
This means that if either the client or server obtains a packet larger than the set max_allow_packet parameter, it returns an “ERR_NET_PACKET_TOO_LARGE” error. The server or client can then close the connection.
This article will demonstrate how you can change the maximum packet size in both MySQL client and MySQL server.
What is max_allow_packet?
The max_allow_packet is a session variable determining the number of bytes sent or received in a single communication packet.
Check the Current Max Allowed Packet Size
We can check the maximum allowed packet size by getting the values of MySQL variables.
An example query is shown below:
The query above should return the maximum allowed packet size in bytes as:
|------------------|--------|
|max_allowed_packet|67108864|
Our example shows that the current maximum allowed packet size is 67108864 bytes, or approximately 67MB.
Remember that although MySQL allows us to change the maximum allowed packet size value, the largest possible packet size is 1GB.
Change the max_allowed_packet in MySQL
Method 1 – Client and Server Side Configuration
We must change both the client and server-side configurations to increase or decrease the value of the max_allowed packet size.
This stems from MySQL’s use of standalone values for both mysqld and the client. Hence, the value you get from the SHOW VARIABLES LIKE ‘max_allowed_packet’ command refers to the max value on the server side.
To set the max value on the client side, we can run the command:
The command above sets the packet size to 100MB on the client side.
Note that the default max packet allowed on the client side is 16MB.
On the server side, modify the packet size as:
NOTE that sthe default max packet allowed on the server side is 64MB.
Method 2 – Server Side Without Restart
If you have SUPER privileges on the server, you can change the value of the max packet using the SET GLOBAL command as:
This increases the max_allowed_packget value to 128MB without restarting the server. However, it does require SUPER privileges.
Method 3 – Editing the Configuration File
Another method you can use to increase or decrease the maximum allowed packet is editing the configuration file.
In the MySQL installation directory, locate the my.ini (Windows) file. By default, the file is located in C:\ProgramData\MySQL\MySQL Server 8.0
Edit the file and change the max_allowed_packet value to your target size.
For example, we can set the packet size to 128MB as shown:
Save and close the file. Restart the MySQL service to reload the new configuration.
In Linux, you need to edit the mysqld.conf file. In most cases, this file is located in /etc/MySQL/mysqld.conf.d/mysqld.cnf
Similarly, edit and change the max_allowed_packet size to the desired value. Similarly, restart the service to reload the changes.
Conclusion
In this article, you learned what MySQL max_allowed_variable is, how to check the maximum allowed packet size, and various methods of increasing or decreasing the packet size.
