SOLVED Mysqldump failure on automatic backups

[janipewter]

Recently my WHM automatic backups have been completing with errors. I get this in the email:

The backup process completed, but 10 errors occurred.

[2018-09-13 02:05:09 +0100] removed_1: mysqldump failed -- database may be corrupt
[2018-09-13 02:05:21 +0100] removed_2: mysqldump failed -- database may be corrupt
[2018-09-13 02:08:17 +0100] removed_3: mysqldump failed -- database may be corrupt
[2018-09-13 02:08:35 +0100] removed_4: mysqldump failed -- database may be corrupt
[2018-09-13 02:25:10 +0100] removed_5: mysqldump failed -- database may be corrupt
[2018-09-13 02:25:23 +0100] removed_6: mysqldump failed -- database may be corrupt
[2018-09-13 02:25:24 +0100] removed_7: mysqldump failed -- database may be corrupt
[2018-09-13 02:25:24 +0100] removed_8: mysqldump failed -- database may be corrupt
[2018-09-13 08:28:12 +0100] removed_9: mysqldump failed -- database may be corrupt
[2018-09-13 08:32:55 +0100] removed_10: mysqldump failed -- database may be corrupt

I have removed all of the names of the databases, but they are spread over multiple accounts on my server. All of the databases are in use and working perfectly. I never had this error until recently, when it suddenly started on all 10 of these databases. I have tried repairing them with phpmyadmin but it says no errors found. One of the databases was only created 2 weeks ago and is definitely not corrupt.

 

[sysnishit]

Hello,

You can use mysqlcheck -r databasename to check if databases / database tables are corrupted or not. Also, Try to backup mysql database from command line using mysqldump command

cd /home
mysqldump database_name > database_name.sql

 

[janipewter]

Thanks for your reply synsni****. I tried the mysqlcheck on all of the databases and they were all OK, nothing to repair. A couple of the databases showed this message for some tables:

The storage engine for the table doesn't support repair

But they are all still working fine.

I tried using mysqldump to make a backup of my biggest database and it worked perfectly.

Any other suggestions?

 

[cPanelLauren]

Hi @janipewter

After running mysqlcheck on the databases are you still getting an error during automated backups (regardless of whether or not errors were fixed)

Furthermore, do you have anything in relation to these databases being noted in the mysql error logs?

Thanks!

 

[janipewter]

Won't be able to tell until the next automatic backup runs on Wednesday night. For now I am doing manual backups of the affected accounts and there are no errors with those. The databases are in tact.

There are no mysql error logs either.

 

[cPanelLauren]

Hello @janipewter

You could also modify the backup schedule to set it to run or run it manually by executing the following:

/usr/local/cpanel/bin/backup --force

Thanks!

 

[janipewter]

OK, I started the backup manually using that command. Watching the log output as it's going I've already seen this:

exec(/usr/bin/mysqldump,--complete-insert --quote-names --quick --single-transaction --default-character-set=utf8mb4 --events --routines --triggers --routines -- database_name_removed) exited with error: The “/usr/bin/mysqldump --complete-insert --quote-names --quick --single-transaction --default-character-set=utf8mb4 --events --routines --triggers --routines -- database_name_removed” command (process 6851) reported error number 2 when it ended. at /usr/local/cpanel/Cpanel/Pkgacct.pm line 443.
[2018-09-18 02:31:46 +0100] database_name_removed: mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)
[2018-09-18 02:31:46 +0100] database_name_removed: mysqldump failed -- database may be corrupt
[2018-09-18 02:31:46 +0100] (7493 bytes)
[2018-09-18 02:31:46 +0100] Failed to dump database database_name_removed: The subprocess reported error number 2 when it ended.
[2018-09-18 02:31:46 +0100]
[2018-09-18 02:31:46 +0100] ERROR: Failed to dump one or more databases

Once again I have removed the database name myself. It seems that the problem isn't fixed.

 

[cPanelLauren]

Hi @janipewter

I am curious about the error you're receiving:

mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start

Can you run the following and provide the output:

ls -lah /var/lib/mysql

Thanks!

 

[janipewter]

[[email protected] ~]# ls -lah /var/lib/mysql
total 177M
drwxr-x--x 38 mysql mysql 4.0K Sep 9 14:42 .
drwxr-xr-x. 24 root root 4.0K Sep 18 04:33 ..
-rw-rw---- 1 mysql mysql 16K Sep 16 20:09 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Sep 16 20:09 aria_log_control
-rw-rw---- 1 mysql mysql 56 Apr 27 2017 auto.cnf
drwx------ 2 mysql mysql 4.0K Sep 18 02:25 db_name_removed_1
drwx------ 2 mysql mysql 4.0K Apr 27 2017 cphulkd
-rw-rw---- 1 mysql mysql 5.2K Apr 27 2017 hlgh001.hlgh.local.err
-rw-rw---- 1 mysql mysql 5.3K Apr 27 2017 HLGH001.HLGH.local.err
-rw-rw---- 1 mysql mysql 76M Sep 18 23:47 ibdata1
-rw-rw---- 1 mysql mysql 48M Sep 18 23:47 ib_logfile0
-rw-rw---- 1 mysql mysql 48M Sep 16 17:11 ib_logfile1
drwx------ 2 mysql mysql 4.0K Sep 18 06:24 db_name_removed_2
drwx------ 2 mysql mysql 4.0K Sep 18 06:23 db_name_removed_3
drwx------ 2 mysql mysql 12K Mar 16 2018 db_name_removed_4
-rw-rw---- 1 mysql mysql 0 Apr 26 00:11 multi-master.info
drwx--x--x 2 mysql mysql 4.0K Nov 6 2017 mysql
srwxrwxrwx 1 mysql mysql 0 Sep 9 14:42 mysql.sock
-rw-r--r-- 1 mysql mysql 6 Nov 6 2017 mysql_upgrade_info
drwx------ 2 mysql mysql 4.0K Jan 11 2018 db_name_removed_5
drwx------ 2 mysql mysql 32K Sep 26 2017 db_name_removed_6
drwx------ 2 mysql mysql 4.0K Nov 6 2017 performance_schema
drwx------ 2 mysql mysql 40K Oct 27 2017 db_name_removed_7
drwx------ 2 mysql mysql 4.0K Sep 18 06:23 db_name_removed_8
-rw-r--r-- 1 mysql mysql 2.3K Nov 6 2017 RPM_UPGRADE_HISTORY
-rw-r--r-- 1 mysql mysql 1.1K Nov 6 2017 RPM_UPGRADE_MARKER-LAST
drwx------ 2 mysql mysql 4.0K Feb 12 2018 db_name_removed_9
drwx------ 2 mysql mysql 12K Sep 18 06:24 db_name_removed_10
drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_11
drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_12
drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_13
drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_14
drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_15
drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_16
drwx------ 2 mysql mysql 12K Apr 28 2017 db_name_removed_17
drwx------ 2 mysql mysql 28K Apr 28 2017 db_name_removed_18
drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_19
drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_20
drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_21
drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_22
drwx------ 2 mysql mysql 4.0K Sep 12 03:07 db_name_removed_23
drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_24
drwx------ 2 mysql mysql 12K Apr 28 2017 db_name_removed_25
drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_26
drwx------ 2 mysql mysql 12K Apr 28 2017 db_name_removed_27
drwx------ 2 mysql mysql 4.0K Sep 18 02:31 db_name_removed_28
drwx------ 2 mysql mysql 4.0K Sep 1 21:00 db_name_removed_29
drwx------ 2 mysql mysql 12K Jul 25 22:01 db_name_removed_30
drwx------ 2 mysql mysql 4.0K Sep 18 02:31 db_name_removed_31
-rw-rw---- 1 mysql mysql 3.8M Sep 18 20:40 server_hostname_removed.err
-rw-rw---- 1 mysql mysql 5 Sep 9 14:42 server_hostname_removed.pid
drwx------ 2 mysql mysql 4.0K Apr 16 18:11 .ssh
-rw-rw---- 1 mysql mysql 24K Sep 9 14:42 tc.log
drwx------ 2 mysql mysql 4.0K Sep 8 2017 db_name_removed_32

 

[eva2000]

database_name_removed: mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)

sounds like mysql/mariadb server was never properly upgraded if you had done mysql upgrades. Did you make sure to run mysql_upgrade after upgrading mysql/mariadb major versions ?

what's output for these 2 commands

cat /var/lib/mysql/mysql_upgrade_info

mysqladmin ver

 

[cPanelLauren]

Hi @janipewter

This is what I was looking for:

drwx------ 2 mysql mysql 4.0K Apr 16 18:11 .ssh

I'd also wager that an upgrade attempt would fail because of the directory's presence due to it being misconstrued as a database due to it's location.

We have an open case that matches the description of what's occurring here CPANEL-21490 - /usr/local/cpanel/scripts/updatesupportauthorizations is creating empty .ssh dir in all homedirs including system users.

This case is fixed in v76 of cPanel but until the resolution is available on your build the issue should stop if you simply remove

/var/lib/mysql/.ssh

Thanks!

 

[janipewter]

sounds like mysql/mariadb server was never properly upgraded if you had done mysql upgrades. Did you make sure to run mysql_upgrade after upgrading mysql/mariadb major versions ?

what's output for these 2 commands

cat /var/lib/mysql/mysql_upgrade_info

mysqladmin ver

[[email protected] ~]# cat /var/lib/mysql/mysql_upgrade_info
5.6.38
[[email protected] ~]# mysqladmin ver
mysqladmin Ver 9.1 Distrib 10.1.36-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version 10.1.36-MariaDB
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 11 days 1 hour 37 min 17 sec

Threads: 1 Questions: 16621133 Slow queries: 0 Opens: 4193 Flush tables: 1 Open tables: 1636 Queries per second avg: 17.381

Hi @janipewter

This is what I was looking for:

drwx------ 2 mysql mysql 4.0K Apr 16 18:11 .ssh

I'd also wager that an upgrade attempt would fail because of the directory's presence due to it being misconstrued as a database due to it's location.

We have an open case that matches the description of what's occurring here CPANEL-21490 - /usr/local/cpanel/scripts/updatesupportauthorizations is creating empty .ssh dir in all homedirs including system users.

This case is fixed in v76 of cPanel but until the resolution is available on your build the issue should stop if you simply remove

/var/lib/mysql/.ssh

Thanks!

Will try this and report back. Thanks.

 

[janipewter]

Err OK, it still seems to be broken.

[2018-09-20 16:32:09 +0100] database_name_removed: mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)
exec(/usr/bin/mysqldump,--complete-insert --quote-names --quick --single-transaction --default-character-set=utf8mb4 --events --routines --triggers --routines -- database_name_removed) exited with error: The “/usr/bin/mysqldump --complete-insert --quote-names --quick --single-transaction --default-character-set=utf8mb4 --events --routines --triggers --routines -- database_name_removed” command (process 54547) reported error number 2 when it ended. at /usr/local/cpanel/Cpanel/Pkgacct.pm line 443.
[2018-09-20 16:32:09 +0100] database_name_removed: mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)
[2018-09-20 16:32:09 +0100] database_name_removed: mysqldump failed -- database may be corrupt
[2018-09-20 16:32:09 +0100] (864 bytes)
[2018-09-20 16:32:09 +0100] Failed to dump database database_name_removed: The subprocess reported error number 2 when it ended.
[2018-09-20 16:32:09 +0100]
[2018-09-20 16:32:09 +0100] ERROR: Failed to dump one or more databases
[2018-09-20 16:32:09 +0100] ...Done
Load watching resumed due to SIGUSR2
[2018-09-20 16:32:09 +0100] Completed “Mysql” component.

 

[cPanelLauren]

Hi @janipewter

I think you may have had two separate issues. The information @eva2000 provided is pointing in the right direction as well. The two should match:

From my server:

cat /var/lib/mysql/mysql_upgrade_info
10.2.17-MariaDB

mysqladmin --version
mysqladmin  Ver 9.1 Distrib 10.2.17-MariaDB, for Linux on x86_64

We have another case open for the behavior you're seeing here as well CPANEL-22320. In most cases running:

mysql_upgrade --force

was a resolution to the issue

 

[janipewter]

OK, have now updated to MariaDB 10.2 and my output for the versions now matches yours. Also did the
mysql_upgrade --force.

I will manually start the backup a bit later and see how it goes.

 

[cPanelLauren]

Hi @janipewter

Ok, that sounds good.


Thanks!

 

[janipewter]

Can confirm that this has resolved the issue. Thanks for the help everyone. Great support as always from cPanel!

 

[cPanelLauren]

Hi @janipewter

I'm glad it's resolved the issue! Thank you so much for updating here and letting us know.