Debian Codegraph @

mysql-test/suite/innodb/t/import_cfg.test

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