Impacts of max_allowed_packet size problems on your MySQL database

We recently run into some troubles with max_allowed_packet size problems during backups with the FromDual Backup/Recovery Manager and thus I investigated a bit more in the symptoms of such problems.

Read more about: max_allowed_packet.

A general rule for max_allowed_packet size to avoid problems is: All clients and the server should have set the same value for max_allowed_packet size!

I prepared some data for the test which looked as follows:

mysql> SELECT id, LEFT(data, 30), LENGTH(data), ts FROM test;
+----+--------------------------------+--------------+------+
| id | left(data, 30)                 | length(data) | ts   |
+----+--------------------------------+--------------+------+
|  1 | Anhang                         |            6 | NULL |
|  2 | Anhang                         |            6 | NULL |
|  3 | Anhangblablablablablablablabla |      2400006 | NULL |
|  4 | Anhang                         |            6 | NULL |
+----+--------------------------------+--------------+------+

Max_packet_size was set to a too small value then:

mysql> SHOW GLOBAL VARIABLES WHERE variable_name = 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+

The first test was to retrieve the too big row:

mysql> SELECT * FROM test WHERE id = 3;
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes

mysql> SELECT CURRENT_USER();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: test

We got an error message AND we were disconnected from the server. This is indicated with the message MySQL server has gone away which is basically wrong. We were disconnected and not the server has died or similar in this case.

A further symptom is that we get an entry in the MySQL error log about this incident:

[Warning] Aborted connection 3 to db: 'test' user: 'root' host: 'localhost' (Got an error writing communication packets)

So watching carefully such error messages in your MySQL error log with the script check_error_log_mysql.pl from our Nagios/Icinga plugins would be a good idea...

The mysqldump utility basically does the same as a SELECT command so I tried this out and got the same error:

shell> mysqldump -u root test > /tmp/test_dump.sql
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `test` at row: 2

And again we get an error message in the error log! This is also a good indicator to see if your backup, made with mysqldump failed in this case.

To get a proper dump we have to configure the mysqldump utility properly:

shell> mysqldump --max-allowed-packet=5000000 -u root test > /tmp/test_dump.sql

After the backup we tried to restore the data:

shell> mysql -u root test < /tmp/test_dump.sql
ERROR 2006 (HY000) at line 40: MySQL server has gone away

Again we got an error on the command line and in the MySQL error log:

[Warning] Aborted connection 11 to db: 'test' user: 'root' host: 'localhost' (Got a packet bigger than 'max_allowed_packet' bytes)

and further the data are only partially loaded:

mysql> SELECT * FROM test;
+----+--------+------+
| id | data   | ts   |
+----+--------+------+
|  1 | Angang | NULL |
|  2 | Angang | NULL |
+----+--------+------+

Another symptom we can see here is that the MySQL status aborted_clients is increased in all 3 situation:

mysql> SHOW GLOBAL STATUS WHERE variable_name = 'aborted_clients';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Aborted_clients | 10    |
+-----------------+-------+

One positive aspect is that with MySQL 5.7.5 the first 2 symptoms do not appear any more...