Debian Codegraph @

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

raw
1 #
2 # Bug#30128418: IMPORT TABLESPACE MUST CHECK DESCENDING INDEX DEFINITION
3 #
4 ###
5 ### EXPORT #1: Backup Ascending key IBD and v3 CFG
6 ###
7 # Create a table with a normal ascending secondary key and export it.
8 CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b));
9 INSERT INTO t1 VALUES (1, 1);
10 INSERT INTO t1 VALUES (2, 2);
11 SELECT * FROM t1;
12 a b
13 1 1
14 2 2
15 SELECT * FROM t1 order by a;
16 a b
17 1 1
18 2 2
19 SELECT * FROM t1 order by b;
20 a b
21 1 1
22 2 2
23 SHOW CREATE TABLE t1;
24 Table Create Table
25 t1 CREATE TABLE `t1` (
26 `a` int NOT NULL,
27 `b` int DEFAULT NULL,
28 PRIMARY KEY (`a`),
29 KEY `b` (`b`)
30 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
31 # Export the table using IB_EXPORT_CFG_VERSION_V3,
32 # which does not export the DESC flag.
33 SET GLOBAL DEBUG='+d,ib_export_use_cfg_version_3';
34 FLUSH TABLES t1 FOR EXPORT;
35 SET GLOBAL DEBUG='-d,ib_export_use_cfg_version_3';
36 # Back-up the IBD and the v3 CFG file
37 # Unlock the table, which deletes the CFG file.
38 UNLOCK TABLES;
39 ###
40 ### EXPORT #2: Backup v4 CFG for Ascending key IBD
41 ###
42 # Export the table using IB_EXPORT_CFG_VERSION_V4,
43 # which will export the DESC flag on the secondary index.
44 FLUSH TABLES t1 FOR EXPORT;
45 # Back-up the v4 CFG file
46 UNLOCK TABLES;
47 DROP TABLE t1;
48 ###
49 ### EXPORT #3: Backup Descending key IBD and v3 CFG
50 ###
51 # Create a table with a descending secondary key and export it.
52 CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b DESC));
53 INSERT INTO t1 VALUES (1, 1);
54 INSERT INTO t1 VALUES (2, 2);
55 SELECT * FROM t1;
56 a b
57 1 1
58 2 2
59 SELECT * FROM t1 order by a;
60 a b
61 1 1
62 2 2
63 SELECT * FROM t1 order by b;
64 a b
65 1 1
66 2 2
67 SHOW CREATE TABLE t1;
68 Table Create Table
69 t1 CREATE TABLE `t1` (
70 `a` int NOT NULL,
71 `b` int DEFAULT NULL,
72 PRIMARY KEY (`a`),
73 KEY `b` (`b` DESC)
74 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
75 # Export the table using IB_EXPORT_CFG_VERSION_V3,
76 # which does not export the DESC flag.
77 SET GLOBAL DEBUG='+d,ib_export_use_cfg_version_3';
78 FLUSH TABLES t1 FOR EXPORT;
79 SET GLOBAL DEBUG='-d,ib_export_use_cfg_version_3';
80 # Back-up the IBD and the v3 CFG file
81 # Unlock the table, which deletes the CFG file.
82 UNLOCK TABLES;
83 ###
84 ### EXPORT #4: Backup v4 CFG for Descending key IBD
85 ###
86 # Export the table using IB_EXPORT_CFG_VERSION_V4,
87 # which will export the DESC flag on the secondary index.
88 FLUSH TABLES t1 FOR EXPORT;
89 # Back-up the v4 CFG with the descending key.
90 # Unlock the table, which deletes the cfg file.
91 UNLOCK TABLES;
92 ###
93 ### EXPORT #5: Backup with a future version 99 CFG
94 ###
95 # Export the table using a future version IB_EXPORT_CFG_VERSION_V99,
96 SET GLOBAL DEBUG='+d,ib_export_use_cfg_version_99';
97 FLUSH TABLES t1 FOR EXPORT;
98 SET GLOBAL DEBUG='-d,ib_export_use_cfg_version_99';
99 # Back-up the v99 CFG with the descending key.
100 # Unlock the table, which deletes the cfg file.
101 UNLOCK TABLES;
102 ###
103 ### IMPORT TEST #1: Ascending key IBD and v3 CFG to Discarded Ascending Key IBD
104 ###
105 # Recreate the table without the DESC attribute on the secondary key.
106 DROP TABLE t1;
107 CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b));
108 SHOW CREATE TABLE t1;
109 Table Create Table
110 t1 CREATE TABLE `t1` (
111 `a` int NOT NULL,
112 `b` int DEFAULT NULL,
113 PRIMARY KEY (`a`),
114 KEY `b` (`b`)
115 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
116 # Discard the table which deletes the IBD file.
117 ALTER TABLE t1 DISCARD TABLESPACE;
118 # Restore the ascending key IBD and the v3 version of the CFG.
119 # Import the table. Since both the discarded IBD and the imported
120 # IBD match, the import succeeds.
121 ALTER TABLE t1 IMPORT TABLESPACE;
122 # Show that column b is sorted correctly.
123 SELECT * FROM t1;
124 a b
125 1 1
126 2 2
127 SELECT * FROM t1 order by a;
128 a b
129 1 1
130 2 2
131 SELECT * FROM t1 order by b;
132 a b
133 1 1
134 2 2
135 INSERT INTO t1 VALUES (3, 3);
136 INSERT INTO t1 VALUES (4, 4);
137 SELECT * FROM t1;
138 a b
139 1 1
140 2 2
141 3 3
142 4 4
143 SELECT * FROM t1 order by a;
144 a b
145 1 1
146 2 2
147 3 3
148 4 4
149 SELECT * FROM t1 order by b;
150 a b
151 1 1
152 2 2
153 3 3
154 4 4
155 DROP TABLE t1;
156 ###
157 ### IMPORT TEST #2: Ascending key IBD and v4 CFG to Discarded Ascending Key IBD
158 ###
159 # Recreate the table without the DESC attribute on the secondary key.
160 CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b));
161 SHOW CREATE TABLE t1;
162 Table Create Table
163 t1 CREATE TABLE `t1` (
164 `a` int NOT NULL,
165 `b` int DEFAULT NULL,
166 PRIMARY KEY (`a`),
167 KEY `b` (`b`)
168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
169 # Discard the table which deletes the IBD file.
170 ALTER TABLE t1 DISCARD TABLESPACE;
171 # Restore the ascending key IBD and the v4 version of the CFG.
172 # Import the table. Since both the discarded IBD and the imported
173 # IBD match, the import succeeds.
174 ALTER TABLE t1 IMPORT TABLESPACE;
175 # Show that column b is sorted correctly.
176 SELECT * FROM t1;
177 a b
178 1 1
179 2 2
180 SELECT * FROM t1 order by a;
181 a b
182 1 1
183 2 2
184 SELECT * FROM t1 order by b;
185 a b
186 1 1
187 2 2
188 INSERT INTO t1 VALUES (3, 3);
189 INSERT INTO t1 VALUES (4, 4);
190 SELECT * FROM t1;
191 a b
192 1 1
193 2 2
194 3 3
195 4 4
196 SELECT * FROM t1 order by a;
197 a b
198 1 1
199 2 2
200 3 3
201 4 4
202 SELECT * FROM t1 order by b;
203 a b
204 1 1
205 2 2
206 3 3
207 4 4
208 DROP TABLE t1;
209 ###
210 ### IMPORT TEST #3: Ascending key IBD and v3 CFG to Discarded Descending Key IBD
211 ###
212 # Recreate the table with the DESC attribute on the secondary key.
213 CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b DESC));
214 SHOW CREATE TABLE t1;
215 Table Create Table
216 t1 CREATE TABLE `t1` (
217 `a` int NOT NULL,
218 `b` int DEFAULT NULL,
219 PRIMARY KEY (`a`),
220 KEY `b` (`b` DESC)
221 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
222 # Discard the table which deletes the IBD file.
223 ALTER TABLE t1 DISCARD TABLESPACE;
224 # Restore the ascending key IBD and the v3 version of the CFG.
225 # Attempt to import the table. Since the DESC flag is not available in the
226 # CFG file, we assume it is ascending, which is different from the exported
227 # table. The error message will complain about Index b field b.
228 # The import will abort and delete the cfg file.
229 ALTER TABLE t1 IMPORT TABLESPACE;
230 ERROR HY000: Schema mismatch (Index b field b is descending which does not match metadata file which is ascending)
231 DROP TABLE t1;
232 ###
233 ### IMPORT TEST #4 Ascending key IBD and v4 CFG to Discarded Descending Key IBD
234 ###
235 # Recreate the table with the DESC attribute on the secondary key.
236 CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b DESC));
237 SHOW CREATE TABLE t1;
238 Table Create Table
239 t1 CREATE TABLE `t1` (
240 `a` int NOT NULL,
241 `b` int DEFAULT NULL,
242 PRIMARY KEY (`a`),
243 KEY `b` (`b` DESC)
244 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
245 # Discard the table which deletes the IBD file.
246 ALTER TABLE t1 DISCARD TABLESPACE;
247 # Restore the ascending key IBD and the v4 version of the CFG.
248 # Attempt to import the table. Since the DESC flag if the discarded table
249 # is different from the exported table, the error message will complain about
250 # Index b field b. The import will abort and delete the cfg file.
251 ALTER TABLE t1 IMPORT TABLESPACE;
252 ERROR HY000: Schema mismatch (Index b field b is descending which does not match metadata file which is ascending)
253 DROP TABLE t1;
254 ###
255 ### IMPORT TEST #5: Descending key IBD and v3 CFG to Discarded Ascending Key IBD
256 ###
257 # Recreate the table without the DESC attribute on the secondary key.
258 CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b));
259 SHOW CREATE TABLE t1;
260 Table Create Table
261 t1 CREATE TABLE `t1` (
262 `a` int NOT NULL,
263 `b` int DEFAULT NULL,
264 PRIMARY KEY (`a`),
265 KEY `b` (`b`)
266 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
267 # Discard the table which deletes the IBD file.
268 ALTER TABLE t1 DISCARD TABLESPACE;
269 # Restore the descending key IBD and the v3 version of the CFG.
270 # Attempt to import the table. Since the DESC flag is not available in the
271 # CFG file, we assume it is ascending, which matches the exported table.
272 # So the import succeeds, but the sort order of the keys in the imported
273 # file is descending and all new keys will be ascending.
274 ALTER TABLE t1 IMPORT TABLESPACE;
275 Warnings:
276 Warning 1817 InnoDB: Index corrupt: Index 'b' is found to be corrupt and should be recreated.
277 # Show that the index is corrupt
278 SHOW WARNINGS;
279 Level Code Message
280 Warning 1817 InnoDB: Index corrupt: Index 'b' is found to be corrupt and should be recreated.
281 CHECK TABLE t1 EXTENDED;
282 Table Op Msg_type Msg_text
283 test.t1 check Warning InnoDB: Index b is marked as corrupted
284 test.t1 check error Corrupt
285 # Fix the corruption
286 OPTIMIZE TABLE t1;
287 Table Op Msg_type Msg_text
288 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
289 test.t1 optimize status OK
290 # Show that the index is no longer corrupt
291 CHECK TABLE t1 EXTENDED;
292 Table Op Msg_type Msg_text
293 test.t1 check status OK
294 # Show that column b is sorted consistently.
295 SELECT * FROM t1;
296 a b
297 1 1
298 2 2
299 SELECT * FROM t1 order by a;
300 a b
301 1 1
302 2 2
303 SELECT * FROM t1 order by b;
304 a b
305 1 1
306 2 2
307 INSERT INTO t1 VALUES (3, 3);
308 INSERT INTO t1 VALUES (4, 4);
309 SELECT * FROM t1;
310 a b
311 1 1
312 2 2
313 3 3
314 4 4
315 SELECT * FROM t1 order by a;
316 a b
317 1 1
318 2 2
319 3 3
320 4 4
321 SELECT * FROM t1 order by b;
322 a b
323 1 1
324 2 2
325 3 3
326 4 4
327 DROP TABLE t1;
328 ###
329 ### IMPORT TEST #6: Descending key IBD and v4 CFG to Discarded Ascending Key IBD
330 ###
331 # Recreate the table without the DESC attribute on the secondary key.
332 CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b));
333 SHOW CREATE TABLE t1;
334 Table Create Table
335 t1 CREATE TABLE `t1` (
336 `a` int NOT NULL,
337 `b` int DEFAULT NULL,
338 PRIMARY KEY (`a`),
339 KEY `b` (`b`)
340 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
341 # Discard the table which deletes the IBD file.
342 ALTER TABLE t1 DISCARD TABLESPACE;
343 # Restore the descending key IBD and the v4 version of the CFG.
344 # Attempt to import the table. Since the DESC flag if the discarded table
345 # is different from the exported table, the error message will complain about
346 # column b in index b. The import will abort and delete the cfg file.
347 ALTER TABLE t1 IMPORT TABLESPACE;
348 ERROR HY000: Schema mismatch (Index b field b is ascending which does not match metadata file which is descending)
349 DROP TABLE t1;
350 ###
351 ### IMPORT TEST #7: Descending key IBD and v3 CFG to Discarded Descending Key IBD
352 ###
353 # Recreate the table with the DESC attribute on the secondary key.
354 CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b DESC));
355 SHOW CREATE TABLE t1;
356 Table Create Table
357 t1 CREATE TABLE `t1` (
358 `a` int NOT NULL,
359 `b` int DEFAULT NULL,
360 PRIMARY KEY (`a`),
361 KEY `b` (`b` DESC)
362 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
363 # Discard the table which deletes the IBD file.
364 ALTER TABLE t1 DISCARD TABLESPACE;
365 # Restore the descending key IBD and the v3 version of the CFG.
366 # Attempt to import the table. Since the DESC flag is not available in the
367 # CFG file, we wrongly assume it is ascending. Since this is different from
368 # the exported table, the error message will complain about column b in index b.
369 ALTER TABLE t1 IMPORT TABLESPACE;
370 ERROR HY000: Schema mismatch (Index b field b is descending which does not match metadata file which is ascending)
371 DROP TABLE t1;
372 ###
373 ### IMPORT TEST #8: Descending key IBD and v4 CFG to Discarded Descending Key IBD
374 ###
375 # Recreate the table with the DESC attribute on the secondary key.
376 CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b DESC));
377 SHOW CREATE TABLE t1;
378 Table Create Table
379 t1 CREATE TABLE `t1` (
380 `a` int NOT NULL,
381 `b` int DEFAULT NULL,
382 PRIMARY KEY (`a`),
383 KEY `b` (`b` DESC)
384 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
385 # Discard the table which deletes the IBD file.
386 ALTER TABLE t1 DISCARD TABLESPACE;
387 # Restore the descending key IBD and the v4 version of the CFG.
388 # Import the table successfuly.
389 ALTER TABLE t1 IMPORT TABLESPACE;
390 # Show that column b is descending.
391 SELECT * FROM t1;
392 a b
393 1 1
394 2 2
395 SELECT * FROM t1 order by a;
396 a b
397 1 1
398 2 2
399 SELECT * FROM t1 order by b;
400 a b
401 1 1
402 2 2
403 INSERT INTO t1 VALUES (3, 3);
404 INSERT INTO t1 VALUES (4, 4);
405 SELECT * FROM t1;
406 a b
407 1 1
408 2 2
409 3 3
410 4 4
411 SELECT * FROM t1 order by a;
412 a b
413 1 1
414 2 2
415 3 3
416 4 4
417 SELECT * FROM t1 order by b;
418 a b
419 1 1
420 2 2
421 3 3
422 4 4
423 ###
424 ### IMPORT TEST #9: Any unknown version will be rejected with a clear error message.
425 ###
426 # Discard the table which deletes the IBD file.
427 ALTER TABLE t1 DISCARD TABLESPACE;
428 # Restore the descending key IBD and the v99 version of the CFG.
429 # Attempt to import the table.
430 ALTER TABLE t1 IMPORT TABLESPACE;
431 ERROR HY000: Failed to import test/t1 because the CFG file version (99) is not compatible with the current version (5)
432 SHOW WARNINGS;
433 Level Code Message
434 Error 3949 Failed to import test/t1 because the CFG file version (99) is not compatible with the current version (5)
435 Warning 1816 InnoDB: ALTER TABLE `test`.`t1` IMPORT TABLESPACE failed with error 48 : 'Unsupported'
436 Error 1112 Table 't1' uses an extension that doesn't exist in this MySQL version
437 # Cleanup
438 DROP TABLE t1;
439 #
440 # Bug#30191523 : FLUSH TABLE T FOR EXPORT OR ALTER TABLE T2 IMPORT
441 # TABLESPACE BROKEN IN 8.0.17.
442 #
443 # ---------------------- Test 1 ----------------------------------
444 # Source table has INSTANT columns but target table doesn't
445 # ----------------------------------------------------------------
446 # Create source table t1 and add a column INSTANTly
447 CREATE TABLE t1(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY);
448 ALTER TABLE t1 ADD COLUMN v1 VARCHAR(255), ALGORITHM=INSTANT;
449 ALTER TABLE t1 ADD COLUMN v2 VARCHAR(255), ALGORITHM=INSTANT;
450 # Insert some data into t1
451 SELECT * from t1 limit 10;
452 id v1 v2
453 1 aaaaaaaaaaaaa bbbbbbbbbbbbbb
454 2 aaaaaaaaaaaaa bbbbbbbbbbbbbb
455 3 aaaaaaaaaaaaa bbbbbbbbbbbbbb
456 4 aaaaaaaaaaaaa bbbbbbbbbbbbbb
457 5 aaaaaaaaaaaaa bbbbbbbbbbbbbb
458 6 aaaaaaaaaaaaa bbbbbbbbbbbbbb
459 7 aaaaaaaaaaaaa bbbbbbbbbbbbbb
460 8 aaaaaaaaaaaaa bbbbbbbbbbbbbb
461 9 aaaaaaaaaaaaa bbbbbbbbbbbbbb
462 10 aaaaaaaaaaaaa bbbbbbbbbbbbbb
463 SELECT COUNT(*) from t1;
464 COUNT(*)
465 1000
466 SELECT NAME, N_COLS, INSTANT_COLS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/t1";
467 NAME N_COLS INSTANT_COLS
468 test/t1 6 0
469 SELECT NAME, POS, HAS_DEFAULT from information_Schema.innodb_columns WHERE NAME='v1' OR NAME='v2';
470 NAME POS HAS_DEFAULT
471 v1 1 1
472 v2 2 1
473 # Flush the table and store CFG/IBD files to temp
474 FLUSH TABLE t1 FOR EXPORT;
475 UNLOCK TABLES;
476 # Create table t2 without INSTANTly added columns
477 CREATE TABLE t2 LIKE t1;
478 ALTER TABLE t2 DISCARD TABLESPACE;
479 # Copy CFG/IBD file from temp
480 # IMPORT should succeed. Target table metadata should have been updated.
481 ALTER TABLE t2 IMPORT TABLESPACE;
482 SELECT * from t2 limit 10;
483 id v1 v2
484 1 aaaaaaaaaaaaa bbbbbbbbbbbbbb
485 2 aaaaaaaaaaaaa bbbbbbbbbbbbbb
486 3 aaaaaaaaaaaaa bbbbbbbbbbbbbb
487 4 aaaaaaaaaaaaa bbbbbbbbbbbbbb
488 5 aaaaaaaaaaaaa bbbbbbbbbbbbbb
489 6 aaaaaaaaaaaaa bbbbbbbbbbbbbb
490 7 aaaaaaaaaaaaa bbbbbbbbbbbbbb
491 8 aaaaaaaaaaaaa bbbbbbbbbbbbbb
492 9 aaaaaaaaaaaaa bbbbbbbbbbbbbb
493 10 aaaaaaaaaaaaa bbbbbbbbbbbbbb
494 SELECT COUNT(*) from t2;
495 COUNT(*)
496 1000
497 SELECT NAME, N_COLS, INSTANT_COLS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/t1" OR NAME="test/t2";
498 NAME N_COLS INSTANT_COLS
499 test/t1 6 0
500 test/t2 6 0
501 SELECT NAME, POS, HAS_DEFAULT from information_Schema.innodb_columns WHERE NAME='v1' OR NAME='v2';
502 NAME POS HAS_DEFAULT
503 v1 1 1
504 v2 2 1
505 v1 1 1
506 v2 2 1
507 DROP TABLE t2;
508 # Create table t2 with one column added INSTANTly
509 CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, v1 VARCHAR(255));
510 ALTER TABLE t2 ADD COLUMN v2 VARCHAR(255), ALGORITHM=INSTANT;
511 ALTER TABLE t2 DISCARD TABLESPACE;
512 # Copy CFG/IBD file from temp
513 # IMPORT should fail as INSTANT METADATA doesn't match.
514 ALTER TABLE t2 IMPORT TABLESPACE;
515 ERROR HY000: Schema mismatch (Table has instant column but current row version didn't match.)
516 DROP TABLE t2;
517 # Create table t2 with two column added INSTANTly
518 CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY);
519 ALTER TABLE t2 ADD COLUMN v1 VARCHAR(255), ALGORITHM=INSTANT;
520 ALTER TABLE t2 ADD COLUMN v2 VARCHAR(255), ALGORITHM=INSTANT;
521 ALTER TABLE t2 DISCARD TABLESPACE;
522 # Copy CFG/IBD file from temp
523 # IMPORT should succeed.
524 ALTER TABLE t2 IMPORT TABLESPACE;
525 SELECT * from t2 limit 10;
526 id v1 v2
527 1 aaaaaaaaaaaaa bbbbbbbbbbbbbb
528 2 aaaaaaaaaaaaa bbbbbbbbbbbbbb
529 3 aaaaaaaaaaaaa bbbbbbbbbbbbbb
530 4 aaaaaaaaaaaaa bbbbbbbbbbbbbb
531 5 aaaaaaaaaaaaa bbbbbbbbbbbbbb
532 6 aaaaaaaaaaaaa bbbbbbbbbbbbbb
533 7 aaaaaaaaaaaaa bbbbbbbbbbbbbb
534 8 aaaaaaaaaaaaa bbbbbbbbbbbbbb
535 9 aaaaaaaaaaaaa bbbbbbbbbbbbbb
536 10 aaaaaaaaaaaaa bbbbbbbbbbbbbb
537 SELECT COUNT(*) from t2;
538 COUNT(*)
539 1000
540 SELECT NAME, N_COLS, INSTANT_COLS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/t1" OR NAME="test/t2";
541 NAME N_COLS INSTANT_COLS
542 test/t1 6 0
543 test/t2 6 0
544 SELECT NAME, POS, HAS_DEFAULT from information_Schema.innodb_columns WHERE NAME='v1' OR NAME='v2';
545 NAME POS HAS_DEFAULT
546 v1 1 1
547 v2 2 1
548 v1 1 1
549 v2 2 1
550 DROP TABLE t2;
551 DROP TABLE t1;
552 # ---------------------- Test 2 ----------------------------------
553 # Source table doesn't have INSTANT columns but target table does
554 # ----------------------------------------------------------------
555 # Create source table t1 and add a column INSTANTly
556 CREATE TABLE t1(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, v1 VARCHAR(255), v2 VARCHAR(255));
557 # Insert some data into t1
558 SELECT * from t1 limit 10;
559 id v1 v2
560 1 aaaaaaaaaaaaa bbbbbbbbbbbbbb
561 2 aaaaaaaaaaaaa bbbbbbbbbbbbbb
562 3 aaaaaaaaaaaaa bbbbbbbbbbbbbb
563 4 aaaaaaaaaaaaa bbbbbbbbbbbbbb
564 5 aaaaaaaaaaaaa bbbbbbbbbbbbbb
565 6 aaaaaaaaaaaaa bbbbbbbbbbbbbb
566 7 aaaaaaaaaaaaa bbbbbbbbbbbbbb
567 8 aaaaaaaaaaaaa bbbbbbbbbbbbbb
568 9 aaaaaaaaaaaaa bbbbbbbbbbbbbb
569 10 aaaaaaaaaaaaa bbbbbbbbbbbbbb
570 SELECT COUNT(*) from t1;
571 COUNT(*)
572 1000
573 SELECT NAME, N_COLS, INSTANT_COLS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/t1";
574 NAME N_COLS INSTANT_COLS
575 test/t1 6 0
576 SELECT NAME, POS, HAS_DEFAULT from information_Schema.innodb_columns WHERE NAME='v1' OR NAME='v2';
577 NAME POS HAS_DEFAULT
578 v1 1 0
579 v2 2 0
580 # Flush the table and store CFG/IBD files to temp
581 FLUSH TABLE t1 FOR EXPORT;
582 UNLOCK TABLES;
583 # Create table t2 with no INSTANTly added columns
584 CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, v1 VARCHAR(255), v2 VARCHAR(255));
585 ALTER TABLE t2 DISCARD TABLESPACE;
586 # Copy CFG/IBD file from temp
587 # IMPORT should succeed.
588 ALTER TABLE t2 IMPORT TABLESPACE;
589 DROP TABLE t2;
590 # Create table t2 with 1 INSTANTly added columns
591 CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, v1 VARCHAR(255));
592 ALTER TABLE t2 ADD COLUMN v2 VARCHAR(255), ALGORITHM=INSTANT;
593 ALTER TABLE t2 DISCARD TABLESPACE;
594 # Copy CFG/IBD file from temp
595 # IMPORT should fail.
596 ALTER TABLE t2 IMPORT TABLESPACE;
597 ERROR HY000: Schema mismatch (The .cfg file indicates no INSTANT column in the source table whereas the metadata in data dictionary says there are instant columns in the target table)
598 DROP TABLE t2;
599 # Create table t2 with 2 INSTANTly added columns
600 CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY);
601 ALTER TABLE t2 ADD COLUMN v1 VARCHAR(255), ALGORITHM=INSTANT;
602 ALTER TABLE t2 ADD COLUMN v2 VARCHAR(255), ALGORITHM=INSTANT;
603 ALTER TABLE t2 DISCARD TABLESPACE;
604 # Copy CFG/IBD file from temp
605 # IMPORT should fail.
606 ALTER TABLE t2 IMPORT TABLESPACE;
607 ERROR HY000: Schema mismatch (The .cfg file indicates no INSTANT column in the source table whereas the metadata in data dictionary says there are instant columns in the target table)
608 DROP TABLE t2;
609 # Cleanup
610 DROP TABLE t1;