MySQL max_allowed_packet setting

max_allowed_packet is a parameter in MySQL that is used to set the size of the accepted packet. Depending on the situation, its default value may be 1M or 4M. For example, if it is 4M, the value is: 4 * 1024 * 1024 = 4194304

Phenomenon

When a message 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" appears in the log, the error itself clearly indicates the corresponding method.

Confirm max_allowed_package

Use the following method to confirm the current setting value

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>

Revise

You can use the set command to modify it, but it is only a temporary modification and will be lost after restarting. You can also directly modify the MySQL configuration file and restart the MySQL service to permanently ensure the settings.

The modified files will be different depending on the installation of MySQL. The normal installation method may modify my.cnf. Here we use the official image of MySQL, and the modified file should be: /etc/mysql/mysql.conf.d/mysqld.cnf

Modification content: 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

[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 we can see that it has been successfully set to 256M (268435456)

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

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links