MySQL Packets larger than Max_allowed_packet is not allowed

MySQL Packets larger than Max_allowed_packet is not allowed

A system parameter for MySQL: Max_allowed_packet, whose default value is 1048576 (1M),
Enquiry: Show VARIABLES like '%max_allowed_packet% ';
Modify the value of this variable: "Max_allowed_packet = 1M" In the [Mysqld] section of the My.ini file in the MySQL installation directory, such as change to 4M (if there is no line of content, add one row), save, restart the MySQL service. It is now possible to load files larger than 1M.

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

View current configuration

Show VARIABLES like '%max_allowed_packet% ';

The results shown are:

+--------------------+---------+

| variable_name | Value |

+--------------------+---------+

| Max_allowed_packet | 1048576 |

+--------------------+---------+

The above description is currently configured to: 1M

Modify method

1) Workaround

Modify the MySQL installation directory My.ini or My-default.ini to modify, add in the [Mysqld] Segment

Max_allowed_packet = 20M

The contents of a text file are typically imported into a database through MySQL's "Load data local infile" statement.
This will be fast, but today we find that if the size of the text exceeds 1M, an exception occurs: "Packets larger than Max_allowed_packet is not allowed"

A system parameter for MySQL: Max_allowed_packet, whose default value is 1048576 (1M),
Enquiry: Show VARIABLES like '%max_allowed_packet% ';
Modify the value of this variable: "Max_allowed_packet = 1M" In the [Mysqld] section of the My.ini file in the MySQL installation directory, such as change to 4M (if there is no line of content, add one row), save, restart the MySQL service. It is now possible to load files larger than 1M.

MySQL max_allowed_packet query and modification

MySQL Windows

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

View current configuration

Show VARIABLES like '%max_allowed_packet% ';

The results shown are:

+--------------------+---------+

| variable_name | Value |

+--------------------+---------+

| Max_allowed_packet | 1048576 |

+--------------------+---------+

The above description is currently configured to: 1M

Modify method

1) Method 1

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.

Max_allowed_packet = 20M

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

MySQL--help | grep my.cnf

To find the my.cnf file.

2) Method 2

(very compromise, very tangled approach)

Go to MySQL server

Run in the MySQL command line

Set Global Max_allowed_packet = 2*1024*1024*10

Then shut down this MySQL server link and then enter.

Show VARIABLES like '%max_allowed_packet% ';

See if the next Max_allowed_packet is edited successfully

MySQL Packets larger than Max_allowed_packet is not allowed