I ran into this error when doing a very large MySQL import from a dumpfile.
$ mysql dbname < /tmp/dump.sql
ERROR 1153 (08S01) at line 9424: Got a packet bigger than 'max_allowed_packet' bytes
Here are the steps I used to fix this.
Allow large packets in the client#
You can change the maximum allowed packet size when running the mysql command as a parameter. This increases the maximum packets that the client will send to the server.
$ mysql --max_allowed_packet=100M dbname < /tmp/dump.sql
That’s part one, this covers your client-side action.
Allow large packets on the server#
Now, the server also needs to accept the bigger packets. There’s generally two ways of doing this.
Get the current max_allowed_packet size#
You can see what the currently configured maximum packet size is, by querying for the variable on the MySQL server. To do so, log in to your MySQL shell with root privileges.
$ mysql -uroot
mysql> SELECT @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
| 16777216 |
+----------------------+
1 row in set (0.00 sec)
To get a human-readable version, in MB, try this:
mysql> SELECT @@max_allowed_packet / 1024 / 1024;
+------------------------------------+
| @@max_allowed_packet / 1024 / 1024 |
+------------------------------------+
| 16.00000000 |
+------------------------------------+
1 row in set (0.01 sec)
On MySQL 5.7 the server default was 16MB (16777216 bytes), as shown above. As of MySQL 8.0 the server default is 64MB. The mysql client’s own default is still 16MB.
Temporarily increasing the packet size#
You can change this max allowed packet parameter temporarily, for the running MySQL instance. To do so, log into the MySQL service as a root user and run these commands:
$ mysql -uroot
mysql> SET GLOBAL net_buffer_length=1000000;
mysql> SET GLOBAL max_allowed_packet=1000000000;
This sets the max allowed packet to 1000000000 bytes, or roughly 950MB (close to 1GB). Pick a value that fits your dump; 100MB is 104857600.
If you restart your MySQL service at any time, the settings are reverted back to their default or whatever is configured in your my.cnf.
Permanently increase the packet size in my.cnf#
To set the desired values whenever your MySQL server restarts, you need to update your my.cnf config file.
Edit the file /etc/mysql/my.cnf (or wherever your my.cnf is) and look for the [mysqld] section.
[mysqld]
# ...
# There might be other config parameters in here
# ...
max_allowed_packet = 100M
Once the config has been changed, restart the MySQL server to apply these settings.