Mysqldump failure during automatic backups (max_allowed_packet)

[Bdzzld]

Hi,

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

[2018-09-18 02:44:43 +0200] username_master: mysqldump failed -- database may be corrupt

The logs show the following:

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]

Hi @Bdzzld

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

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]

@cPanelLauren: All our cPanel servers have the following configured in /etc/my.cnf regarding mysqldump:

[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]

hi @Bdzzld

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

# grep max_allowed /etc/my.cnf
max_allowed_packet=268435456

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