Debian Codegraph @

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

raw
1 #
2 # Bug#20977779 CANNOT IMPORT TABLES CONTAINING PREFIX INDEXES
3 #
4 CREATE TABLE t1 (c1 VARCHAR(32), c2 VARCHAR(32), c3 VARCHAR(32),
5 PRIMARY KEY (c1, c2, c3))
6 ENGINE=InnoDB;
7 ALTER TABLE t1 ADD INDEX ind1(c1(5), c2, c3);
8 ALTER TABLE t1 ADD INDEX ind2(c3, c1(10), c2);
9 ALTER TABLE t1 ADD INDEX ind3(c2, c3, c1(20));
10 INSERT INTO t1 VALUES ('Test Data -1', 'Test Data -2', 'Test Data -3');
11 # Test with 2ndary index having prefix
12 FLUSH TABLES test.t1 FOR EXPORT;
13 UNLOCK TABLES;
14 ALTER TABLE test.t1 DISCARD TABLESPACE;
15 ALTER TABLE test.t1 IMPORT TABLESPACE;
16 CHECK TABLE test.t1;
17 Table Op Msg_type Msg_text
18 test.t1 check status OK
19 SHOW CREATE TABLE test.t1;
20 Table Create Table
21 t1 CREATE TABLE `t1` (
22 `c1` varchar(32) NOT NULL,
23 `c2` varchar(32) NOT NULL,
24 `c3` varchar(32) NOT NULL,
25 PRIMARY KEY (`c1`,`c2`,`c3`),
26 KEY `ind1` (`c1`(5),`c2`,`c3`),
27 KEY `ind2` (`c3`,`c1`(10),`c2`),
28 KEY `ind3` (`c2`,`c3`,`c1`(20))
29 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
30 SELECT * FROM test.t1;
31 c1 c2 c3
32 Test Data -1 Test Data -2 Test Data -3
33 # Test with PK & 2ndary index with prefix
34 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(c1(5), c2(10), c3(20));
35 FLUSH TABLES test.t1 FOR EXPORT;
36 UNLOCK TABLES;
37 ALTER TABLE test.t1 DISCARD TABLESPACE;
38 ALTER TABLE test.t1 IMPORT TABLESPACE;
39 CHECK TABLE test.t1;
40 Table Op Msg_type Msg_text
41 test.t1 check status OK
42 SHOW CREATE TABLE test.t1;
43 Table Create Table
44 t1 CREATE TABLE `t1` (
45 `c1` varchar(32) NOT NULL,
46 `c2` varchar(32) NOT NULL,
47 `c3` varchar(32) NOT NULL,
48 PRIMARY KEY (`c1`(5),`c2`(10),`c3`(20)),
49 KEY `ind1` (`c1`(5),`c2`,`c3`),
50 KEY `ind2` (`c3`,`c1`(10),`c2`),
51 KEY `ind3` (`c2`,`c3`,`c1`(20))
52 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
53 SELECT * FROM test.t1;
54 c1 c2 c3
55 Test Data -1 Test Data -2 Test Data -3
56 DROP TABLE t1;
57 #
58 # Bug#30561144 IMPORT TABLESPACE FAILS: ERROR 1808 (HY000): SCHEMA MISMATCH
59 #
60 CREATE TABLE t1(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY);
61 ALTER TABLE t1 ADD COLUMN v VARCHAR(255) DEFAULT "default_value", ALGORITHM=INSTANT;
62 # Insert some data into t1
63 SELECT * from t1 limit 10;
64 id v
65 1 aaaaaaaaaaaaa
66 2 aaaaaaaaaaaaa
67 3 aaaaaaaaaaaaa
68 4 aaaaaaaaaaaaa
69 5 aaaaaaaaaaaaa
70 6 aaaaaaaaaaaaa
71 7 aaaaaaaaaaaaa
72 8 aaaaaaaaaaaaa
73 9 aaaaaaaaaaaaa
74 10 aaaaaaaaaaaaa
75 SELECT COUNT(*) from t1;
76 COUNT(*)
77 1000
78 # Flush the table and store CFG/IBD files to temp
79 FLUSH TABLE t1 FOR EXPORT;
80 UNLOCK TABLES;
81 #
82 # 1) Both tables contain INSTANT column, with matching default values.
83 #
84 CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY);
85 ALTER TABLE t2 ADD COLUMN v VARCHAR(255) DEFAULT "default_value", ALGORITHM=INSTANT;
86 ALTER TABLE t2 DISCARD TABLESPACE;
87 # Copy CFG/IBD file from temp
88 # IMPORT should succeed now
89 ALTER TABLE t2 IMPORT TABLESPACE;
90 SELECT * from t2 limit 10;
91 id v
92 1 aaaaaaaaaaaaa
93 2 aaaaaaaaaaaaa
94 3 aaaaaaaaaaaaa
95 4 aaaaaaaaaaaaa
96 5 aaaaaaaaaaaaa
97 6 aaaaaaaaaaaaa
98 7 aaaaaaaaaaaaa
99 8 aaaaaaaaaaaaa
100 9 aaaaaaaaaaaaa
101 10 aaaaaaaaaaaaa
102 DROP TABLE t2;
103 #
104 # 2) Both tables contain INSTANT column, with conflicting default values.
105 #
106 CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY);
107 ALTER TABLE t2 ADD COLUMN v VARCHAR(255) DEFAULT "different_value", ALGORITHM=INSTANT;
108 ALTER TABLE t2 DISCARD TABLESPACE;
109 # Copy CFG/IBD file from temp
110 # IMPORT should fail because of the conflicting values
111 ALTER TABLE t2 IMPORT TABLESPACE;
112 ERROR HY000: Schema mismatch (The metadata in the data dictionary and the .cfg file contain different default values for column v!)
113 # Manually remove tablespace files, since import failed drop won't delete them.
114 # Cleanup
115 DROP TABLE t2;
116 DROP TABLE t1;
117 #
118 # Bug #30830441 ALTER TABLE IMPORT TABLESPACE FAILS AFTER A SERVER RESTART
119 #
120 CREATE TABLE t1 ( id int );
121 INSERT INTO t1 values (1),(2),(3);
122 FLUSH TABLES t1 FOR EXPORT;
123 UNLOCK TABLES;
124 # restart
125 ALTER TABLE t1 DISCARD TABLESPACE;
126 ALTER TABLE t1 IMPORT TABLESPACE;
127 DROP TABLE t1;