SOLVED Mysqldump failure on automatic backups

janipewter

Active Member
Jan 2, 2013
44
3
8
cPanel Access Level
Root Administrator
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

Active Member
Aug 13, 2018
25
3
3
Internet
cPanel Access Level
Root Administrator
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

Active Member
Jan 2, 2013
44
3
8
cPanel Access Level
Root Administrator
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

Product Owner
Staff member
Nov 14, 2017
13,296
1,271
313
Houston
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

Active Member
Jan 2, 2013
44
3
8
cPanel Access Level
Root Administrator
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

Product Owner
Staff member
Nov 14, 2017
13,296
1,271
313
Houston
Hello @janipewter

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

Code:
/usr/local/cpanel/bin/backup --force
Thanks!
 

janipewter

Active Member
Jan 2, 2013
44
3
8
cPanel Access Level
Root Administrator
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

Product Owner
Staff member
Nov 14, 2017
13,296
1,271
313
Houston
Hi @janipewter

I am curious about the error you're receiving:

Code:
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:

Code:
ls -lah /var/lib/mysql
Thanks!
 

janipewter

Active Member
Jan 2, 2013
44
3
8
cPanel Access Level
Root Administrator
[[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

Well-Known Member
Aug 14, 2001
345
18
318
Brisbane, Australia
cPanel Access Level
Root Administrator
Twitter
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
Code:
cat /var/lib/mysql/mysql_upgrade_info
Code:
mysqladmin ver
 

cPanelLauren

Product Owner
Staff member
Nov 14, 2017
13,296
1,271
313
Houston
Hi @janipewter

This is what I was looking for:

Code:
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
Code:
/var/lib/mysql/.ssh

Thanks!
 
  • Like
Reactions: eva2000

janipewter

Active Member
Jan 2, 2013
44
3
8
cPanel Access Level
Root Administrator
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
Code:
cat /var/lib/mysql/mysql_upgrade_info
Code:
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:

Code:
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
Code:
/var/lib/mysql/.ssh

Thanks!
Will try this and report back. Thanks.
 

janipewter

Active Member
Jan 2, 2013
44
3
8
cPanel Access Level
Root Administrator
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

Product Owner
Staff member
Nov 14, 2017
13,296
1,271
313
Houston
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:
Code:
cat /var/lib/mysql/mysql_upgrade_info
10.2.17-MariaDB
Code:
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:
Code:
mysql_upgrade --force
was a resolution to the issue
 
  • Like
Reactions: eva2000

janipewter

Active Member
Jan 2, 2013
44
3
8
cPanel Access Level
Root Administrator
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.