Bug #100810 miss to check table definition for partitioned table while importing tablespace

• Submitted: 11 Sep 2020 8:11
• Modified: 14 Sep 2020 13:47
• Reporter: Brian Yue (OCA)
• Status: Verified
• Category: MySQL Server: Partitions
• Severity: S1 (Critical)
• Version: MySQL8.0.20
• OS: Any (rhel-7.4)
• CPU Architecture: Any (intel x86)
• Tags: data missing, IMPORT TABLESPACE, partition

[11 Sep 2020 8:11] Brian Yue

Description:
Dear Verification Team,

  I found a problem that, import tablespace failed to check table definition for partition table, leading to data missing in select query.

  Please reference to `How to repeat` part for detail.

How to repeat:
(1) create source table `part`

create database yxx;
use yxx;

CREATE TABLE `part` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
 PARTITION BY RANGE (`id`)
(PARTITION p0 VALUES LESS THAN (1000) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
 
 insert into part values (1050,1050);
 insert into part values (2050,2050);

(2) create dest table `part1`
 
CREATE TABLE `part1` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
 PARTITION BY RANGE (`id`)
(PARTITION p0 VALUES LESS THAN (1100) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2100) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

(3) copy and import tablespace from `part` to `part1`

mysql> alter table part1 discard tablespace;
Query OK, 0 rows affected (0.09 sec)

mysql> flush table part for export;
Query OK, 0 rows affected (0.03 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table part1 import tablespace;
Query OK, 0 rows affected (0.41 sec)

(4) check data of table `part1`
mysql> select * from part1;
+------+------+
| id   | age  |
+------+------+
| 1050 | 1050 |
| 2050 | 2050 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from part1 where id = 1050;
Empty set (0.00 sec)

mysql> select * from part1 where id = 2050;
Empty set (0.00 sec)

We can see that, with query `select * from part1`, we can get all records; 
But if we query records with primary key, we get none of record 1050 and 2050;

I guess, In this case, table definitions of table part and part1 are not same, 
we shouldn't continue to import tablespace, right?

Suggested fix:
Stop to import tablespace when partition info of source table and dest table are not same, I think.

[14 Sep 2020 13:29] MySQL Verification Team

mysql [localhost:8021] {root} ((none)) > create database yxx;
Query OK, 1 row affected (0.00 sec)

mysql [localhost:8021] {root} ((none)) > use yxx;
Database changed
mysql [localhost:8021] {root} (yxx) > CREATE TABLE `part` (
    ->   `id` int(11) NOT NULL,
    ->   `age` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
    ->  PARTITION BY RANGE (`id`)
    -> (PARTITION p0 VALUES LESS THAN (1000) ENGINE = InnoDB,
    ->  PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
    ->  PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql [localhost:8021] {root} (yxx) >
mysql [localhost:8021] {root} (yxx) >  insert into part values (1050,1050);
Query OK, 1 row affected (0.01 sec)

mysql [localhost:8021] {root} (yxx) >  insert into part values (2050,2050);
Query OK, 1 row affected (0.00 sec)

mysql [localhost:8021] {root} (yxx) > CREATE TABLE `part1` (
    ->   `id` int(11) NOT NULL,
    ->   `age` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
    ->  PARTITION BY RANGE (`id`)
    -> (PARTITION p0 VALUES LESS THAN (1100) ENGINE = InnoDB,
    ->  PARTITION p1 VALUES LESS THAN (2100) ENGINE = InnoDB,
    ->  PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql [localhost:8021] {root} (yxx) >  alter table part1 discard tablespace;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8021] {root} (yxx) > flush table part for export;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8021] {root} (yxx) > unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8021] {root} (yxx) > alter table part1 import tablespace;
ERROR 1812 (HY000): Tablespace is missing for table `yxx`.`part1`.
mysql [localhost:8021] {root} (yxx) >

[14 Sep 2020 13:39] MySQL Verification Team

[arhimed@localdev yxx]$ cp part#p#p0.ibd part1#p#p0.ibd
[arhimed@localdev yxx]$ cp part#p#p1.ibd part1#p#p1.ibd
[arhimed@localdev yxx]$ cp part#p#pmax.ibd part1#p#pmax.ibd

[14 Sep 2020 13:40] MySQL Verification Team

mysql [localhost:8021] {msandbox} (yxx) > alter table part1 import tablespace;
Query OK, 0 rows affected, 3 warnings (0.11 sec)
mysql [localhost:8021] {msandbox} (yxx) > show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                          |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './yxx/part1#p#p0.cfg', will attempt to import without schema verification   |
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './yxx/part1#p#p1.cfg', will attempt to import without schema verification   |
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './yxx/part1#p#pmax.cfg', will attempt to import without schema verification |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql [localhost:8021] {msandbox} (yxx) >

mysql [localhost:8021] {msandbox} (yxx) > select * from part1;
+------+------+
| id   | age  |
+------+------+
| 1050 | 1050 |
| 2050 | 2050 |
+------+------+
2 rows in set (0.00 sec)

mysql [localhost:8021] {msandbox} (yxx) > select * from part1 where id = 1050;
Empty set (0.00 sec)

mysql [localhost:8021] {msandbox} (yxx) > select * from part1 where id = 2050;
Empty set (0.00 sec)

mysql [localhost:8021] {msandbox} (yxx) >

[14 Sep 2020 13:47] MySQL Verification Team

mysql [localhost:8021] {msandbox} (yxx) > alter table part1 discard tablespace;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8021] {msandbox} (yxx) > flush table part for export;
Query OK, 0 rows affected (0.00 sec)

---
[arhimed@localdev yxx]$ cp part#p#p0.cfg part1#p#p0.cfg
[arhimed@localdev yxx]$ cp part#p#p0.ibd part1#p#p0.ibd
[arhimed@localdev yxx]$ cp part#p#p1.cfg part1#p#p1.cfg
[arhimed@localdev yxx]$ cp part#p#p1.ibd part1#p#p1.ibd
[arhimed@localdev yxx]$ cp part#p#pmax.cfg part1#p#pmax.cfg
[arhimed@localdev yxx]$ cp part#p#pmax.ibd part1#p#pmax.ibd
[arhimed@localdev yxx]$

---
mysql [localhost:8021] {msandbox} (yxx) > unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8021] {msandbox} (yxx) > alter table part1 import tablespace;
Query OK, 0 rows affected (0.09 sec)

mysql [localhost:8021] {msandbox} (yxx) > select * from part1;
+------+------+
| id   | age  |
+------+------+
| 1050 | 1050 |
| 2050 | 2050 |
+------+------+
2 rows in set (0.00 sec)

mysql [localhost:8021] {msandbox} (yxx) > select * from part1 where id = 1050;
Empty set (0.01 sec)

mysql [localhost:8021] {msandbox} (yxx) > select * from part1 where id = 2050;
Empty set (0.00 sec)

mysql [localhost:8021] {msandbox} (yxx) >

[14 Sep 2020 13:47] MySQL Verification Team

Hi,

Thanks for the test case, verified with 8.0.21