Increase max_allowed_packet size in MySQL

When a MySQL client or the MySQL server gets a packet larger than the max_allowed_packet bytes, it issues a “Packet too large” error and closes the connection. By default, this value is configured low. You need to increase this value for large queries/messages. Set the value of this parameter to the size of the biggest table message you anticipate.

For example, if WP STAGING backup restore fails because your database has a too-small packet size, it will show you the size of that query, and you can adjust the max_allowed_packet size according to that value.

You have two options to change the MySQL max_allowed_packet size:

To set max_allowed_packet permanently:

  1. Open the “my.ini” file under the MySQL server installation directory.
  2. Search for the “max_allowed_packet” parameter. If the file does not have it, add the parameter to the file.
  3. Set the value as needed. To set the value to 1GB, enter the value as one of the following: max_allowed_packet=1073741824 or
    max_allowed_packet=1G
  4. Restart the MySQL Server.

To set max_allowed_packet temporary:

The max_allowed_packet variable can be set globally by running a query.

However, if you do not change it in the my.ini file, the value will always reset when the server restarts, even if you set it globally.

To change the max allowed packet for everyone to 1GB until the server restarts:

SET GLOBAL max_allowed_packet=1073741824;

Updated on February 14, 2022