Mysqldump failure during automatic backups (max_allowed_packet)

Bdzzld

Well-Known Member
Apr 3, 2004
410
5
168
Hi,

The last few weeks our automatic backups respond with a failure at database level for a specific database/user:

Code:
[2018-09-18 02:44:43 +0200] username_master: mysqldump failed -- database may be corrupt
The logs show the following:

Code:
username_master: mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `TableName` at row: 12
As fas as I can tell, this can not be changed by the server administrator and is something set by cPanel.

Please advise.
 

cPanelLauren

Product Owner
Staff member
Nov 14, 2017
13,297
1,259
313
Houston
Hi @Bdzzld

This is set in mysql's configuration file. You can see it by running the following:

Code:
grep packet /etc/my.cnf
If you increase this value, restart mysql do you still encounter the same error? These are the MySQL defaults - not something set by cPanel necessarily.

Thanks!
 

Bdzzld

Well-Known Member
Apr 3, 2004
410
5
168
@cPanelLauren: All our cPanel servers have the following configured in /etc/my.cnf regarding mysqldump:

Code:
[mysqldump]
quick
max_allowed_packet=16M
The specific account is several years old and was back upped before without problems.
The problem started to appear as of this month (the 4th to be exactly).
 

cPanelLauren

Product Owner
Staff member
Nov 14, 2017
13,297
1,259
313
Houston
hi @Bdzzld

This is the setting for my server under [mysqld] and not specific to mysqldump running MariaDB 10.2:

Code:
# grep max_allowed /etc/my.cnf
max_allowed_packet=268435456
Code:
MariaDB [(none)]> show variables like '%max_all%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 268435456  |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)
I can confirm that these values have not been modified since I upgraded the server to MariaDB. It simply seems that the issue is the value is not high enough for your databases.

The default for this limitation is 16MB per MySQL :: MySQL 5.7 Reference Manual :: B.5.2.9 Packet Too Large and Server System Variables