How to configure Max_allowed_packet parameters in MySQL (avoid big Data writes or failed updates)

This article mainly introduces the configuration method of the Max_allowed_packet parameter in MySQL, and the method of viewing the current value of the Max_allowed_packet parameter, the friend who needs can refer to the following

MySQL restricts the size of packets accepted by the server according to the configuration file. Sometimes large inserts and updates are limited by the Max_allowed_packet parameter, causing the write or update to fail.

View current configuration:

Copy CodeThe code is as follows: Show VARIABLES like '%max_allowed_packet% ';

The results shown are:

Copy CodeThe code is as follows: +--------------------+---------+
| variable_name | Value |
+--------------------+---------+
| Max_allowed_packet | 1048576 |
+--------------------+---------+

The above description is currently configured to: 1M

Modify method

1. Modify the configuration file

You can edit the my.cnf to modify (under Windows My.ini) and modify it in the [Mysqld] section or in the MySQL server configuration section.

Copy CodeThe code is as follows: Max_allowed_packet = 20M

If you can't find the MY.CNF, you can pass

Copy CodeThe code is as follows: MySQL--help | grep my.cnf

To find the my.cnf file.
Under Linux the file is under/etc/.

2. Modify in MySQL command line

Run in the MySQL command line:

Copy CodeThe code is as follows: Set global Max_allowed_packet = 2*1024*1024*10

Then exit the command line, restart the MySQL service, and then enter.

Copy CodeThe code is as follows: Show VARIABLES like '%max_allowed_packet% ';

See if the next Max_allowed_packet is edited successfully

Note: This value is set too high to cause a single record to exceed the limit after the write to the database fails, and subsequent record writes will fail.

How to configure Max_allowed_packet parameters in MySQL (avoid big Data writes or failed updates)