Bug #102137 Clone donor's innodb_validate_tablespace_paths = OFF leads ER_TABLESPACE_MISSING
Submitted: 4 Jan 7:00 Modified: 26 Jan 14:29
Reporter: tsubasa tanaka (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.22 OS:CentOS (7.9.2009)
Assigned to: CPU Architecture:Any (x86)

[4 Jan 7:00] tsubasa tanaka
Description:
When Clone **donor** setting "innodb_validate_tablespace_paths = OFF", CLONE INSTANCE operation is wrong.

CLONE INSTANCE operation seems succeeding but SELECT statement fails by ER_TABLESPACE_MISSING (Error 1812)

How to repeat:
On doner-node (172.17.0.2:3306)

mysql> SELECT @@innodb_validate_tablespace_paths;
+------------------------------------+
| @@innodb_validate_tablespace_paths |
+------------------------------------+
|                                  0 |
+------------------------------------+
1 row in set (0.05 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE DATABASE d1;
Query OK, 1 row affected (0.19 sec)

mysql> use d1
Database changed

mysql> CREATE TABLE t1 (num int PRIMARY KEY);
Query OK, 0 rows affected (0.06 sec)

------

On recipient-node(172.17.0.3:3306) returns Error 1812,  both innodb_validate_tablespace_paths = ON and OFF.

mysql> SELECT @@innodb_validate_tablespace_paths;
+------------------------------------+
| @@innodb_validate_tablespace_paths |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set (0.05 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.01 sec)

mysql> SET GLOBAL clone_valid_donor_list = '172.17.0.2:3306';
Query OK, 0 rows affected (0.00 sec)

mysql> CLONE INSTANCE FROM root@172.17.0.2:3306 IDENTIFIED BY '';
Query OK, 0 rows affected (2.01 sec)

-- After restart,

mysql> SELECT * FROM d1.t1;
ERROR 1812 (HY000): Tablespace is missing for table `d1`.`t1`.

In recipient-node's error log.

2021-01-04T06:47:54.626934Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-01-04T06:47:55.163934Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-01-04T06:47:55.326973Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 2, name 'd1/t1', file './d1/t1.ibd' is missing!
[5 Jan 8:39] MySQL Verification Team
Hello Tanaka-San,

Thank you for the report and feedback.
I'm not seeing any issues at my end while reproducing the issue. 

---- On single instance(Donor/Recipient) or on individual instances
-- Donor (8.0.22)  on support-cluster04 

rm -rf 102137D/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/102137D --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/102137D --core-file --socket=/tmp/mysql_D_ushastry.sock  --port=3333 --log-error=$PWD/102137D/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv=/tmp/ --innodb_validate_tablespace_paths=OFF 2>&1 &

 bin/mysql -uroot -S /tmp/mysql_D_ushastry.sock --prompt='Donor>'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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.

Donor>SELECT @@innodb_validate_tablespace_paths;
+------------------------------------+
| @@innodb_validate_tablespace_paths |
+------------------------------------+
|                                  0 |
+------------------------------------+
1 row in set (0.00 sec)

Donor>INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.04 sec)

Donor>CREATE DATABASE d1;
Query OK, 1 row affected (0.02 sec)

Donor>use d1
Database changed
Donor>CREATE TABLE t1 (num int PRIMARY KEY);
Query OK, 0 rows affected (0.07 sec)

Donor>

--- Recipient (8.0.22)  on support-cluster01

rm -rf 102137R/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/102137R --log-error-verbosity=3
bin/mysqld_safe --no-defaults --basedir=$PWD --datadir=$PWD/102137R --core-file --socket=/tmp/mysql_R_ushastry.sock  --port=3336 --log-error=$PWD/102137R/log.err --mysqlx-port=33360 --mysqlx-socket=/tmp/mysql_xx_ushastry.sock --log-error-verbosity=3  --secure-file-priv=/tmp/ 2>&1 &

 bin/mysql -uroot -S /tmp/mysql_R_ushastry.sock --prompt='Recipient>'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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.

Recipient>SELECT @@innodb_validate_tablespace_paths;
+------------------------------------+
| @@innodb_validate_tablespace_paths |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set (0.00 sec)

Recipient>INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.03 sec)

Recipient>SET GLOBAL clone_valid_donor_list = 'localhost:3333';
Query OK, 0 rows affected (0.00 sec)

Recipient>CLONE INSTANCE FROM root@localhost:3333 IDENTIFIED BY '';
Query OK, 0 rows affected (2.09 sec)

Recipient>Restarting mysqld...
2021-01-05T08:36:03.278176Z mysqld_safe Number of processes running now: 0
2021-01-05T08:36:03.287420Z mysqld_safe mysqld restarted

Recipient>SELECT * FROM d1.t1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    7
Current database: *** NONE ***

Empty set (0.02 sec)

Recipient>SELECT * FROM d1.t1;
Empty set (0.00 sec)

Recipient>

Is there anything I'm missing here? Please let me know. Thank you!

regards,
Umesh
[7 Jan 2:54] tsubasa tanaka
Hi Umesh,

Thank you for testing, I confirm the problem does not occur by your command-line operations.
Maybe I miss something to reproduce the problem and I look for it.

Could you set Status as "Can't Repeat"?
If I find the key for reproducing, I would re-open this report.

Thank you.
[7 Jan 8:29] tsubasa tanaka
Hi MySQL Verification Team, 

I found 2 points which I missed.

1. This problem appears docker container only(Not appears Linux - Generic binary)
  - I changed Category (Clone plugin -> Docker Images)

2. CREATE TABLE before "turn off innodb_validate_tablespace_paths".

$ docker --version
Docker version 20.10.2, build 2291f61

---

docker run -d -P --name=donor -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -e MYSQL_ROOT_PASSWORD="""" -e MYSQL_ROOT_HOST=""%"" mysql/mysql-server:8.0.22

mysql -h172.17.0.2 ### donor-container IPAddr
  ## In mysql command-line client
  INSTALL PLUGIN clone SONAME 'mysql_clone.so';
  CREATE DATABASE d1;
  use d1
  CREATE TABLE t1 (num int);
  quit

docker exec -it donor bash
  ## In the container
  echo "innodb_validate_tablespace_paths=OFF" >> /etc/my.cnf
  exit
docker restart donor

----

docker run -d -P --name=recipient --restart=on-failure -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -e MYSQL_ROOT_PASSWORD="""" -e MYSQL_ROOT_HOST=""%"" mysql/mysql-server:8.0.22

mysql -h172.17.0.3 ### recipient-container IPAddr
  ## In mysql command-line client  
  INSTALL PLUGIN clone SONAME 'mysql_clone.so';
  SET GLOBAL clone_valid_donor_list = '172.17.0.2:3306';
  CLONE INSTANCE FROM root@172.17.0.2:3306 IDENTIFIED BY '';

mysql> SELECT * FROM d1.t1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    8
Current database: *** NONE ***

ERROR 1812 (HY000): Tablespace is missing for table `d1`.`t1`.

I can't set Status as "Open" by myself.

> Status of this bug can no longer be changed. If you want the bug reopened, please give your reasons below with a comment.

Could you try to reproduce again?
[7 Jan 9:13] MySQL Verification Team
Thank you tanaka-San, I'll give it a try and come back to you if needed further info.

regards,
Umesh
[7 Jan 13:00] MySQL Verification Team
Thank you tanaka-San, verified on docker images.

regards,
Umesh
[7 Jan 13:12] MySQL Verification Team
Docker test results

Attachment: 102137_Docker.results.txt (text/plain), 9.91 KiB.

[26 Jan 14:29] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.24 release, and here's the proposed changelog entry from the documentation team:

A missing tablespace error was reported on the recipient MySQL server
instance after a remote cloning operation. The tablespace was not cloned
due to the innodb_validate_tablespace_paths variable being disabled on the
donor instance, which resulted in the associated tablespace object not
being loaded. A check is now performed before a cloning operation to
ensure that all tablespace objects are loaded.