Configuration method for Max_allowed_packet parameters in Mysql (avoid large data writes or update failures) _mysql

Depending on the profile, MySQL restricts the packet size that the server accepts. Sometimes large inserts and updates are limited by the Max_allowed_packet parameter, causing the write or update to fail.

View current configuration:

Copy Code code as follows:

Show VARIABLES like '%max_allowed_packet% ';

The results displayed are:

Copy Code code as follows:

+--------------------+---------+
| variable_name | Value |
+--------------------+---------+
| Max_allowed_packet | 1048576 |
+--------------------+---------+

The above description is currently configured as: 1M

Modify method

1, modify the configuration file

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

Copy Code code as follows:

Max_allowed_packet = 20M

If you can't find my.cnf, you can pass the

Copy Code code as follows:

MySQL--help | grep my.cnf

To find my.cnf files.
Linux under this file under/etc/.

2, in the MySQL command line to modify

Running on the MySQL command line:

Copy Code code as follows:

Set Global Max_allowed_packet = 2*1024*1024*10

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

Copy Code code as follows:

Show VARIABLES like '%max_allowed_packet% ';

See if Max_allowed_packet edits successfully

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