Debian Codegraph @

mysql-test/suite/innodb/r/import_run_once.result

raw
1 #
2 # MDEV-26137 ALTER TABLE IMPORT enhancement
3 #
4 # drop t1 before importing t2
5 CREATE TABLE t1 (a int) ENGINE=InnoDB;
6 INSERT INTO t1 VALUES(42);
7 FLUSH TABLES t1 FOR EXPORT;
8 UNLOCK TABLES;
9 DROP TABLE t1;
10 ALTER TABLE t2 IMPORT TABLESPACE;
11 SHOW CREATE TABLE t2;
12 Table Create Table
13 t2 CREATE TABLE `t2` (
14 `a` int(11) DEFAULT NULL
15 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
16 SELECT * FROM t2;
17 a
18 42
19 DROP TABLE t2;
20 # created t2 but did not discard tablespace
21 CREATE TABLE t1 (a int) ENGINE=InnoDB;
22 INSERT INTO t1 VALUES(42);
23 CREATE TABLE t2 LIKE t1;
24 FLUSH TABLES t1 FOR EXPORT;
25 UNLOCK TABLES;
26 DROP TABLE t1;
27 call mtr.add_suppression("InnoDB: Unable to import tablespace");
28 ALTER TABLE t2 IMPORT TABLESPACE;
29 ERROR HY000: Tablespace for table 'test/t2' exists. Please DISCARD the tablespace before IMPORT
30 SHOW CREATE TABLE t2;
31 Table Create Table
32 t2 CREATE TABLE `t2` (
33 `a` int(11) DEFAULT NULL
34 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
35 SELECT * FROM t2;
36 a
37 DROP TABLE t2;
38 # attempt to import when there's no tablespace
39 ALTER TABLE t2 IMPORT TABLESPACE;
40 ERROR 42S02: Table 'test.t2' doesn't exist
41 # with index
42 CREATE TABLE t1 (a int, b varchar(50)) ENGINE=InnoDB;
43 CREATE UNIQUE INDEX ai ON t1 (a);
44 INSERT INTO t1 VALUES(42, "hello");
45 FLUSH TABLES t1 FOR EXPORT;
46 UNLOCK TABLES;
47 ALTER TABLE t2 IMPORT TABLESPACE;
48 SHOW CREATE TABLE t2;
49 Table Create Table
50 t2 CREATE TABLE `t2` (
51 `a` int(11) DEFAULT NULL,
52 `b` varchar(50) DEFAULT NULL,
53 UNIQUE KEY `ai` (`a`)
54 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
55 SELECT * FROM t2;
56 a b
57 42 hello
58 SHOW INDEX FROM t1;
59 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
60 t1 0 ai 1 a A 1 NULL NULL YES BTREE NO
61 SHOW INDEX FROM t2;
62 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
63 t2 0 ai 1 a A 1 NULL NULL YES BTREE NO
64 DROP TABLE t1, t2;
65 # with virtual column index
66 CREATE TABLE t1 (a int, b int as (a * a)) ENGINE=InnoDB;
67 CREATE UNIQUE INDEX ai ON t1 (b);
68 INSERT INTO t1 VALUES(42, default);
69 FLUSH TABLES t1 FOR EXPORT;
70 UNLOCK TABLES;
71 ALTER TABLE t2 IMPORT TABLESPACE;
72 SHOW CREATE TABLE t2;
73 Table Create Table
74 t2 CREATE TABLE `t2` (
75 `a` int(11) DEFAULT NULL,
76 `b` int(11) GENERATED ALWAYS AS (`a` * `a`) VIRTUAL,
77 UNIQUE KEY `ai` (`b`)
78 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
79 SELECT * FROM t2;
80 a b
81 42 1764
82 SELECT b FROM t2 USE INDEX (ai);
83 b
84 1764
85 SHOW INDEX FROM t1;
86 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
87 t1 0 ai 1 b A 1 NULL NULL YES BTREE NO
88 SHOW INDEX FROM t2;
89 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
90 t2 0 ai 1 b A 1 NULL NULL YES BTREE NO
91 CHECK TABLE t2 EXTENDED;
92 Table Op Msg_type Msg_text
93 test.t2 check status OK
94 DROP TABLE t1, t2;
95 # with auto_increment
96 CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, i2 INT, i1 INT)ENGINE=INNODB;
97 INSERT INTO t1 (i2) SELECT 4 FROM seq_1_to_1024;
98 FLUSH TABLE t1 FOR EXPORT;
99 UNLOCK TABLES;
100 ALTER TABLE t2 IMPORT TABLESPACE;
101 CHECK TABLE t2 EXTENDED;
102 Table Op Msg_type Msg_text
103 test.t2 check status OK
104 DROP TABLE t2, t1;