Increase max_allowed_packet Size in MySQL

When the MySQL server receives a database query with a data 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. It would be best 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 the 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. Side note: In the latest version, this is no issue anymore in WP STAGING, as this plugin will throttle and execute the number of database queries dynamically depending on the site of your server’s maximum allowed packet size.

You have two options to change the MySQL max_allowed_packet size:

How 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.
  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
  4. Restart the MySQL Server.

How 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;

Related Articles

Updated on February 21, 2023