Skip to main content

Problem with backup after mariadb v10.3 upgrade

billmcollam
I recently upgraded from MariaDBv10.1 to v10.3. I noted the critical message:
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: [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.

Comments

12 comments

Date Votes
  • cPanelLauren
    Hi @billmcollam mysqldump should be packaged with the mariadb installation. Can you run the following: rpm -qa |grep MariaDB
    rpm-qa |grep coreutils
    mysql --version
    0
  • billmcollam
    Hi @billmcollam mysqldump should be packaged with the mariadb installation. Can you run the following:

    Thanks for the reply - see responses below. rpm -qa |grep MariaDB
    [root@vps 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 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 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
    0
  • cPanelLauren
    No you shouldn't need to reboot after an upgrade of this nature.
    0
  • billmcollam
    Okay... so any idea why I still get this error on the Cpanel Backup script - but works fine manualy?
    0
  • cPanelLauren
    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:
    Hi @billmcollam mysqldump should be packaged with the mariadb installation. Can you run the following: rpm -qa |grep MariaDB
    rpm-qa |grep coreutils
    mysql --version

    0
  • billmcollam
    My response had all that information ... pls look back thread
    0
  • cPanelLauren
    My response had all that information ... pls look back thread

    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: mysqldump --version mysqldump Ver 10.17 Distrib 10.3.23-MariaDB, for Linux (x86_64)
    # 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 @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@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 TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2020-06-05 0:11:53
    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? mysqldump --version
    stat /usr/bin/mysqldump
    What happens when you try to manually run mysqldump? mysqldump --databases $dbname
    cat /etc/my.cnf
    0
  • billmcollam
    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: mysqldump --version mysqldump Ver 10.17 Distrib 10.3.23-MariaDB, for Linux (x86_64)
    # 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 @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@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 TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2020-06-05 0:11:53
    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? mysqldump --version
    stat /usr/bin/mysqldump
    What happens when you try to manually run mysqldump? mysqldump --databases $dbname
    cat /etc/my.cnf

    [root@vps ~]# mysqldump --version mysqldump Ver 10.17 Distrib 10.3.23-MariaDB, for Linux (x86_64) [root@vps ~]# 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 [root@vps ~]# 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 @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@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. [root@vps ~]# 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
    0
  • SamuelM
    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? # 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.
    0
  • billmcollam
    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
    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? # 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.

    0
  • billmcollam
    Confirming that the problem with the scripted backup has been cleared. Thank you very much. Cheers.
    0
  • SamuelM
    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
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post