1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
# Bug 34307874: Innodb import tablespace crash on tables with instant algorithm used
# Importing without .cfg file for tables with INSTANT columns must not crash
# Scenario 1: IMPORT without CFG file
CREATE TABLE t1 (id INT, str1 VARCHAR(20), index(str1));
INSERT INTO t1 VALUES (1, 'val1');
ALTER TABLE t1 ADD COLUMN str2 VARCHAR(20), ALGORITHM=INSTANT;
INSERT INTO t1 VALUES (2, 'val1', 'val2');
FLUSH TABLES t1 FOR EXPORT;
UNLOCK TABLES;
CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 DISCARD TABLESPACE;
ALTER TABLE t2 IMPORT TABLESPACE;
ERROR HY000: Schema mismatch (CFG file is missing and source table is found to have row versions. CFG file is must to IMPORT tables with row versions.)
DROP TABLE t1;
DROP TABLE t2;
# Scenario 2: IMPORT with CFG file
CREATE TABLE t1 (id INT, str1 VARCHAR(20), index(str1));
INSERT INTO t1 VALUES (1, 'val1');
ALTER TABLE t1 ADD COLUMN str2 VARCHAR(20), ALGORITHM=INSTANT;
INSERT INTO t1 VALUES (2, 'val1', 'val2');
FLUSH TABLES t1 FOR EXPORT;
UNLOCK TABLES;
CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 DISCARD TABLESPACE;
ALTER TABLE t2 IMPORT TABLESPACE;
SELECT * FROM t2;
id str1 str2
1 val1 NULL
2 val1 val2
DROP TABLE t1;
DROP TABLE t2;
# Scenario 3: IMPORT without CFG file for another case
CREATE TABLE t1 (id INT, c1 VARCHAR(20), c2 VARCHAR(20));
INSERT INTO t1 VALUES (1, "1-c1", "1-c2");
ALTER TABLE t1 ADD COLUMN c3 VARCHAR(20), ALGORITHM=INSTANT;
INSERT INTO t1 VALUES (2, "2-c1", "2-c2", "2-c3");
ALTER TABLE t1 ADD COLUMN c4 VARCHAR(20) FIRST, ALGORITHM=INSTANT;
INSERT INTO t1 VALUES ("3-c4", 3, "3-c1", "3-c2", "3-c3");
ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT;
INSERT INTO t1 VALUES ("4-c4", 4, "4-c2", "4-c3");
ALTER TABLE t1 RENAME COLUMN c2 TO c22;
INSERT INTO t1 VALUES ("5-c4", 5, "5-c22", "5-c3");
SELECT * FROM t1;
c4 id c22 c3
NULL 1 1-c2 NULL
NULL 2 2-c2 2-c3
3-c4 3 3-c2 3-c3
4-c4 4 4-c2 4-c3
5-c4 5 5-c22 5-c3
FLUSH TABLES t1 FOR EXPORT;
UNLOCK TABLES;
CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 DISCARD TABLESPACE;
ALTER TABLE t2 IMPORT TABLESPACE;
ERROR HY000: Schema mismatch (CFG file is missing and source table is found to have row versions. CFG file is must to IMPORT tables with row versions.)
DROP TABLE t1;
DROP TABLE t2;
# Scenario 4: IMPORT with CFG file for another case
CREATE TABLE t1 (id INT, c1 VARCHAR(20), c2 VARCHAR(20));
INSERT INTO t1 VALUES (1, "1-c1", "1-c2");
ALTER TABLE t1 ADD COLUMN c3 VARCHAR(20), ALGORITHM=INSTANT;
INSERT INTO t1 VALUES (2, "2-c1", "2-c2", "2-c3");
ALTER TABLE t1 ADD COLUMN c4 VARCHAR(20) FIRST, ALGORITHM=INSTANT;
INSERT INTO t1 VALUES ("3-c4", 3, "3-c1", "3-c2", "3-c3");
ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT;
INSERT INTO t1 VALUES ("4-c4", 4, "4-c2", "4-c3");
ALTER TABLE t1 RENAME COLUMN c2 TO c22;
INSERT INTO t1 VALUES ("5-c4", 5, "5-c22", "5-c3");
SELECT * FROM t1;
c4 id c22 c3
NULL 1 1-c2 NULL
NULL 2 2-c2 2-c3
3-c4 3 3-c2 3-c3
4-c4 4 4-c2 4-c3
5-c4 5 5-c22 5-c3
FLUSH TABLES t1 FOR EXPORT;
UNLOCK TABLES;
CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 DISCARD TABLESPACE;
ALTER TABLE t2 IMPORT TABLESPACE;
SELECT * FROM t2;
c4 id c22 c3
NULL 1 1-c2 NULL
NULL 2 2-c2 2-c3
3-c4 3 3-c2 3-c3
4-c4 4 4-c2 4-c3
5-c4 5 5-c22 5-c3
DROP TABLE t1;
DROP TABLE t2;
|
