Mysql max_allowed_packet query and modification Problems

When I use SQLyog to export MySQL Data today, when the data volume is large, an Error Code is displayed: 1153 Got a packet bigger than \ 'max _ allowed_packet, later, Baidu gave a solution, and I will share it with you.

Error Code: 1153 Got a packet bigger than 'max _ allowed_packet 'bytes Error message.
I didn't pay attention to it at first. I had a bunch of prompts before I saw this sentence.
When using SQLyog to export MySQL Data, when the data volume is large, the export will not be wrong, but the import will produce an error. If the SQL statement is executed separately, the Error Code: 1153 Got a packet bigger than 'max _ allowed_packet 'bytes is returned.

To solve this problem, add max_allowed_packet = 16 m at the end of my. ini (set as needed ).

Here we will introduce the specific solution

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, resulting in failure.

View current configuration

Show VARIABLES like '% max_allowed_packet %'; the result is:

+ -------------------- + --------- + | Variable_name | Value | + -------------------- + --------- + | max_allowed_packet | 1048576 | + ---------------------- + --------- +

The preceding description shows the current configuration: 1 M.

 
Modification Method

1) method 1

You can edit my. cnf (my. ini in windows) and modify it in the [mysqld] section or mysql server configuration section.

Max_allowed_packet = 20 m. If you cannot find my. cnf, you can use

Mysql -- help | grep my. cnf to find the my. cnf file.

2) method 2

Enter mysql server

Run in mysql Command Line

Set global max_allowed_packet = 2*1024*1024*10, close the mysql server link, and then enter.

Show VARIABLES like '% max_allowed_packet %'; check whether max_allowed_packet is successfully edited.

Note: No matter what operating system you are in, we need to restart the mysql server to take effect after the settings.