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.