Problems and Solutions of MySQL Packet for query is too large

  • 2021-10-25 08:09:40
  • OfStack

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 limits the size of packets that Server accepts depending on the configuration file. Sometimes the size of the 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 side:


show VARIABLES like '%max_allowed_packet%'; 

The results are as follows:


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

Modification method:

1. Modify the configuration file
You can edit my. cnf to modify my. ini under windows, in the [mysqld] section or in the server configuration section of mysql.

max_allowed_packet = 20M

If my cannot be found. cnf can be found via

mysql --help | grep my.cnf

Look for the my. cnf file.

Under linux this file is under/etc/.

2. Modify on the mysql command line


set global max_allowed_packet = 2*1024*1024*10  
// The value here is the number of bytes, and here it is changed to 20M You need to re-log in to the client to work  

Note: After the client modifies with the command, it can only work temporarily. After restarting MYSQL, the original value will be restored.

mysql under Linux Report Packet for query is too large (1040 > 1024) Wrong solution

Before the project 1 straight normal operation, these days suddenly 1 straight prompt query error, read the log found prompt Packet for query is too large (1040 > 1024). You can change this value on the server by setting the max_allowed_packet 'variable. Error. Baidu worked out the solution and recorded the solution process.

1. Reasons

The packet returned by querying the database is too large and exceeds the default value.
Log in to mysql, enter 1 in the console to query the default max_allowed_packet value, and find that it is only 1024


show VARIABLES like '%max_allowed_packet%'; 

2. Modify the default maximum allowable package size

2.1 Mode 1: Command Mode

(1). Set max_allowed_packet to 20M by entering the following command under the mysql console

set global max_allowed_packet = 20*1024*1024;

(2). Exit mysql, restart mysql service, and then log in to myql to inquire whether max_allowed_packet has been modified successfully

show VARIABLES like '%max_allowed_packet%';

2.2 Mode 2: Modify configuration file my. cnf Mode
(1). Edit my. cnf by entering the following command under the mysql console

sudo vi /etc/mysql/my.cnf

(2). Add under [mysqId]

max_allowed_packet = 20M

2.2 Exit edit mode and restart mysql as shown in (2) of 2.1

3. Notes

After configuring the query max_allowed_packet, it is found that it is 16777216, which is actually 16*1024*1024, which is not the expected 20M, so it may be that the server memory capacity is not enough, because java takes up a lot, so mysql may automatically reset the parameters. Therefore, in some cases, you may have changed the parameters of max_allowed_packet at that time, and mysql automatically reset the parameters to the default 1024 after a period of time, and the same error occurred again.


Related articles: