Debian Codegraph @

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

raw
1 #
2 # Bug #29627690 NOT ABLE TO IMPORT PARTITIONED TABLESPACE OLDER THAN 8.0.14
3 # After wl#13352, we no longer try to induce 8.0.14 behaviour by debug
4 # variable. innodb.partition_upgrade* tests checks import from 8.0.14.
5 # Keeping this test to run on current version.
6 select @@lower_case_table_names;
7 @@lower_case_table_names
8 1
9 # Test 1: With Hash partitions
10 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
11 PARTITION BY HASH (c1) PARTITIONS 3;
12 INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6);
13 SELECT c1 FROM t1 ORDER BY c1;
14 c1
15 1
16 2
17 3
18 4
19 5
20 6
21 FLUSH TABLES t1 FOR EXPORT;
22 backup: t1#p#p0
23 backup: t1#p#p1
24 backup: t1#p#p2
25 UNLOCK TABLES;
26 DROP TABLE t1;
27 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
28 PARTITION BY HASH (c1) PARTITIONS 3;
29 ALTER TABLE t1 DISCARD TABLESPACE;
30 restore: t1#p#p0 .ibd and .cfg files
31 restore: t1#p#p1 .ibd and .cfg files
32 restore: t1#p#p2 .ibd and .cfg files
33 ALTER TABLE t1 IMPORT TABLESPACE;
34 SELECT * FROM t1 ORDER BY c1;
35 c1
36 1
37 2
38 3
39 4
40 5
41 6
42 DROP TABLE t1;
43 # Test 2 With range partitions/subpartitions with mixed cases partiton names
44 create table t1 (f1 int ,f2 int) partition by range (f1)
45 subpartition by key (f2)
46 (
47 partition ABCdef values less than (10) (
48 subpartition SUB_ABCdef0,
49 subpartition SUB_abcdef1
50 ),
51 partition ABC values less than (50) (
52 subpartition SUB_ABC0,
53 subpartition SUB_abc1
54 ) ,
55 partition def values less than (100) (
56 subpartition sub_def0,
57 subpartition SUB_DEF1
58 ));
59 insert into t1 values (90,10) ,(40,78) ,(5,76);
60 SELECT * from t1 order by f1;
61 f1 f2
62 5 76
63 40 78
64 90 10
65 FLUSH TABLES t1 FOR EXPORT;
66 backup: t1#p#abcdef#sp#sub_abcdef0
67 backup: t1#p#abcdef#sp#sub_abcdef1
68 backup: t1#p#abc#sp#sub_abc0
69 backup: t1#p#abc#sp#sub_abc1
70 backup: t1#p#def#sp#sub_def0
71 backup: t1#p#def#sp#sub_def1
72 unlock tables;
73 drop table t1;
74 create table t1 (f1 int ,f2 int) partition by range (f1)
75 subpartition by key (f2)
76 (
77 partition ABCdef values less than (10) (
78 subpartition SUB_ABCdef0,
79 subpartition SUB_abcdef1
80 ),
81 partition ABC values less than (50) (
82 subpartition SUB_ABC0,
83 subpartition SUB_abc1
84 ) ,
85 partition def values less than (100) (
86 subpartition sub_def0,
87 subpartition SUB_DEF1
88 ));
89 ALTER TABLE t1 DISCARD TABLESPACE;
90 restore: t1#p#abcdef#sp#sub_abcdef0 .ibd and .cfg files
91 restore: t1#p#abcdef#sp#sub_abcdef1 .ibd and .cfg files
92 restore: t1#p#abc#sp#sub_abc0 .ibd and .cfg files
93 restore: t1#p#abc#sp#sub_abc1 .ibd and .cfg files
94 restore: t1#p#def#sp#sub_def0 .ibd and .cfg files
95 restore: t1#p#def#sp#sub_def1 .ibd and .cfg files
96 ALTER TABLE t1 IMPORT TABLESPACE;
97 SELECT * FROM t1 ORDER BY f1;
98 f1 f2
99 5 76
100 40 78
101 90 10
102 DROP TABLE t1;
103 # Test 3 : With encryption
104 # restart: --early-plugin-load=keyring_file=keyring_file.so --loose-keyring_file_data=MYSQL_TMP_DIR/mysecret_keyring --plugin-dir=KEYRING_PLUGIN_PATH
105 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB ENCRYPTION='Y'
106 PARTITION BY RANGE (c1) ( PARTITION ABC VALUES LESS THAN (10));
107 INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6);
108 SELECT c1 FROM t1 ORDER BY c1;
109 c1
110 1
111 2
112 3
113 4
114 5
115 6
116 FLUSH TABLES t1 FOR EXPORT;
117 backup: t1#p#abc
118 UNLOCK TABLES;
119 DROP TABLE t1;
120 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB ENCRYPTION='Y'
121 PARTITION BY RANGE (c1) ( PARTITION ABC VALUES LESS THAN (10));
122 ALTER TABLE t1 DISCARD TABLESPACE;
123 restore: t1#p#abc .ibd and .cfg files
124 ALTER TABLE t1 IMPORT TABLESPACE;
125 SELECT * FROM t1 ORDER BY c1;
126 c1
127 1
128 2
129 3
130 4
131 5
132 6
133 DROP TABLE t1;
134 # Restarting server without keyring to restore server state
135 # restart:
136 Test 4 : Missing cfg file
137 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
138 PARTITION BY RANGE (c1) ( PARTITION ABCD VALUES LESS THAN (10));
139 INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(8);
140 SELECT c1 FROM t1 ORDER BY c1;
141 c1
142 1
143 2
144 3
145 4
146 5
147 8
148 FLUSH TABLES t1 FOR EXPORT;
149 UNLOCK TABLES;
150 DROP TABLE t1;
151 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
152 PARTITION BY RANGE (c1) ( PARTITION ABCD VALUES LESS THAN (10));
153 ALTER TABLE t1 DISCARD TABLESPACE;
154 restore: t1#p#abcd .ibd file
155 ALTER TABLE t1 IMPORT TABLESPACE;
156 Warnings:
157 Warning 1810 InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t1#p#abcd.cfg', will attempt to import without schema verification
158 SELECT * FROM t1 ORDER BY c1;
159 c1
160 1
161 2
162 3
163 4
164 5
165 8
166 DROP TABLE t1;
167 Test 5:-
168 # 1. Create table in 8.0.13 version
169 # 2. Upgrade the table to 8.0.15 version
170 # 3. Add another partition (PART_two)
171 # 4. Do flush table and generate cfg files
172 # 5. These ibd and cfg files are present in 8.0.15_cfg.zip
173 # 6. Do import from the cfg file and ibd from 8.0.15_cfg.zip.
174 CREATE DATABASE db1;
175 use db1;
176 CREATE TABLE t1 (
177 f1 int
178 ) ENGINE=InnoDB
179 PARTITION BY RANGE (f1)
180 (PARTITION PART_one VALUES LESS THAN (10),
181 PARTITION PART_two VALUES LESS THAN (20));
182 ALTER TABLE t1 DISCARD TABLESPACE;
183 ALTER TABLE t1 IMPORT TABLESPACE;
184 SELECT * from t1 ORDER BY f1;
185 f1
186 9
187 19
188 ALTER TABLE t1 FORCE;
189 SELECT * from t1 ORDER BY f1;
190 f1
191 9
192 19
193 DROP TABLE t1;
194 Test 6:-
195 # 1. Create table in 8.0.13 version
196 # 2. Upgrade the table to 8.0.15 version
197 # 3. Add another partition (PART_two)
198 # 4. Upgrade to 8.0.17 versiosn
199 # 5. Add another partition (PART_three)
200 # 6. Do flush table and generate cfg files
201 # 7. These ibd and cfg files are present in 8.0.17_cfg.zip
202 # 6. Do import from the cfg file and ibd from 8.0.17_cfg.zip
203 use db1;
204 CREATE TABLE t1 (
205 f1 int
206 ) ENGINE=InnoDB
207 PARTITION BY RANGE (f1)
208 (PARTITION PART_one VALUES LESS THAN (10),
209 PARTITION PART_two VALUES LESS THAN (20),
210 PARTITION PART_three VALUES LESS THAN (30));
211 ALTER TABLE t1 DISCARD TABLESPACE;
212 ALTER TABLE t1 IMPORT TABLESPACE;
213 SELECT * from t1 ORDER BY f1;
214 f1
215 9
216 19
217 29
218 ALTER TABLE t1 FORCE;
219 SELECT * from t1 ORDER BY f1;
220 f1
221 9
222 19
223 29
224 DROP TABLE t1;
225 DROP DATABASE db1;