At Percona Managed Services, we manage Percona for MySQL, MySQL Community, MariaDB, and other software. Sometimes we might need to use mydumper/myloader to restore one or more databases from the production environment to the lower environment for testing.
The automated restore process ran well until one day, we got the error below.
0. The error we face is below, myloader failed to restore view with function.
1 2 3 4 | /usr/bin/myloader --host=localhost --directory=/root/test --queries-per-transaction=100 --threads=4 --verbose=3 --database=db_new --source-db=db ** (myloader:7879): CRITICAL **: 06:07:30.355: Error occurs between lines: 10 and 13 on file db...vw_##schema-view.sql.gz: FUNCTION db_new.fn_get_### does not exist ** Message: 06:07:30.355: |
After checking the db…vw_##schema-view.sql.gz file, the view definition, we found it calls a function that is not created when restored, therefore, the error above triggers.
We reproduce this error with the same mydumper 0.12.7-3 and Percona Server for MySQL 8.0.33 version installed on the test server:
1. We create the test table, view, and function as below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | mysql> show create table testdb.test_tabG *************************** 1. row *************************** Table: test_tab Create Table: CREATE TABLE `test_tab` ( `id` bigint NOT NULL, `test_data` varchar(50) NOT NULL, `op_time` timestamp NOT NULL, PRIMARY KEY (`id`,`op_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> select * from test_tab; +----+-----------+---------------------+ | id | test_data | op_time | +----+-----------+---------------------+ | 1 | lt1 | 2023-11-26 20:58:04 | | 2 | lt2 | 2023-11-26 20:58:29 | | 3 | lt3 | 2023-11-26 21:04:26 | +----+-----------+---------------------+ 3 rows in set (0.15 sec) DELIMITER // CREATE FUNCTION `fn_get_status`(pid INT) RETURNS int(1) BEGIN -- Variables DECLARE _status INT(1); SET _status = 0; CASE WHEN ((SELECT IFNULL(id,0) FROM test_tab WHERE id = pid LIMIT 1 ) = 0 ) THEN SET _status = 1; RETURN _status; ELSE RETURN _status; END CASE; END// delimiter ; CREATE VIEW `vw_testtab_detail` AS select test_tab.* (select `fn_get_status`(1)) AS `status` from test_tab; mysql> CREATE VIEW `vw_testtab_detail` AS select test_tab.* , `fn_get_status`(1) AS `status` from test_tab; Query OK, 0 rows affected (0.91 sec) mysql> select *from vw_testtab_detail; +----+-----------+---------------------+--------+ | id | test_data | op_time | status | +----+-----------+---------------------+--------+ | 1 | lt1 | 2023-11-26 20:58:04 | 0 | | 2 | lt2 | 2023-11-26 20:58:29 | 0 | | 3 | lt3 | 2023-11-26 21:04:26 | 0 | +----+-----------+---------------------+--------+ 3 rows in set (0.00 sec) |
2. Take the mydumper backup
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | root@deb11m8:~/test# /usr/bin/mydumper --success-on-1146 --outputdir=/root/test --database testdb --verbose=3 --host=localhost --port=3306 --kill-long-queries --chunk-filesize=5120 --events --routines --triggers --less-locking ** Message: 17:41:49.111: MyDumper backup version: 0.12.7-3 ** Message: 17:41:49.311: Server version reported as: 8.0.33-25 ** Message: 17:41:49.313: Connected to a MySQL server ** Message: 17:41:49.314: Sending Flush Table …. ** Message: 17:41:53.366: Waiting jobs to complete ** Message: 17:41:53.366: Thread 2 dumping SP and VIEWs for `testdb` ** Message: 17:41:53.367: Thread 3 shutting down ** Message: 17:41:53.468: Thread 1 shutting down ** Message: 17:41:53.618: Thread 4 shutting down ** Message: 17:41:53.618: Releasing DDL lock ** Message: 17:41:53.619: Main connection closed ** Message: 17:41:53.659: Finished dump at: 2024-03-01 17:41:53 root@deb11m8:~/test# ls -ltr |
3. We simulate the restore with a change of the DB name
1 2 3 4 5 6 7 8 9 10 11 12 | root@deb11m8:~/test# /usr/bin/myloader --host=localhost --directory=/root/test --queries-per-transaction=100 --threads=4 --verbose=3 --database=testdb_new --source-db=testdb ** Message: 17:45:27.924: Server version reported as: 8.0.33-25 ** Message: 17:45:29.135: Intermediate queue ending ** Message: 17:45:32.174: Thread 3 restoring `testdb_new`.`test_tab` part 1 of 1 from testdb.test_tab.00000.sql. Progress 2 of 2. ** Message: 17:45:32.175: Thread 1: Data import ended ** Message: 17:45:32.175: Thread 1: Starting post import task over table ** Message: 17:45:32.426: Thread 3: Data import ended ** Message: 17:45:32.426: Thread 3: Starting post import task over table <strong>** (myloader:11470): CRITICAL **: 17:45:32.581: Error restoring: CREATE ALGORITHM=UNDEFINED DEFINER=`larry`@`%` SQL SECURITY DEFINER VIEW `vw_testtab_detail` AS select `test_tab`.`id` AS `id`,`test_tab`.`test_data` AS `test_data`,`test_tab`.`op_time` AS `op_time`,`fn_get_status`(1) AS `status` from `test_tab`; FUNCTION testdb_new.fn_get_status does not exist ** (myloader:11470): CRITICAL **: 17:45:32.581: Error occurs between lines: 10 and 10 on file testdb.vw_testtab_detail-schema-view.sql: FUNCTION testdb_new.fn_get_status does not exist </strong>** Message: 17:45:32.724: Starting table checksum verification |
Yes, the same error as we got before.
4. One possible solution is to add:
Add the content (to be specific, the create function part) of file testdb-schema-post.sql
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | root@deb11m8:~/test# cat testdb-schema-post.sql SET @PREV_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT; SET @PREV_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS; SET @PREV_COLLATION_CONNECTION=@@COLLATION_CONNECTION; SET character_set_client = utf8mb4; SET character_set_results = utf8mb4; SET collation_connection = utf8mb4_0900_ai_ci; DROP FUNCTION IF EXISTS `fn_get_status`; CREATE DEFINER=`larry`@`%` FUNCTION `fn_get_status`(pid INT) RETURNS int BEGIN DECLARE _status INT(1); SET _status = 0; CASE WHEN ((SELECT IFNULL(id,0) FROM test_tab WHERE id = pid LIMIT 1 ) = 0 ) THEN SET _status = 1; RETURN _status; ELSE RETURN _status; END CASE; END; SET character_set_client = @PREV_CHARACTER_SET_CLIENT; SET character_set_results = @PREV_CHARACTER_SET_RESULTS; SET collation_connection = @PREV_COLLATION_CONNECTION; |
to the top of the file testdb.vw_testtab_detail-schema-view.sql.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /*!40101 SET NAMES binary*/; DROP TABLE IF EXISTS `vw_testtab_detail`; DROP VIEW IF EXISTS `vw_testtab_detail`; SET @PREV_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT; SET @PREV_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS; SET @PREV_COLLATION_CONNECTION=@@COLLATION_CONNECTION; SET character_set_client = utf8mb4; SET character_set_results = utf8mb4; SET collation_connection = utf8mb4_0900_ai_ci; CREATE ALGORITHM=UNDEFINED DEFINER=`larry`@`%` SQL SECURITY DEFINER VIEW `vw_testtab_detail` AS select `test_tab`.`id` AS `id`,`test_tab`.`test_data` AS `test_data`,`test_tab`.`op_time` AS `op_time`,`fn_get_status`(1) AS `status` from `test_tab`; SET character_set_client = @PREV_CHARACTER_SET_CLIENT; SET character_set_results = @PREV_CHARACTER_SET_RESULTS; SET collation_connection = @PREV_COLLATION_CONNECTION; |
Then, run the myloader command to restore again. This will create the function first and create the view depending on the function later.
We run myloader again, it’s good
1 | /usr/bin/myloader --host=localhost --directory=/root/test --queries-per-transaction=100 --threads=4 --verbose=3 --database=testdb_new --source-db=testdb |
Another option is to upgrade mydumper(myloader) to the latest version, as it’s a known issue and discussed here:
https://github.com/mydumper/mydumper/issues/755
But we need to upgrade mydumper on both the source server where the backup was taken, and the target server where the database we are going to restore to.
Otherwise, we might get the error below if we are trying to restore the backup with the latest mydumper v0.16.1-2, as it cannot restore the backup taken by version 0.12.7-3,
1 2 3 4 5 6 7 8 9 10 11 | root@deb11m8:~/test16# /usr/bin/myloader -u root --ask-password --host=localhost --directory=/root/test --queries-per-transaction=100 --threads=4 --verbose=3 --database=testdb_new --source-db=testdb ** Message: 16:26:21.369: Using 4 loader threads Enter MySQL Password: ** Message: 16:26:27.076: Connection via default library settings using password: Host: localhost User: root ** Message: 16:26:27.089: Initializing initialize_worker_schema ** (myloader:3638): WARNING **: 16:26:27.094: Failed to load config file /root/test/metadata: Key file contains line “Started dump at: 2024-03-01 17:41:49” which is not a key-value pair, group, or comment <strong>** (myloader:3638): ERROR **: 16:26:27.094: Global metadata file processing was not possible</strong> Trace/breakpoint trap root@deb11m8:~/test16# |
Conclusion
Hope this is helpful for your daily MySQL and MariaDB management or if you face the same issue as I did.
As mentioned in the beginning, Percona offers Support and Managed Services for Community versions of MariaDB.
At the same time, we encourage MariaDB users to explore Percona Server for MySQL as an alternative.
Should you make the decision to migrate to Percona Software for MySQL, our experts will assist throughout the migration process and support you after the migration is complete.
Contact us to discuss migration options
