Problem with backup after mariadb v10.3 upgrade

B

billmcollam

Member
Jan 12, 2015
22
2
53
Ontario Canada
cPanel Access Level
Reseller Owner
I recently upgraded from MariaDBv10.1 to v10.3. I noted the critical message:
Code: 
In MariaDB® 10.3, the mysqldump client includes logic for the mysql.transaction_registry table. You cannot use the mysqldump client from an earlier MariaDB release on MariaDB 10.3 and later. For more information about how to upgrade to MariaDB 10.3, read the documentation .
But I didnt see that it would apply since the only dump function I use is the Cpanel nightly db backup. However the next day I found the over night backp had not run and errored with the follwing:

Code: 
[2020-06-01 02:03:29 -0400] The “Mysql” failed with an error: Failed after 3 times; last error: STDERR: mysqldump: Couldn't execute 'SHOW CREATE PROCEDURE `ListReached`': Failed to load routine lyndsey0_geodata_test.ListReached. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) (1457)
This clearly looks related to the warning... but wouldn't the cpanel backup routine know enough to use the newer version of mysqldump? I can do a manual backup from the command line easily. But don't know how to correct the batch script.

Any advice appreciated.
 
cPanelLauren

cPanelLauren

Product Owner II
Staff member
Nov 14, 2017
13,266
1,301
363
Houston
Hi @billmcollam

mysqldump should be packaged with the mariadb installation. Can you run the following:

Code: 
rpm -qa |grep MariaDB
Code: 
rpm-qa |grep coreutils
Code: 
mysql --version
 
B

billmcollam

Member
Jan 12, 2015
22
2
53
Ontario Canada
cPanel Access Level
Reseller Owner
cPanelLauren said:
Hi @billmcollam

mysqldump should be packaged with the mariadb installation. Can you run the following:
Click to expand...
Thanks for the reply - see responses below.

Code: 
rpm -qa |grep MariaDB
[[email protected] backup]# rpm -qa |grep MariaDB
MariaDB-compat-10.3.23-1.el6.x86_64
MariaDB-server-10.3.23-1.el6.x86_64
MariaDB-common-10.3.23-1.el6.x86_64
MariaDB-client-10.3.23-1.el6.x86_64
MariaDB-devel-10.3.23-1.el6.x86_64
MariaDB-shared-10.3.23-1.el6.x86_64

Code: 
rpm-qa |grep coreutils
coreutils-8.4-47.el6.x86_64
coreutils-libs-8.4-47.el6.x86_64
policycoreutils-2.0.83-30.1.el6_8.x86_6

Code: 
mysql --version
mysql Ver 15.1 Distrib 10.3.23-MariaDB, for Linux (x86_64) using readline 5.1

One additional thought. After the db upgrade I didnt reboot the server. Is it possible the older version is somehow cached?

Bill
 
Last edited by a moderator:
cPanelLauren

cPanelLauren

Product Owner II
Staff member
Nov 14, 2017
13,266
1,301
363
Houston
I requested some specific items in order to assist you with that but you've not provided any of that information. I've quoted the original post below:

cPanelLauren said:
Hi @billmcollam

mysqldump should be packaged with the mariadb installation. Can you run the following:

Code: 
rpm -qa |grep MariaDB
Code: 
rpm-qa |grep coreutils
Code: 
mysql --version
Click to expand...
 
cPanelLauren

cPanelLauren

Product Owner II
Staff member
Nov 14, 2017
13,266
1,301
363
Houston
billmcollam said:
My response had all that information ... pls look back thread
Click to expand...
I see - it was missed because you included it inside the quote of my response. I've removed it so that it is visible to everyone now.

Checking a CentOS 6 server to compare and everything installed appears to be correct:


I tested by running mysqldump manually:

Code: 
mysqldump --version
mysqldump  Ver 10.17 Distrib 10.3.23-MariaDB, for Linux (x86_64)
Code: 
# mysqldump --databases cptest_testdb
-- MySQL dump 10.17  Distrib 10.3.23-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: cptest_testdb
-- ------------------------------------------------------
-- Server version    10.3.23-MariaDB

/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @[email protected]@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `cptest_testdb`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cptest_testdb` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `cptest_testdb`;
/*!40103 SET [email protected]_TIME_ZONE */;

/*!40101 SET [email protected]_SQL_MODE */;
/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
/*!40111 SET [email protected]_SQL_NOTES */;

-- Dump completed on 2020-06-05  0:11:53
Code: 
stat /usr/bin/mysqldump
  File: `/usr/bin/mysqldump'
  Size: 7453976       Blocks: 14560      IO Block: 4096   regular file
Device: fd01h/64769d    Inode: 152114      Links: 1
Access: (0755/-rwxr-xr-x)  Uid: (    0/    root)   Gid: (    0/    root)
Access: 2020-06-05 00:11:11.316999926 +0000
Modify: 2020-05-09 22:23:45.000000000 +0000
Change: 2020-06-05 00:02:26.738000017 +0000
Can you provide me the following from your server?
Code: 
mysqldump --version
Code: 
stat /usr/bin/mysqldump
What happens when you try to manually run mysqldump?
Code: 
mysqldump --databases $dbname
Code: 
cat /etc/my.cnf
 
B

billmcollam

Member
Jan 12, 2015
22
2
53
Ontario Canada
cPanel Access Level
Reseller Owner
cPanelLauren said:
I see - it was missed because you included it inside the quote of my response. I've removed it so that it is visible to everyone now.

Checking a CentOS 6 server to compare and everything installed appears to be correct:


I tested by running mysqldump manually:

Code: 
mysqldump --version
mysqldump  Ver 10.17 Distrib 10.3.23-MariaDB, for Linux (x86_64)
Code: 
# mysqldump --databases cptest_testdb
-- MySQL dump 10.17  Distrib 10.3.23-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: cptest_testdb
-- ------------------------------------------------------
-- Server version    10.3.23-MariaDB

/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @[email protected]@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `cptest_testdb`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cptest_testdb` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `cptest_testdb`;
/*!40103 SET [email protected]_TIME_ZONE */;

/*!40101 SET [email protected]_SQL_MODE */;
/*!40014 SET F[email protected]_FOREIGN_KEY_CHECKS */;
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
/*!40111 SET [email protected]_SQL_NOTES */;

-- Dump completed on 2020-06-05  0:11:53
Code: 
stat /usr/bin/mysqldump
  File: `/usr/bin/mysqldump'
  Size: 7453976       Blocks: 14560      IO Block: 4096   regular file
Device: fd01h/64769d    Inode: 152114      Links: 1
Access: (0755/-rwxr-xr-x)  Uid: (    0/    root)   Gid: (    0/    root)
Access: 2020-06-05 00:11:11.316999926 +0000
Modify: 2020-05-09 22:23:45.000000000 +0000
Change: 2020-06-05 00:02:26.738000017 +0000
Can you provide me the following from your server?
Code: 
mysqldump --version
Code: 
stat /usr/bin/mysqldump
What happens when you try to manually run mysqldump?
Code: 
mysqldump --databases $dbname
Code: 
cat /etc/my.cnf
Click to expand...
[[email protected] ~]# mysqldump --version
mysqldump Ver 10.17 Distrib 10.3.23-MariaDB, for Linux (x86_64)

[[email protected] ~]# stat /usr/bin/mysqldump
File: `/usr/bin/mysqldump'
Size: 7453976 Blocks: 14584 IO Block: 4096 regular file
Device: fd01h/64769d Inode: 1671211 Links: 1
Access: (0755/-rwxr-xr-x) Uid: ( 0/ root) Gid: ( 0/ root)
Access: 2020-06-04 21:00:49.000000000 -0400
Modify: 2020-05-09 18:23:45.000000000 -0400
Change: 2020-05-29 17:09:03.000000000 -0400

[[email protected] ~]# mysqldump --databases ********_geodata_test
-- MySQL dump 10.17 Distrib 10.3.23-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: *********_geodata_test
-- ------------------------------------------------------
-- Server version 10.3.23-MariaDB-log

/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @[email protected]@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `*******_geodata_test`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `*******_geodata_test` /*!40100 DEFAULT CHARACTER SET latin1 */;

This works fine (as I think I mentioned in my OP. Its just the batch backup script that doesnt work.

[[email protected] ~]# cat /etc/my.cnf
[mysqld]
log-error=/var/lib/mysql/vps.xxxxxxxxxx.com.err
performance-schema=0
slow-query-log=1
long-query-time=1
slow-query-log-file="/var/log/mysql/localhost-slow.log"
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
init-file = "/usr/share/mysql/mysql_start.sql"
query_cache_type=ON
query_cache_size=16777216
max_connections = 150
open_files_limit=10000
innodb_flush_log_at_trx_commit = 0


I apprecate you looking into this.... im still running manual backups until i can resolve.
Bll
 
S

SamuelM

Technical Analyst Team Lead
Nov 20, 2019
196
40
103
USA
cPanel Access Level
Root Administrator
Hello @billmcollam

Thank you for providing all that information.

Looking back at your first post, it appears that the "You cannot use the mysqldump client from an earlier MariaDB release on MariaDB 10.3 and later" warning may be unrelated to the error message that results from actually dumping a database. And I noticed that the error message occurs when attempting to dump a stored procedure for a specific database, due to an apparent issue with the mysql.proc table.

Can you let us know if you are able to run the following commands without errors?

Code: 
# mysqldump --routines mysql proc 1>/dev/null
# mysqlcheck mysql proc

# mysqldump --routines ********_geodata_test 1>/dev/null

Using 1>/dev/null discards all standard output and prints only error messages.
 
B

billmcollam

Member
Jan 12, 2015
22
2
53
Ontario Canada
cPanel Access Level
Reseller Owner
HI Samuel,
I had started to suspect the upgrade warning and the backup issue were unrelated too.
The error message refers to a procedure: LIstReached. At first I thought that was some kind of
artifact of the backup process. But when I examined the database in question it really had an
entry for that procedure - and it had marked it corrupt. I dropped the procedure, and was hoping
that tonight backup goes ahead without issue.

Running those commands you suggested all work fine. I expect the problem is cleared. I will confirm
tomorrow.

ps. I still don't know where the issue arose and the coincidental timing around the mariadb upgrade, but i'll be quite content if its gone.

Thank for the attention. Much apprecated.

Bill



cPSamuel said:
Hello @billmcollam

Thank you for providing all that information.

Looking back at your first post, it appears that the "You cannot use the mysqldump client from an earlier MariaDB release on MariaDB 10.3 and later" warning may be unrelated to the error message that results from actually dumping a database. And I noticed that the error message occurs when attempting to dump a stored procedure for a specific database, due to an apparent issue with the mysql.proc table.

Can you let us know if you are able to run the following commands without errors?

Code: 
# mysqldump --routines mysql proc 1>/dev/null
# mysqlcheck mysql proc

# mysqldump --routines ********_geodata_test 1>/dev/null

Using 1>/dev/null discards all standard output and prints only error messages.
Click to expand...
 
S

SamuelM

Technical Analyst Team Lead
Nov 20, 2019
196
40
103
USA
cPanel Access Level
Root Administrator
Hello again @billmcollam

Thanks for getting back to us to confirm you're no longer observing the problem after dropping the corrupt procedure. We can't say for sure how the problem arose either, however, please feel free to reach out to us via these forums or submit a ticket if there is anything else we can assist with.

Best regards
 
You must log in or register to reply here.
Thread starter Similar threads Forum Replies Date
C SOLVED Problem with command line mysqldump database backups Databases 8
S Restoring Database from Backup Problem Databases 4
X Problem in Restore Full Backup Databases 1
G Site backup and restore problems with MySQL databases Databases 2
B fullbackup problem to restore database Databases 2
Similar threads
SOLVED Problem with command line mysqldump database backups
Restoring Database from Backup Problem
Problem in Restore Full Backup
Site backup and restore problems with MySQL databases
fullbackup problem to restore database
Top Bottom