MySQL Packet for query is too large problem and solution

Problem description:

Error message:

Caused by: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1354 > 1024). You can change this value on the server
by setting the max_allowed_packet' variable

MySQL will limit the size of the data packet accepted by the Server according to the configuration file. Sometimes the size of the data packet during insert, update or query is limited by the max_allowed_packet parameter, causing the operation to fail.

View the max_allowed_packet parameter:

Execute on the client:

show VARIABLES like '%max_allowed_packet%';

The results are as follows:

+--------------------+------------+ 
| Variable_name | Value | 
+--------------------+-------------------+ 
| max_allowed_packet | 1024 | 
+--------------------+-------------------+

Modification method:

1. To modify the configuration file, you can edit my.cnf (my.ini under Windows) and modify it in the [mysqld] section or the mysql server configuration section.

max_allowed_packet = 20M

If my.cnf cannot be found, you can

mysql --help | grep my.cnf

Go find the my.cnf file.

In Linux, the file is located in /etc/.

2. Modify in the mysql command line

set global max_allowed_packet = 2*1024*1024*10  
//The value here is the number of bytes. If it is changed to 20M, you need to log in to the client again to take effect.

Note: After the client uses the command to modify, it will only take effect temporarily. After restarting MYSQL, the original value will be restored.

Solution to the error "Packet for query is too large (1040 > 1024)" reported by MySQL under Linux

The project had been running normally before, but suddenly in the past few days it kept prompting query errors. I looked at the logs and found that it prompted Packet for query is too large (1040 > 1024). You can change this value on the server by setting the max_allowed_packet' variable. error. I searched Baidu for the solution and recorded the solution process.

1. Reasons

The data packet returned by the query database is too large and exceeds the default value.
Log in to mysql and enter the following command in the console to query the default max_allowed_packet value. It is only 1024.

show VARIABLES like '%max_allowed_packet%';

2. Modify the default maximum allowed packet size

2.1 Method 1: Command method

(1). Enter the following command in the MySQL console to set max_allowed_packet to 20M

set global max_allowed_packet = 20*1024*1024;

(2) Exit MySQL, restart the MySQL service, and then log in to MySQL to check whether max_allowed_packet has been modified successfully.

show VARIABLES like '%max_allowed_packet%';

2.2 Method 2: Modify the configuration file my.cnf
(1) Enter the following command in the mysql console to edit my.cnf

sudo vi /etc/mysql/my.cnf

(2). Add below [mysqId]

max_allowed_packet = 20M

2.2 Exit edit mode and restart MySQL. Follow the steps in 2.1 (2)

3. Note

After configuration, I queried max_allowed_packet and found that it was 16777216, which is actually 16*1024*1024, not the expected 20M. So I checked again and found that the server memory capacity might be insufficient, because Java occupies a lot of it, so MySQL might automatically reset the parameters. So in some cases, it may be that after you changed the max_allowed_packet parameter, MySQL automatically reset the parameter to the default 1024 after a while, and the same error occurred again.