Bug #109035 | Clone plugin wipe out the user-created data before its operation is fobidden | ||
---|---|---|---|
Submitted: | 9 Nov 2022 6:46 | Modified: | 11 Jan 2023 12:28 |
Reporter: | Phong Dinh | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Clone Plugin | Severity: | S3 (Non-critical) |
Version: | 8.0.29, 8.0.31 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[9 Nov 2022 6:46]
Phong Dinh
[30 Nov 2022 6:13]
MySQL Verification Team
Hello Phong Dinh, Thank you for the report and feedback. I tried to reproduce the issue but not seeing any as reported. I'm joining the test results for your reference, please let me know if I'm missing anything here. Thank you. regards, Umesh
[30 Nov 2022 6:14]
MySQL Verification Team
8.0.29 test results
Attachment: 109035.results (application/octet-stream, text), 33.60 KiB.
[1 Jan 2023 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[4 Jan 2023 4:10]
Agustín G
Hi Umesh, I'm able to reproduce with current latest, even, with almost exact steps as you sent. 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 ################################## Please let me know what additional information I can provide to help validate this issue.
[4 Jan 2023 4:22]
MySQL Verification Team
Thank you, Agustín. Let me retry once again and get back to you. Strange that with identical steps you are able to see the issue. Sincerely, Umesh
[11 Jan 2023 12:28]
MySQL Verification Team
Thank you, Agustín. Sincerely, Umesh
[21 Jul 2023 18:19]
Sveta Smirnova
You need to use DATA DIRECTORY option if want to keep data on the recipient server when using Clone plugin. See also https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-remote.html
[3 Aug 2023 16:11]
Sveta Smirnova
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.