How to configure the max_allowed_packet parameter in mysql (to avoid writing or updating big data)

MySQL limits the size of data packets received by the Server according to the configuration file. Sometimes large inserts and updates are limited by the max_allowed_packet parameter, leading to write or update failure.

View the current configuration:
Copy codeThe Code is as follows: show VARIABLES like '% max_allowed_packet % ';
The result is as follows:
Copy codeThe Code is as follows: + ------------------ + --------- +
| Variable_name | Value |
+ -------------------- + --------- +
| Max_allowed_packet| 1048576 |
+ -------------------- + --------- +
The preceding description shows the current configuration: 1 M.

Modification Method

1. modify the configuration file

You can edit my. cnf (my. ini in windows) and modify it in the [mysqld] section or mysql server configuration section.
Copy codeThe Code is as follows: max_allowed_packet = 20 M
If you cannot find my. cnf, you can use
Copy codeThe Code is as follows: mysql -- help | grep my. cnf
Find the my. cnf file.
In linux, the file is in/etc.

2. modify it in the mysql Command Line

Run the following command 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 % ';
Check whether max_allowed_packet is successfully edited.

Note: if this value is set too small, writing to the database fails if a single record exceeds the limit, and writing to subsequent records also fails.