Debian Codegraph @

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

raw
1 # ------------------------------------------------------------
2 # Exported table t1 (in older version) having
3 # c1 INT, c2 INT, c3 INT, c4 TEXT normal columns
4 # c5 char(20) INSTANT ADD column with default def_c5.
5 # +------+------+------+---------+--------+
6 # | c1 | c2 | c3 | c4 | c5 |
7 # +------+------+------+---------+--------+
8 # | 1 | 2 | 3 | abcdefg | c5_def |
9 # | 100 | 200 | 300 | qwerty | c5_def |
10 # | 200 | 300 | 400 | asdfg | c5_def |
11 # | 300 | 400 | 500 | xxxxxx | r4c5 |
12 # +------+------+------+---------+--------+
13 # ------------------------------------------------------------
14 # Copy and unzip the ibd/cfg files.
15 # listing MYSQL_TMP_DIR/old_instant_part_comp
16 t1#p#p0.cfg
17 t1#p#p0.ibd
18 t1#p#p1.cfg
19 t1#p#p1.ibd
20 t1#p#p2.cfg
21 t1#p#p2.ibd
22 t1#p#p3.cfg
23 t1#p#p3.ibd
24 # ------------------------------------------------------------
25 # Scenario 1 : No INSTANT ADD/DROP Columns in target table
26 # ------------------------------------------------------------
27 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT, c5 char(20)) row_format=compact
28 PARTITION BY RANGE(c1 * 2)
29 (PARTITION p0 VALUES LESS THAN (128),
30 PARTITION p1 VALUES LESS THAN (256),
31 PARTITION p2 VALUES LESS THAN (384),
32 PARTITION p3 VALUES LESS THAN MAXVALUE);
33 # Metadata from INFORMATION_SCHEMA.TABLES
34 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
35 test/t1#p#p0 8 0 0
36 # Metadata from INFORMATION_SCHEMA.COLUMNS
37 NAME POS MTYPE PRTYPE HAS_DEFAULT
38 c1 0 6 1027 0
39 c2 1 6 1027 0
40 c3 2 6 1027 0
41 c4 3 5 16711932 0
42 c5 4 13 16711934 0
43 # Metadata from INFORMATION_SCHEMA.TABLES
44 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
45 test/t1#p#p1 8 0 0
46 # Metadata from INFORMATION_SCHEMA.COLUMNS
47 NAME POS MTYPE PRTYPE HAS_DEFAULT
48 c1 0 6 1027 0
49 c2 1 6 1027 0
50 c3 2 6 1027 0
51 c4 3 5 16711932 0
52 c5 4 13 16711934 0
53 # Metadata from INFORMATION_SCHEMA.TABLES
54 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
55 test/t1#p#p2 8 0 0
56 # Metadata from INFORMATION_SCHEMA.COLUMNS
57 NAME POS MTYPE PRTYPE HAS_DEFAULT
58 c1 0 6 1027 0
59 c2 1 6 1027 0
60 c3 2 6 1027 0
61 c4 3 5 16711932 0
62 c5 4 13 16711934 0
63 # Metadata from INFORMATION_SCHEMA.TABLES
64 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
65 test/t1#p#p3 8 0 0
66 # Metadata from INFORMATION_SCHEMA.COLUMNS
67 NAME POS MTYPE PRTYPE HAS_DEFAULT
68 c1 0 6 1027 0
69 c2 1 6 1027 0
70 c3 2 6 1027 0
71 c4 3 5 16711932 0
72 c5 4 13 16711934 0
73 # IMPORT
74 ALTER TABLE t1 discard tablespace;
75 ALTER TABLE t1 IMPORT TABLESPACE;
76 SELECT * FROM t1 ORDER BY c1;
77 c1 c2 c3 c4 c5
78 1 2 3 abcdefg c5_def
79 100 200 300 qwerty c5_def
80 200 300 400 asdfg c5_def
81 300 400 500 xxxxxx r4c5
82 # Metadata from INFORMATION_SCHEMA.TABLES
83 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
84 test/t1#p#p0 8 4 0
85 # Metadata from INFORMATION_SCHEMA.COLUMNS
86 NAME POS MTYPE PRTYPE HAS_DEFAULT
87 c1 0 6 1027 0
88 c2 1 6 1027 0
89 c3 2 6 1027 0
90 c4 3 5 16711932 0
91 c5 4 13 16711934 1
92 # Metadata from INFORMATION_SCHEMA.TABLES
93 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
94 test/t1#p#p1 8 4 0
95 # Metadata from INFORMATION_SCHEMA.COLUMNS
96 NAME POS MTYPE PRTYPE HAS_DEFAULT
97 c1 0 6 1027 0
98 c2 1 6 1027 0
99 c3 2 6 1027 0
100 c4 3 5 16711932 0
101 c5 4 13 16711934 1
102 # Metadata from INFORMATION_SCHEMA.TABLES
103 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
104 test/t1#p#p2 8 4 0
105 # Metadata from INFORMATION_SCHEMA.COLUMNS
106 NAME POS MTYPE PRTYPE HAS_DEFAULT
107 c1 0 6 1027 0
108 c2 1 6 1027 0
109 c3 2 6 1027 0
110 c4 3 5 16711932 0
111 c5 4 13 16711934 1
112 # Metadata from INFORMATION_SCHEMA.TABLES
113 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
114 test/t1#p#p3 8 4 0
115 # Metadata from INFORMATION_SCHEMA.COLUMNS
116 NAME POS MTYPE PRTYPE HAS_DEFAULT
117 c1 0 6 1027 0
118 c2 1 6 1027 0
119 c3 2 6 1027 0
120 c4 3 5 16711932 0
121 c5 4 13 16711934 1
122 # Cleanup
123 DROP TABLE t1;
124 # ------------------------------------------------------------
125 # Scenario 2 : No INSTANT ADD/DROP. Column mismatch
126 # ------------------------------------------------------------
127 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT, c5 char(10)) row_format=compact
128 PARTITION BY RANGE(c1 * 2)
129 (PARTITION p0 VALUES LESS THAN (128),
130 PARTITION p1 VALUES LESS THAN (256),
131 PARTITION p2 VALUES LESS THAN (384),
132 PARTITION p3 VALUES LESS THAN MAXVALUE);
133 # Metadata from INFORMATION_SCHEMA.TABLES
134 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
135 test/t1#p#p0 8 0 0
136 # Metadata from INFORMATION_SCHEMA.COLUMNS
137 NAME POS MTYPE PRTYPE HAS_DEFAULT
138 c1 0 6 1027 0
139 c2 1 6 1027 0
140 c3 2 6 1027 0
141 c4 3 5 16711932 0
142 c5 4 13 16711934 0
143 # Metadata from INFORMATION_SCHEMA.TABLES
144 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
145 test/t1#p#p1 8 0 0
146 # Metadata from INFORMATION_SCHEMA.COLUMNS
147 NAME POS MTYPE PRTYPE HAS_DEFAULT
148 c1 0 6 1027 0
149 c2 1 6 1027 0
150 c3 2 6 1027 0
151 c4 3 5 16711932 0
152 c5 4 13 16711934 0
153 # Metadata from INFORMATION_SCHEMA.TABLES
154 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
155 test/t1#p#p2 8 0 0
156 # Metadata from INFORMATION_SCHEMA.COLUMNS
157 NAME POS MTYPE PRTYPE HAS_DEFAULT
158 c1 0 6 1027 0
159 c2 1 6 1027 0
160 c3 2 6 1027 0
161 c4 3 5 16711932 0
162 c5 4 13 16711934 0
163 # Metadata from INFORMATION_SCHEMA.TABLES
164 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
165 test/t1#p#p3 8 0 0
166 # Metadata from INFORMATION_SCHEMA.COLUMNS
167 NAME POS MTYPE PRTYPE HAS_DEFAULT
168 c1 0 6 1027 0
169 c2 1 6 1027 0
170 c3 2 6 1027 0
171 c4 3 5 16711932 0
172 c5 4 13 16711934 0
173 # IMPORT
174 ALTER TABLE t1 discard tablespace;
175 ALTER TABLE t1 IMPORT TABLESPACE;
176 ERROR HY000: Schema mismatch (Column c5 length mismatch.)
177 # Cleanup
178 DROP TABLE t1;
179 # ------------------------------------------------------------
180 # Scenario 3 : INSTANT ADD/DROP Columns in target table
181 # ------------------------------------------------------------
182 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) row_format=compact
183 PARTITION BY RANGE(c1 * 2)
184 (PARTITION p0 VALUES LESS THAN (128),
185 PARTITION p1 VALUES LESS THAN (256),
186 PARTITION p2 VALUES LESS THAN (384),
187 PARTITION p3 VALUES LESS THAN MAXVALUE);
188 ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT "c5_def", ALGORITHM=INSTANT;
189 # Metadata from INFORMATION_SCHEMA.TABLES
190 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
191 test/t1#p#p0 8 0 1
192 # Metadata from INFORMATION_SCHEMA.COLUMNS
193 NAME POS MTYPE PRTYPE HAS_DEFAULT
194 c1 0 6 1027 0
195 c2 1 6 1027 0
196 c3 2 6 1027 0
197 c4 3 5 16711932 0
198 c5 4 13 16711934 1
199 # Metadata from INFORMATION_SCHEMA.TABLES
200 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
201 test/t1#p#p1 8 0 1
202 # Metadata from INFORMATION_SCHEMA.COLUMNS
203 NAME POS MTYPE PRTYPE HAS_DEFAULT
204 c1 0 6 1027 0
205 c2 1 6 1027 0
206 c3 2 6 1027 0
207 c4 3 5 16711932 0
208 c5 4 13 16711934 1
209 # Metadata from INFORMATION_SCHEMA.TABLES
210 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
211 test/t1#p#p2 8 0 1
212 # Metadata from INFORMATION_SCHEMA.COLUMNS
213 NAME POS MTYPE PRTYPE HAS_DEFAULT
214 c1 0 6 1027 0
215 c2 1 6 1027 0
216 c3 2 6 1027 0
217 c4 3 5 16711932 0
218 c5 4 13 16711934 1
219 # Metadata from INFORMATION_SCHEMA.TABLES
220 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
221 test/t1#p#p3 8 0 1
222 # Metadata from INFORMATION_SCHEMA.COLUMNS
223 NAME POS MTYPE PRTYPE HAS_DEFAULT
224 c1 0 6 1027 0
225 c2 1 6 1027 0
226 c3 2 6 1027 0
227 c4 3 5 16711932 0
228 c5 4 13 16711934 1
229 # IMPORT
230 ALTER TABLE t1 discard tablespace;
231 ALTER TABLE t1 IMPORT TABLESPACE;
232 ERROR HY000: Schema mismatch (Target table has INSTANT columns but the .cfg file is from earlier release with INSTANT column in the source table. Instant metadata can't match. Please create target table with no INSTANT column and try IMPORT.)
233 # Cleanup
234 DROP TABLE t1;
235 # ------------------------------------------------------------
236 # Scenario 4 : INSTANT ADD/DROP. Column mismatch
237 # ------------------------------------------------------------
238 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) row_format=compact
239 PARTITION BY RANGE(c1 * 2)
240 (PARTITION p0 VALUES LESS THAN (128),
241 PARTITION p1 VALUES LESS THAN (256),
242 PARTITION p2 VALUES LESS THAN (384),
243 PARTITION p3 VALUES LESS THAN MAXVALUE);
244 ALTER TABLE t1 ADD COLUMN c5 char(10) DEFAULT "c5_def", ALGORITHM=INSTANT;
245 # Metadata from INFORMATION_SCHEMA.TABLES
246 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
247 test/t1#p#p0 8 0 1
248 # Metadata from INFORMATION_SCHEMA.COLUMNS
249 NAME POS MTYPE PRTYPE HAS_DEFAULT
250 c1 0 6 1027 0
251 c2 1 6 1027 0
252 c3 2 6 1027 0
253 c4 3 5 16711932 0
254 c5 4 13 16711934 1
255 # Metadata from INFORMATION_SCHEMA.TABLES
256 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
257 test/t1#p#p1 8 0 1
258 # Metadata from INFORMATION_SCHEMA.COLUMNS
259 NAME POS MTYPE PRTYPE HAS_DEFAULT
260 c1 0 6 1027 0
261 c2 1 6 1027 0
262 c3 2 6 1027 0
263 c4 3 5 16711932 0
264 c5 4 13 16711934 1
265 # Metadata from INFORMATION_SCHEMA.TABLES
266 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
267 test/t1#p#p2 8 0 1
268 # Metadata from INFORMATION_SCHEMA.COLUMNS
269 NAME POS MTYPE PRTYPE HAS_DEFAULT
270 c1 0 6 1027 0
271 c2 1 6 1027 0
272 c3 2 6 1027 0
273 c4 3 5 16711932 0
274 c5 4 13 16711934 1
275 # Metadata from INFORMATION_SCHEMA.TABLES
276 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
277 test/t1#p#p3 8 0 1
278 # Metadata from INFORMATION_SCHEMA.COLUMNS
279 NAME POS MTYPE PRTYPE HAS_DEFAULT
280 c1 0 6 1027 0
281 c2 1 6 1027 0
282 c3 2 6 1027 0
283 c4 3 5 16711932 0
284 c5 4 13 16711934 1
285 # IMPORT
286 ALTER TABLE t1 discard tablespace;
287 ALTER TABLE t1 IMPORT TABLESPACE;
288 ERROR HY000: Schema mismatch (Column c5 length mismatch.)
289 # Cleanup
290 DROP TABLE t1;
291 # ------------------------------------------------------------
292 # Scenario 5 : INSTANT ADD/DROP. Default value mismatch
293 # ------------------------------------------------------------
294 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) row_format=compact
295 PARTITION BY RANGE(c1 * 2)
296 (PARTITION p0 VALUES LESS THAN (128),
297 PARTITION p1 VALUES LESS THAN (256),
298 PARTITION p2 VALUES LESS THAN (384),
299 PARTITION p3 VALUES LESS THAN MAXVALUE);
300 ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT "def_c5", ALGORITHM=INSTANT;
301 # Metadata from INFORMATION_SCHEMA.TABLES
302 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
303 test/t1#p#p0 8 0 1
304 # Metadata from INFORMATION_SCHEMA.COLUMNS
305 NAME POS MTYPE PRTYPE HAS_DEFAULT
306 c1 0 6 1027 0
307 c2 1 6 1027 0
308 c3 2 6 1027 0
309 c4 3 5 16711932 0
310 c5 4 13 16711934 1
311 # Metadata from INFORMATION_SCHEMA.TABLES
312 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
313 test/t1#p#p1 8 0 1
314 # Metadata from INFORMATION_SCHEMA.COLUMNS
315 NAME POS MTYPE PRTYPE HAS_DEFAULT
316 c1 0 6 1027 0
317 c2 1 6 1027 0
318 c3 2 6 1027 0
319 c4 3 5 16711932 0
320 c5 4 13 16711934 1
321 # Metadata from INFORMATION_SCHEMA.TABLES
322 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
323 test/t1#p#p2 8 0 1
324 # Metadata from INFORMATION_SCHEMA.COLUMNS
325 NAME POS MTYPE PRTYPE HAS_DEFAULT
326 c1 0 6 1027 0
327 c2 1 6 1027 0
328 c3 2 6 1027 0
329 c4 3 5 16711932 0
330 c5 4 13 16711934 1
331 # Metadata from INFORMATION_SCHEMA.TABLES
332 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
333 test/t1#p#p3 8 0 1
334 # Metadata from INFORMATION_SCHEMA.COLUMNS
335 NAME POS MTYPE PRTYPE HAS_DEFAULT
336 c1 0 6 1027 0
337 c2 1 6 1027 0
338 c3 2 6 1027 0
339 c4 3 5 16711932 0
340 c5 4 13 16711934 1
341 # IMPORT
342 ALTER TABLE t1 discard tablespace;
343 ALTER TABLE t1 IMPORT TABLESPACE;
344 ERROR HY000: Schema mismatch (Target table has INSTANT columns but the .cfg file is from earlier release with INSTANT column in the source table. Instant metadata can't match. Please create target table with no INSTANT column and try IMPORT.)
345 # Cleanup
346 DROP TABLE t1;
347 # --------------------------------------------------------------
348 # Scenario 6 : Table with INSTANT ADD and reorganized partitions
349 # --------------------------------------------------------------
350 # Copy and unzip the ibd/cfg files.
351 # listing MYSQL_TMP_DIR/old_instant_reorg_part_comp
352 t1#p#p0.cfg
353 t1#p#p0.ibd
354 t1#p#p1_1.cfg
355 t1#p#p1_1.ibd
356 t1#p#p1_2.cfg
357 t1#p#p1_2.ibd
358 t1#p#p2.cfg
359 t1#p#p2.ibd
360 CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT KEY, c1 char(10), c2 char(10), c3 char(10))
361 PARTITION BY RANGE(id) (
362 PARTITION p0 VALUES LESS THAN (100),
363 PARTITION p1_1 VALUES LESS THAN (150),
364 PARTITION p1_2 VALUES LESS THAN (200),
365 PARTITION p2 VALUES LESS THAN MAXVALUE);
366 # Metadata from INFORMATION_SCHEMA.TABLES
367 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
368 test/t1#p#p0 7 0 0
369 # Metadata from INFORMATION_SCHEMA.COLUMNS
370 NAME POS MTYPE PRTYPE HAS_DEFAULT
371 id 0 6 1283 0
372 c1 1 13 16711934 0
373 c2 2 13 16711934 0
374 c3 3 13 16711934 0
375 # Metadata from INFORMATION_SCHEMA.TABLES
376 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
377 test/t1#p#p1_1 7 0 0
378 # Metadata from INFORMATION_SCHEMA.COLUMNS
379 NAME POS MTYPE PRTYPE HAS_DEFAULT
380 id 0 6 1283 0
381 c1 1 13 16711934 0
382 c2 2 13 16711934 0
383 c3 3 13 16711934 0
384 # Metadata from INFORMATION_SCHEMA.TABLES
385 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
386 test/t1#p#p1_2 7 0 0
387 # Metadata from INFORMATION_SCHEMA.COLUMNS
388 NAME POS MTYPE PRTYPE HAS_DEFAULT
389 id 0 6 1283 0
390 c1 1 13 16711934 0
391 c2 2 13 16711934 0
392 c3 3 13 16711934 0
393 # Metadata from INFORMATION_SCHEMA.TABLES
394 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
395 test/t1#p#p2 7 0 0
396 # Metadata from INFORMATION_SCHEMA.COLUMNS
397 NAME POS MTYPE PRTYPE HAS_DEFAULT
398 id 0 6 1283 0
399 c1 1 13 16711934 0
400 c2 2 13 16711934 0
401 c3 3 13 16711934 0
402 # IMPORT
403 ALTER TABLE t1 discard tablespace;
404 ALTER TABLE t1 IMPORT TABLESPACE;
405 SELECT * FROM t1 ORDER BY c1;
406 id c1 c2 c3
407 1 r1c1 r1c2 c3_def
408 2 r2c1 r2c2 r2c3
409 # Metadata from INFORMATION_SCHEMA.TABLES
410 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
411 test/t1#p#p0 7 3 0
412 # Metadata from INFORMATION_SCHEMA.COLUMNS
413 NAME POS MTYPE PRTYPE HAS_DEFAULT
414 id 0 6 1283 0
415 c1 1 13 16711934 0
416 c2 2 13 16711934 0
417 c3 3 13 16711934 1
418 # Metadata from INFORMATION_SCHEMA.TABLES
419 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
420 test/t1#p#p1_1 7 0 0
421 # Metadata from INFORMATION_SCHEMA.COLUMNS
422 NAME POS MTYPE PRTYPE HAS_DEFAULT
423 id 0 6 1283 0
424 c1 1 13 16711934 0
425 c2 2 13 16711934 0
426 c3 3 13 16711934 0
427 # Metadata from INFORMATION_SCHEMA.TABLES
428 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
429 test/t1#p#p1_2 7 0 0
430 # Metadata from INFORMATION_SCHEMA.COLUMNS
431 NAME POS MTYPE PRTYPE HAS_DEFAULT
432 id 0 6 1283 0
433 c1 1 13 16711934 0
434 c2 2 13 16711934 0
435 c3 3 13 16711934 0
436 # Metadata from INFORMATION_SCHEMA.TABLES
437 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
438 test/t1#p#p2 7 3 0
439 # Metadata from INFORMATION_SCHEMA.COLUMNS
440 NAME POS MTYPE PRTYPE HAS_DEFAULT
441 id 0 6 1283 0
442 c1 1 13 16711934 0
443 c2 2 13 16711934 0
444 c3 3 13 16711934 1
445 # Cleanup
446 DROP TABLE t1;
447 ###########
448 # CLEANUP #
449 ###########