max_allowed_packet Settings for Mysql

max_allowed_packet is a setting parameter in Mysql, which is used to set the size of the accepted packet. Depending on the situation, the default value may be 1M or 4M. For example, in the case of 4M, the size of this value is: 4*1024*1024=4194304

Phenomenon

Such as: "Package for query is too large (xxxxxxxx > 4194304). You can change this value on the server by setting the max_allowed_package variable ", the error itself clearly indicates the corresponding way.

Confirm max_allowed_package

You can confirm the current setting value in the following ways

mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
|       4194304 |
+----------------------+
1 row in set (0.00 sec)
mysql>

Or

mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name   | Value  |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.00 sec)
mysql>

Modify

You can use the set command to modify it, but it is only temporary and will be lost after restarting. You can also directly modify the mysql setting file and restart the mysql service, which can guarantee the setting permanently.

The modified files will vary depending on the installation of mysql. The normal installation may modify my. cnf. Here, the official image of mysql is used, and the modified files should be:/etc/mysql/mysql. conf. d/mysqld. cnf

Modification: Add the following settings to this file
max_allowed_packet = 256M

Before modification

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

After modification

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_allowed_packet = 256M

Restart the container and confirm

mysql> show variables like '%max_allowed_pack%';
+--------------------------+------------+
| Variable_name      | Value   |
+--------------------------+------------+
| max_allowed_packet    | 268435456 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.01 sec)
mysql>

So you can see that it has been successfully set to 256M (268435456)

liumiaocn:~ liumiao$ echo "256*1024*1024" |bc
268435456
liumiaocn:~ liumiao$

Summarize