Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

Mysqldump failure during automatic backups (max_allowed_packet)

Discussion in 'Database Discussion' started by Bdzzld, Sep 18, 2018.

  1. Bdzzld

    Bdzzld Well-Known Member

    Joined:
    Apr 3, 2004
    Messages:
    391
    Likes Received:
    4
    Trophy Points:
    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.
     
  2. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    5,815
    Likes Received:
    444
    Trophy Points:
    233
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    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!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. Bdzzld

    Bdzzld Well-Known Member

    Joined:
    Apr 3, 2004
    Messages:
    391
    Likes Received:
    4
    Trophy Points:
    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).
     
  4. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    5,815
    Likes Received:
    444
    Trophy Points:
    233
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    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
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice