MySQL: ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes

Tired of the privacy invasion of browsers? Worried about the risk of seeing ads everywhere? Give the Brave Browser a try. All Chrome extensions are compatible. None of the telemetry. Auto-blocks ads and still support content creators like me.

Download the Brave browser now »

Profile image of Mattias Geniar

Mattias Geniar, May 15, 2020

Follow me on Twitter as @mattiasgeniar

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)

The default value is 16MB, or 16777216 bytes.

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, or 100MB.

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.



Want to subscribe to the cron.weekly newsletter?

I write a weekly-ish newsletter on Linux, open source & webdevelopment called cron.weekly.

It features the latest news, guides & tutorials and new open source projects. You can sign up via email below.

No spam. Just some good, practical Linux & open source content.