Clone plugin wipe out the user-created data before its operation is forbidden
General
Escalation
General
Escalation
Description
Environment
None
AFFECTED CS IDs
CS0032774
Activity
Show:
Sveta Smirnova August 3, 2023 at 4:11 PM
While this is actually expected behavior of the Clone plugin to remove all user data at some point, it leaves database in not recoverable state after data is removed. This means that users would need to re-initialize mysqld instance. It is time-consuming and could be not easy tasks for many users and environments. I suggest it to be fixed as follow:
1 . Add more checks (such as variables sanity) before dropping data.
2. If all checks passed, before dropping data, create a directory, called, say, datadir_essential and copy content of the mysql database into it together with auto.cnf and other persistent options.
3. If clone operation successes, remove directory datadir_essential.
4. If clone operation fails, rename datadir_essential to datadir and restart the server.
This way user will have operational mysqld instance even after clone plugin failure.
Sveta Smirnova June 23, 2023 at 1:46 PM
Steps from
Step 1, in donor:
##################################
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> INSTALL PLUGIN CLONE SONAME "mysql_clone.so";
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE USER clone_user IDENTIFIED BY "clone_password";
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT BACKUP_ADMIN ON *.* to clone_user;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT ON performance_schema.* TO clone_user;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT EXECUTE ON *.* to clone_user;
Query OK, 0 rows affected (0.01 sec)
mysql> create schema test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> CREATE TABLE `keyvalue` (
-> `id` bigint(20) unsigned NOT NULL,
-> `name1` varchar(250),
-> PRIMARY KEY (`id`, name1(10))
-> ) ENGINE=innodb;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> set @id:=0;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into `keyvalue` values (@id:=@id+1,md5(rand()*1000000));
Query OK, 1 row affected, 1 warning (0.00 sec)
##################################Step 2, in recipient:
##################################
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> INSTALL PLUGIN CLONE SONAME "mysql_clone.so";
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE USER clone_user IDENTIFIED BY "clone_password";
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT CLONE_ADMIN ON *.* to clone_user;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT SELECT ON performance_schema.* TO clone_user;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT EXECUTE ON *.* to clone_user;
Query OK, 0 rows affected (0.00 sec)
mysql> create schema test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> CREATE TABLE `keyvalue` (
-> `id` bigint(20) unsigned NOT NULL,
-> `name1` varchar(250),
-> PRIMARY KEY (`id`, name1(10))
-> ) ENGINE=innodb;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> set @id:=0;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into `keyvalue` values (@id:=@id+1,md5(rand()*1000000));
Query OK, 1 row affected, 1 warning (0.01 sec)
##################################Step 3, in recipient, we check for the ibd file in datadir:
##################################
shell> ls -l /var/lib/mysql/test/
total 112
-rw-r-----. 1 mysql mysql 114688 Jan 4 04:01 keyvalue.ibd
##################################Step 4, in recipient, we add config change and restart service:
##################################
shell> cat <<EOF >>/etc/my.cnf
innodb_force_recovery=3
EOF
shell> systemctl restart mysqld
##################################Step 5, in recipient, we try to clone the other instance:
##################################
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW VARIABLES LIKE '%VERSION%';
+--------------------------+------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------+
| admin_tls_version | TLSv1.2 |
| immediate_server_version | 999999 |
| innodb_version | 8.0.31 |
| original_server_version | 999999 |
| protocol_version | 10 |
| replica_type_conversions | |
| slave_type_conversions | |
| tls_version | TLSv1.2 |
| version | 8.0.31 |
| version_comment | MySQL Community Server - GPL |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| version_compile_zlib | 1.2.12 |
+--------------------------+------------------------------+
13 rows in set (0.01 sec)
mysql> SELECT @@innodb_force_recovery;
+-------------------------+
| @@innodb_force_recovery |
+-------------------------+
| 3 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL clone_valid_donor_list = "10.124.33.130:3306";
Query OK, 0 rows affected (0.00 sec)
mysql> CLONE INSTANCE FROM 'clone_user'@'10.124.33.130':3306 IDENTIFIED BY "clone_password";
ERROR 1881 (HY000): Operation not allowed when innodb_force_recovery > 0.
##################################The log on the recipient will show:
##################################
2023-01-04T04:02:09.651858Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.31' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
2023-01-04T04:02:09.658056Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-01-04T04:02:09.658274Z 7 [ERROR] [MY-012810] [InnoDB] innodb_force_recovery is on. We do not allow database modifications by the user. Shut down mysqld and edit my.cnf to set innodb_force_recovery=0
2023-01-04T04:02:09.658361Z 7 [Warning] [MY-010384] [Repl] Failed to compress the gtid_executed table.
2023-01-04T04:02:32.380956Z 8 [Warning] [MY-013460] [InnoDB] Clone removing all user data for provisioning: Started
2023-01-04T04:02:32.496700Z 8 [ERROR] [MY-012810] [InnoDB] innodb_force_recovery is on. We do not allow database modifications by the user. Shut down mysqld and edit my.cnf to set innodb_force_recovery=0
2023-01-04T04:02:32.496741Z 8 [ERROR] [MY-010383] [Repl] Failed to delete the row: 'fe60d642-8be3-11ed-8c03-00163ea66b21:1-4' from the gtid_executed table.
##################################And the ibd file is not there anymore:
##################################
shell> ls -l /var/lib/mysql/test/
ls: cannot access /var/lib/mysql/test/: No such file or directory
##################################Won't Do
Details
Details
Assignee
Unassigned
UnassignedReporter
Sveta SmirnovaUpstream Bug URL
Needs QA
Yes
Affects versions
Priority
MediumSmart Checklist
Open Smart Checklist
Smart Checklist
Open Smart Checklist
Created June 23, 2023 at 1:44 PM
Updated March 6, 2024 at 9:38 AM
Resolved July 21, 2023 at 11:51 AM
Description:
Clone wipes out the user-created data (schemas, tables, table spaces) before the clone operation is forbidden by innodb_force_recovery. This led to most of the data in the datadir is cleared and cause data loss in some data recovery some operations
How to repeat:
Set innodb_force_recovery=3 and perform a remote clone
Clone operation is failed
mysql> CLONE INSTANCE FROM 'root'@'10.124.33.102':3306 IDENTIFIED BY 'verysecretpassword1^'; ERROR 1881 (HY000): Operation not allowed when innodb_force_recovery > 0. But it can delete the user-created data 2022-11-09T06:26:48.621603Z 8 [Warning] [MY-013460] [InnoDB] Clone removing all user data for provisioning: Started 2022-11-09T06:26:48.621624Z 8 [Note] [MY-011977] [InnoDB] Clone Drop all user data 2022-11-09T06:26:48.705623Z 8 [Note] [MY-011977] [InnoDB] Clone: Fix Object count: 187 task: 0 2022-11-09T06:26:48.965259Z 8 [Note] [MY-011977] [InnoDB] Clone Drop User schemas 2022-11-09T06:26:48.965479Z 8 [Note] [MY-011977] [InnoDB] Clone: Fix Object count: 5 task: 0 2022-11-09T06:26:48.970821Z 8 [Note] [MY-011977] [InnoDB] Clone Drop User tablespaces 2022-11-09T06:26:48.971302Z 8 [Note] [MY-011977] [InnoDB] Clone: Fix Object count: 6 task: 0 2022-11-09T06:26:49.142278Z 8 [ERROR] [MY-012810] [InnoDB] innodb_force_recovery is on. We do not allow database modifications by the user. Shut down mysqld and edit my.cnf to set innodb_force_recovery=0 2022-11-09T06:26:49.142336Z 8 [ERROR] [MY-010383] [Repl] Failed to delete the row: '88331cf4-5fef-11ed-8d99-00163ef0f9f2:1-27' from the gtid_executed table. 2022-11-09T06:26:49.145800Z 8 [Note] [MY-011977] [InnoDB] Clone: Failed to RESET MASTER task: 1 code: 1881: Operation not allowed when innodb_force_recovery > 0. 2022-11-09T06:26:49.151543Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Wait for remote after local issue: error: 1815: Operation not allowed when innodb_force_recovery > 0..' 2022-11-09T06:26:49.151592Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Command COM_INIT: error: 1815: Operation not allowed when innodb_force_recovery > 0..' 2022-11-09T06:26:49.151888Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Master ACK COM_EXIT.' 2022-11-09T06:26:49.153809Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Master ACK Disconnect : abort: false.' 2022-11-09T06:26:49.154105Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Task COM_EXIT.' 2022-11-09T06:26:49.155894Z 8 [Note] [MY-013272] [Clone] Plugin Clone reported: 'Client: Task Disconnect : abort: false.'