Debian Codegraph @

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

raw
1 # Not supported in embedded
2 --source include/not_embedded.inc
3
4 -- source include/have_innodb.inc
5 -- source include/have_sequence.inc
6 -- source include/innodb_checksum_algorithm.inc
7
8 call mtr.add_suppression("InnoDB: Unable to import tablespace .* because it already exists. Please DISCARD the tablespace before IMPORT\\.");
9 call mtr.add_suppression("Index for table 't2' is corrupt; try to repair it");
10 call mtr.add_suppression("InnoDB: Cannot save statistics for table `test`\\.`t1` because the \\.ibd file is missing");
11
12 FLUSH TABLES;
13
14 let $MYSQLD_DATADIR = `SELECT @@datadir`;
15 let $checksum_algorithm = `SELECT @@innodb_checksum_algorithm`;
16
17 CREATE TABLE t1
18 (a INT AUTO_INCREMENT PRIMARY KEY,
19 b char(22),
20 c varchar(255),
21 KEY (b))
22 ENGINE = InnoDB;
23
24 CREATE TEMPORARY TABLE t (b char(22),c varchar(255));
25 INSERT INTO t VALUES
26 ('Apa', 'Filler........'),
27 ('Banan', 'Filler........'), ('Cavalry', '..asdasdfaeraf'),
28 ('Devotion', 'asdfuihknaskdf'), ('Evolution', 'lsjndofiabsoibeg');
29
30 INSERT INTO t1 (b,c) SELECT b,c FROM t,seq_1_to_128;
31 DROP TEMPORARY TABLE t;
32 SELECT COUNT(*) FROM t1;
33 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
34 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
35
36 CREATE TABLE t2(a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT;
37
38 --list_files $MYSQLD_DATADIR/test
39 --source include/restart_mysqld.inc
40 FLUSH TABLE t1, t2 FOR EXPORT;
41 --echo # List before copying files
42 --list_files $MYSQLD_DATADIR/test
43 --copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t1.cfg.sav
44 --copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t1.ibd.sav
45 --move_file $MYSQLD_DATADIR/test/t2.cfg $MYSQLD_DATADIR/test/t2.cfg.sav
46 --copy_file $MYSQLD_DATADIR/test/t2.ibd $MYSQLD_DATADIR/test/t2.ibd.sav
47 UNLOCK TABLES;
48 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
49 SELECT COUNT(*) FROM t1;
50 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
51 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
52 --echo # Restarting server
53 -- source include/restart_mysqld.inc
54 --echo # Done restarting server
55 --echo # List before t1 DISCARD
56 --list_files $MYSQLD_DATADIR/test
57 ALTER TABLE t1 DISCARD TABLESPACE;
58 --echo # List after t1 DISCARD
59 --list_files $MYSQLD_DATADIR/test
60 --copy_file $MYSQLD_DATADIR/test/t1.cfg.sav $MYSQLD_DATADIR/test/t1.cfg
61 --copy_file $MYSQLD_DATADIR/test/t1.ibd.sav $MYSQLD_DATADIR/test/t1.ibd
62 ALTER TABLE t1 IMPORT TABLESPACE;
63 ALTER TABLE t1 ENGINE InnoDB;
64 SELECT COUNT(*) FROM t1;
65 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
66 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
67 --list_files $MYSQLD_DATADIR/test
68 SELECT COUNT(*) FROM t1;
69 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
70 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
71 DROP TABLE t1;
72 --remove_file $MYSQLD_DATADIR/test/t1.cfg.sav
73 --remove_file $MYSQLD_DATADIR/test/t1.ibd.sav
74
75 ALTER TABLE t2 ROW_FORMAT=DYNAMIC;
76 ALTER TABLE t2 DISCARD TABLESPACE;
77 --echo # List after t2 DISCARD
78 --list_files $MYSQLD_DATADIR/test
79 --move_file $MYSQLD_DATADIR/test/t2.ibd.sav $MYSQLD_DATADIR/test/t2.ibd
80 --move_file $MYSQLD_DATADIR/test/t2.cfg.sav $MYSQLD_DATADIR/test/t2.cfg
81 --error ER_TABLE_SCHEMA_MISMATCH
82 ALTER TABLE t2 IMPORT TABLESPACE;
83 --remove_file $MYSQLD_DATADIR/test/t2.cfg
84
85 let $error_code = ER_TABLE_SCHEMA_MISMATCH;
86
87 if ($checksum_algorithm == "full_crc32") {
88 let $error_code = 0;
89 }
90
91 if ($checksum_algorithm == "strict_full_crc32") {
92 let $error_code = 0;
93 }
94
95 --replace_regex /(FSP_SPACE_FLAGS=0x)[0-9a-f]+(,.*0x)[0-9a-f]+(.*)/\1*\2*\3/
96 --error $error_code
97 ALTER TABLE t2 IMPORT TABLESPACE;
98 DROP TABLE t2;
99
100 SELECT @@innodb_file_per_table;
101
102 let MYSQLD_DATADIR =`SELECT @@datadir`;
103
104 # Try importing when tablespace already exists
105 CREATE TABLE t1(
106 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
107 c2 INT) ENGINE=InnoDB;
108
109 INSERT INTO t1(c2) VALUES(1);
110 --error ER_TABLESPACE_EXISTS
111 ALTER TABLE t1 IMPORT TABLESPACE;
112 SELECT * FROM t1;
113 DROP TABLE t1;
114
115 # Export/import on the same instance
116 CREATE TABLE t1(
117 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
118 c2 INT) ENGINE=InnoDB;
119
120 INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16;
121
122 --list_files $MYSQLD_DATADIR/test
123
124 FLUSH TABLES t1 FOR EXPORT;
125 perl;
126 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
127 ib_backup_tablespaces("test", "t1");
128 EOF
129
130 --list_files $MYSQLD_DATADIR/test
131
132 UNLOCK TABLES;
133
134 DROP TABLE t1;
135
136 CREATE TABLE t1(
137 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
138 c2 INT) ENGINE=InnoDB;
139
140 ALTER TABLE t1 DISCARD TABLESPACE;
141 --error ER_TABLESPACE_DISCARDED
142 SELECT * FROM t1;
143
144 perl;
145 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
146 ib_discard_tablespaces("test", "t1");
147 ib_restore_tablespaces("test", "t1");
148 EOF
149
150 --list_files $MYSQLD_DATADIR/test
151
152 ALTER TABLE t1 IMPORT TABLESPACE;
153 CHECK TABLE t1;
154
155 SELECT COUNT(*) FROM t1;
156
157 DROP TABLE t1;
158
159 # Export/import on the same instance
160 # Insert some more records to move the LSN forward and then drop the
161 # table and restore
162 CREATE TABLE t1(
163 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
164 c2 INT) ENGINE=InnoDB;
165
166 INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16;
167
168 --list_files $MYSQLD_DATADIR/test
169 FLUSH TABLES t1 FOR EXPORT;
170 perl;
171 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
172 ib_backup_tablespaces("test", "t1");
173 EOF
174 --list_files $MYSQLD_DATADIR/test
175 UNLOCK TABLES;
176
177 --list_files $MYSQLD_DATADIR/test
178
179 # Move the LSN forward
180 DROP TABLE t1;
181
182 CREATE TABLE t1(
183 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
184 c2 INT) ENGINE=InnoDB;
185
186 ALTER TABLE t1 DISCARD TABLESPACE;
187 --error ER_TABLESPACE_DISCARDED
188 SELECT * FROM t1;
189
190 perl;
191 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
192 ib_discard_tablespaces("test", "t1");
193 ib_restore_tablespaces("test", "t1");
194 EOF
195
196 --list_files $MYSQLD_DATADIR/test
197
198 ALTER TABLE t1 IMPORT TABLESPACE;
199 CHECK TABLE t1;
200
201 SELECT COUNT(*) FROM t1;
202
203 DROP TABLE t1;
204
205 # Export/import on the same instance
206 # Insert some more records to move the LSN forward and then drop the
207 # table and restore, this time the table has a secondary index too.
208 CREATE TABLE t1(
209 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
210 c2 INT, INDEX(c2)) ENGINE=InnoDB;
211
212 INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16;
213
214 FLUSH TABLES t1 FOR EXPORT;
215 perl;
216 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
217 ib_backup_tablespaces("test", "t1");
218 EOF
219 --list_files $MYSQLD_DATADIR/test
220 UNLOCK TABLES;
221
222 # Move the LSN forward
223 DROP TABLE t1;
224
225 CREATE TABLE t1(
226 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
227 c2 INT, INDEX(c2)) ENGINE=InnoDB;
228
229 ALTER TABLE t1 DISCARD TABLESPACE;
230 --error ER_TABLESPACE_DISCARDED
231 SELECT * FROM t1;
232
233 perl;
234 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
235 ib_discard_tablespaces("test", "t1");
236 ib_restore_tablespaces("test", "t1");
237 EOF
238
239 ALTER TABLE t1 IMPORT TABLESPACE;
240 CHECK TABLE t1;
241
242 SELECT COUNT(*) FROM t1 WHERE c2 = 1;
243
244 DROP TABLE t1;
245
246 # Export/import on the same instance
247 # Insert some more records to move the LSN forward and then drop the
248 # table and restore, this time the table has a secondary index too.
249 # Rename the index on the create so that the IMPORT fails, drop index
250 # Create with proper name and then do an IMPORT.
251 CREATE TABLE t1(
252 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
253 c2 INT, INDEX idx(c2)) ENGINE=InnoDB;
254
255 INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16;
256
257 FLUSH TABLES t1 FOR EXPORT;
258 perl;
259 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
260 ib_backup_tablespaces("test", "t1");
261 EOF
262 UNLOCK TABLES;
263
264 # Move the LSN forward
265 DROP TABLE t1;
266
267 CREATE TABLE t1(
268 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
269 c2 INT, INDEX x(c2)) ENGINE=InnoDB;
270
271 ALTER TABLE t1 DISCARD TABLESPACE;
272 --error ER_TABLESPACE_DISCARDED
273 SELECT * FROM t1;
274
275 perl;
276 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
277 ib_discard_tablespaces("test", "t1");
278 ib_restore_tablespaces("test", "t1");
279 EOF
280
281 # This is really a name mismatch error, need better error codes.
282 -- error ER_TABLE_SCHEMA_MISMATCH
283 ALTER TABLE t1 IMPORT TABLESPACE;
284 --error ER_TABLESPACE_DISCARDED
285 select count(*) from t1;
286 ALTER TABLE t1 DROP INDEX x;
287 --error ER_CANT_DROP_FIELD_OR_KEY
288 ALTER TABLE t1 DROP INDEX x, ALGORITHM=copy;
289 ALTER TABLE t1 ADD INDEX idx(c2);
290
291 perl;
292 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
293 ib_restore_tablespaces("test", "t1");
294 EOF
295
296 ALTER TABLE t1 IMPORT TABLESPACE;
297 CHECK TABLE t1;
298
299 SELECT * FROM t1;
300
301 perl;
302 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
303 ib_cleanup("test", "t1");
304 EOF
305
306 DROP TABLE t1;
307
308 #
309 # Export/import on the same instance, with --innodb-file-per-table=0
310 # This should fail because it is not supported
311 SET GLOBAL innodb_file_per_table = 0;
312
313 CREATE TABLE t1(
314 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
315 c2 INT) ENGINE=InnoDB;
316
317 INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16;
318 SHOW CREATE TABLE t1;
319
320 # This should fail, InnoDB should return a warning
321 FLUSH TABLES t1 FOR EXPORT;
322
323 UNLOCK TABLES;
324
325 DROP TABLE t1;
326
327 #
328 # Tests that check for schema mismatch during IMPORT
329 #
330
331 SET GLOBAL innodb_file_per_table = 1;
332
333 CREATE TABLE t1(
334 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
335 c2 INT, INDEX idx(c2)) ENGINE=InnoDB;
336
337 INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16;
338
339 SHOW CREATE TABLE t1;
340
341 FLUSH TABLES t1 FOR EXPORT;
342
343 perl;
344 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
345 ib_backup_tablespaces("test", "t1");
346 EOF
347
348 UNLOCK TABLES;
349
350 DROP TABLE t1;
351
352 # Table without the secondary index
353 CREATE TABLE t1(
354 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
355 c2 INT) ENGINE=InnoDB;
356
357 ALTER TABLE t1 DISCARD TABLESPACE;
358 --error ER_TABLESPACE_DISCARDED
359 SELECT * FROM t1;
360
361 perl;
362 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
363 ib_discard_tablespaces("test", "t1");
364 ib_restore_tablespaces("test", "t1");
365 EOF
366
367 # This should fail because of a missing secondary index
368 -- error ER_TABLE_SCHEMA_MISMATCH
369 ALTER TABLE t1 IMPORT TABLESPACE;
370
371 perl;
372 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
373 ib_unlink_tablespace("test", "t1");
374 EOF
375
376 DROP TABLE t1;
377
378 # Table with an additional column
379 CREATE TABLE t1(
380 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
381 c2 INT,
382 c3 INT, INDEX idx(c2)) ENGINE=InnoDB;
383
384 ALTER TABLE t1 DISCARD TABLESPACE;
385 --error ER_TABLESPACE_DISCARDED
386 SELECT * FROM t1;
387
388 perl;
389 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
390 ib_discard_tablespaces("test", "t1");
391 ib_restore_tablespaces("test", "t1");
392 EOF
393
394 # This should fail because the table has an additional column
395 -- error ER_TABLE_SCHEMA_MISMATCH
396 ALTER TABLE t1 IMPORT TABLESPACE;
397
398 perl;
399 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
400 ib_unlink_tablespace("test", "t1");
401 EOF
402
403 DROP TABLE t1;
404
405 # Change the column type of c2
406 CREATE TABLE t1(
407 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
408 c2 BIGINT, INDEX idx(c2)) ENGINE=InnoDB;
409
410 ALTER TABLE t1 DISCARD TABLESPACE;
411 --error ER_TABLESPACE_DISCARDED
412 SELECT * FROM t1;
413
414 perl;
415 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
416 ib_discard_tablespaces("test", "t1");
417 ib_restore_tablespaces("test", "t1");
418 EOF
419
420 # This should fail because c2 is now a BIGINT and not INT
421 -- error ER_TABLE_SCHEMA_MISMATCH
422 ALTER TABLE t1 IMPORT TABLESPACE;
423
424 perl;
425 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
426 ib_unlink_tablespace("test", "t1");
427 EOF
428
429 DROP TABLE t1;
430
431 # This should be OK.
432 CREATE TABLE t1(
433 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
434 c2 INT, INDEX idx(c2)) ENGINE=InnoDB;
435
436 ALTER TABLE t1 DISCARD TABLESPACE;
437 --error ER_TABLESPACE_DISCARDED
438 SELECT * FROM t1;
439
440 perl;
441 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
442 ib_discard_tablespaces("test", "t1");
443 ib_restore_tablespaces("test", "t1");
444 EOF
445
446 ALTER TABLE t1 IMPORT TABLESPACE;
447 CHECK TABLE t1;
448
449 perl;
450 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
451 ib_cleanup("test", "t1");
452 EOF
453
454 SHOW CREATE TABLE t1;
455 SELECT * FROM t1;
456
457 DROP TABLE t1;
458
459 #
460 # EXPORT ROW_FORMAT=REDUNDANT
461 #
462 CREATE TABLE t1(
463 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
464 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
465
466 INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16;
467
468 SHOW CREATE TABLE t1;
469
470 FLUSH TABLES t1 FOR EXPORT;
471
472 perl;
473 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
474 ib_backup_tablespaces("test", "t1");
475 EOF
476
477 UNLOCK TABLES;
478
479 DROP TABLE t1;
480
481 CREATE TABLE t1(
482 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
483 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
484
485 ALTER TABLE t1 DISCARD TABLESPACE;
486 --error ER_TABLESPACE_DISCARDED
487 SELECT * FROM t1;
488
489 perl;
490 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
491 ib_discard_tablespaces("test", "t1");
492 ib_restore_tablespaces("test", "t1");
493 EOF
494
495 ALTER TABLE t1 IMPORT TABLESPACE;
496 CHECK TABLE t1;
497
498 perl;
499 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
500 ib_cleanup("test", "t1");
501 EOF
502
503 SHOW CREATE TABLE t1;
504 SELECT * FROM t1;
505
506 DROP TABLE t1;
507
508 CREATE TABLE t1(
509 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
510 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
511
512 ALTER TABLE t1 DISCARD TABLESPACE;
513 --error ER_TABLESPACE_DISCARDED
514 SELECT * FROM t1;
515
516 perl;
517 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
518 ib_discard_tablespaces("test", "t1");
519 ib_restore_tablespaces("test", "t1");
520 EOF
521
522 --error ER_TABLE_SCHEMA_MISMATCH
523 ALTER TABLE t1 IMPORT TABLESPACE;
524
525 perl;
526 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
527 ib_unlink_tablespace("test", "t1");
528 EOF
529
530 DROP TABLE t1;
531
532 CREATE TABLE t1(
533 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
534 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
535
536 ALTER TABLE t1 DISCARD TABLESPACE;
537 --error ER_TABLESPACE_DISCARDED
538 SELECT * FROM t1;
539
540 perl;
541 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
542 ib_discard_tablespaces("test", "t1");
543 ib_restore_tablespaces("test", "t1");
544 EOF
545
546 --error ER_TABLE_SCHEMA_MISMATCH
547 ALTER TABLE t1 IMPORT TABLESPACE;
548
549 perl;
550 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
551 ib_unlink_tablespace("test", "t1");
552 EOF
553
554 DROP TABLE t1;
555 --disable_query_log
556 SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed;
557 SET GLOBAL innodb_read_only_compressed=OFF;
558 --enable_query_log
559 CREATE TABLE t1(
560 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
561 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
562 ALTER TABLE t1 DISCARD TABLESPACE;
563 --error ER_TABLESPACE_DISCARDED
564 SELECT * FROM t1;
565
566 perl;
567 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
568 ib_discard_tablespaces("test", "t1");
569 ib_restore_tablespaces("test", "t1");
570 EOF
571
572 --error ER_TABLE_SCHEMA_MISMATCH
573 ALTER TABLE t1 IMPORT TABLESPACE;
574
575 perl;
576 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
577 ib_unlink_tablespace("test", "t1");
578 EOF
579
580 DROP TABLE t1;
581
582 # This should be OK.
583 CREATE TABLE t1(
584 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
585 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
586
587 ALTER TABLE t1 DISCARD TABLESPACE;
588 --error ER_TABLESPACE_DISCARDED
589 SELECT * FROM t1;
590
591 perl;
592 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
593 ib_discard_tablespaces("test", "t1");
594 ib_restore_tablespaces("test", "t1");
595 EOF
596
597 ALTER TABLE t1 IMPORT TABLESPACE;
598
599 perl;
600 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
601 ib_cleanup("test", "t1");
602 EOF
603
604 SHOW CREATE TABLE t1;
605 SELECT * FROM t1;
606
607 DROP TABLE t1;
608
609 #
610 # EXPORT ROW_FORMAT=COMPACT
611 #
612 CREATE TABLE t1(
613 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
614 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
615
616 INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16;
617
618 SHOW CREATE TABLE t1;
619
620 FLUSH TABLES t1 FOR EXPORT;
621
622 perl;
623 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
624 ib_backup_tablespaces("test", "t1");
625 EOF
626
627 UNLOCK TABLES;
628
629 DROP TABLE t1;
630
631 CREATE TABLE t1(
632 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
633 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
634
635 ALTER TABLE t1 DISCARD TABLESPACE;
636 --error ER_TABLESPACE_DISCARDED
637 SELECT * FROM t1;
638
639 perl;
640 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
641 ib_discard_tablespaces("test", "t1");
642 ib_restore_tablespaces("test", "t1");
643 EOF
644
645 ALTER TABLE t1 IMPORT TABLESPACE;
646 CHECK TABLE t1;
647
648 perl;
649 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
650 ib_cleanup("test", "t1");
651 EOF
652
653 SHOW CREATE TABLE t1;
654 SELECT * FROM t1;
655
656 DROP TABLE t1;
657
658 CREATE TABLE t1(
659 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
660 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
661
662 ALTER TABLE t1 DISCARD TABLESPACE;
663 --error ER_TABLESPACE_DISCARDED
664 SELECT * FROM t1;
665
666 perl;
667 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
668 ib_discard_tablespaces("test", "t1");
669 ib_restore_tablespaces("test", "t1");
670 EOF
671
672 --error ER_TABLE_SCHEMA_MISMATCH
673 ALTER TABLE t1 IMPORT TABLESPACE;
674
675 perl;
676 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
677 ib_unlink_tablespace("test", "t1");
678 EOF
679
680 DROP TABLE t1;
681
682 CREATE TABLE t1(
683 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
684 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
685
686 ALTER TABLE t1 DISCARD TABLESPACE;
687 --error ER_TABLESPACE_DISCARDED
688 SELECT * FROM t1;
689
690 perl;
691 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
692 ib_discard_tablespaces("test", "t1");
693 ib_restore_tablespaces("test", "t1");
694 EOF
695
696 --error ER_TABLE_SCHEMA_MISMATCH
697 ALTER TABLE t1 IMPORT TABLESPACE;
698
699 perl;
700 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
701 ib_unlink_tablespace("test", "t1");
702 EOF
703
704 DROP TABLE t1;
705
706 CREATE TABLE t1(
707 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
708 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
709 ALTER TABLE t1 DISCARD TABLESPACE;
710 --error ER_TABLESPACE_DISCARDED
711 SELECT * FROM t1;
712
713 perl;
714 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
715 ib_discard_tablespaces("test", "t1");
716 ib_restore_tablespaces("test", "t1");
717 EOF
718
719 --error ER_TABLE_SCHEMA_MISMATCH
720 ALTER TABLE t1 IMPORT TABLESPACE;
721
722 perl;
723 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
724 ib_unlink_tablespace("test", "t1");
725 EOF
726
727 DROP TABLE t1;
728
729 # This should be OK.
730 CREATE TABLE t1(
731 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
732 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
733
734 ALTER TABLE t1 DISCARD TABLESPACE;
735 --error ER_TABLESPACE_DISCARDED
736 SELECT * FROM t1;
737
738 perl;
739 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
740 ib_discard_tablespaces("test", "t1");
741 ib_restore_tablespaces("test", "t1");
742 EOF
743
744 ALTER TABLE t1 IMPORT TABLESPACE;
745 CHECK TABLE t1;
746
747 perl;
748 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
749 ib_cleanup("test", "t1");
750 EOF
751
752 SHOW CREATE TABLE t1;
753 SELECT * FROM t1;
754
755 DROP TABLE t1;
756
757 #
758 # EXPORT ROW_FORMAT=DYNAMIC
759 #
760 CREATE TABLE t1(
761 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
762 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
763
764 INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16;
765
766 SHOW CREATE TABLE t1;
767
768 FLUSH TABLES t1 FOR EXPORT;
769
770 perl;
771 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
772 ib_backup_tablespaces("test", "t1");
773 EOF
774
775 UNLOCK TABLES;
776
777 DROP TABLE t1;
778
779 CREATE TABLE t1(
780 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
781 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
782
783 ALTER TABLE t1 DISCARD TABLESPACE;
784 --error ER_TABLESPACE_DISCARDED
785 SELECT * FROM t1;
786
787 perl;
788 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
789 ib_discard_tablespaces("test", "t1");
790 ib_restore_tablespaces("test", "t1");
791 EOF
792
793 ALTER TABLE t1 IMPORT TABLESPACE;
794 CHECK TABLE t1;
795
796 perl;
797 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
798 ib_cleanup("test", "t1");
799 EOF
800
801 SHOW CREATE TABLE t1;
802 SELECT * FROM t1;
803
804 DROP TABLE t1;
805
806 CREATE TABLE t1(
807 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
808 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
809
810 ALTER TABLE t1 DISCARD TABLESPACE;
811 --error ER_TABLESPACE_DISCARDED
812 SELECT * FROM t1;
813
814 perl;
815 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
816 ib_discard_tablespaces("test", "t1");
817 ib_restore_tablespaces("test", "t1");
818 EOF
819
820 --error ER_TABLE_SCHEMA_MISMATCH
821 ALTER TABLE t1 IMPORT TABLESPACE;
822
823 perl;
824 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
825 ib_unlink_tablespace("test", "t1");
826 EOF
827
828 DROP TABLE t1;
829
830 CREATE TABLE t1(
831 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
832 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
833
834 ALTER TABLE t1 DISCARD TABLESPACE;
835 --error ER_TABLESPACE_DISCARDED
836 SELECT * FROM t1;
837
838 perl;
839 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
840 ib_discard_tablespaces("test", "t1");
841 ib_restore_tablespaces("test", "t1");
842 EOF
843
844 --error ER_TABLE_SCHEMA_MISMATCH
845 ALTER TABLE t1 IMPORT TABLESPACE;
846
847 perl;
848 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
849 ib_unlink_tablespace("test", "t1");
850 EOF
851
852 DROP TABLE t1;
853
854 CREATE TABLE t1(
855 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
856 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
857 ALTER TABLE t1 DISCARD TABLESPACE;
858 --error ER_TABLESPACE_DISCARDED
859 SELECT * FROM t1;
860
861 perl;
862 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
863 ib_discard_tablespaces("test", "t1");
864 ib_restore_tablespaces("test", "t1");
865 EOF
866
867 --error ER_TABLE_SCHEMA_MISMATCH
868 ALTER TABLE t1 IMPORT TABLESPACE;
869
870 perl;
871 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
872 ib_unlink_tablespace("test", "t1");
873 EOF
874
875 DROP TABLE t1;
876
877 # This should be OK.
878 CREATE TABLE t1(
879 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
880 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
881
882 ALTER TABLE t1 DISCARD TABLESPACE;
883 --error ER_TABLESPACE_DISCARDED
884 SELECT * FROM t1;
885
886 perl;
887 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
888 ib_discard_tablespaces("test", "t1");
889 ib_restore_tablespaces("test", "t1");
890 EOF
891
892 ALTER TABLE t1 IMPORT TABLESPACE;
893 CHECK TABLE t1;
894
895 perl;
896 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
897 ib_cleanup("test", "t1");
898 EOF
899
900 SHOW CREATE TABLE t1;
901 SELECT * FROM t1;
902
903 DROP TABLE t1;
904
905 #
906 # EXPORT ROW_FORMAT=COMPRESSED
907 #
908 CREATE TABLE t1(
909 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
910 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
911
912 INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16;
913
914 SHOW CREATE TABLE t1;
915
916 FLUSH TABLES t1 FOR EXPORT;
917
918 perl;
919 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
920 ib_backup_tablespaces("test", "t1");
921 EOF
922
923 UNLOCK TABLES;
924
925 DROP TABLE t1;
926
927 CREATE TABLE t1(
928 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
929 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
930
931 ALTER TABLE t1 DISCARD TABLESPACE;
932 --error ER_TABLESPACE_DISCARDED
933 SELECT * FROM t1;
934
935 perl;
936 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
937 ib_discard_tablespaces("test", "t1");
938 ib_restore_tablespaces("test", "t1");
939 EOF
940
941 ALTER TABLE t1 IMPORT TABLESPACE;
942 CHECK TABLE t1;
943
944 perl;
945 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
946 ib_cleanup("test", "t1");
947 EOF
948
949 SHOW CREATE TABLE t1;
950 SELECT * FROM t1;
951
952 DROP TABLE t1;
953
954 CREATE TABLE t1(
955 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
956 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
957
958 ALTER TABLE t1 DISCARD TABLESPACE;
959 --error ER_TABLESPACE_DISCARDED
960 SELECT * FROM t1;
961
962 perl;
963 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
964 ib_discard_tablespaces("test", "t1");
965 ib_restore_tablespaces("test", "t1");
966 EOF
967
968 --error ER_TABLE_SCHEMA_MISMATCH
969 ALTER TABLE t1 IMPORT TABLESPACE;
970
971 perl;
972 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
973 ib_unlink_tablespace("test", "t1");
974 EOF
975
976 DROP TABLE t1;
977
978 CREATE TABLE t1(
979 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
980 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
981
982 ALTER TABLE t1 DISCARD TABLESPACE;
983 --error ER_TABLESPACE_DISCARDED
984 SELECT * FROM t1;
985
986 perl;
987 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
988 ib_discard_tablespaces("test", "t1");
989 ib_restore_tablespaces("test", "t1");
990 EOF
991
992 --error ER_TABLE_SCHEMA_MISMATCH
993 ALTER TABLE t1 IMPORT TABLESPACE;
994
995 perl;
996 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
997 ib_unlink_tablespace("test", "t1");
998 EOF
999
1000 DROP TABLE t1;
1001
1002 CREATE TABLE t1(
1003 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
1004 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
1005 ALTER TABLE t1 DISCARD TABLESPACE;
1006 --error ER_TABLESPACE_DISCARDED
1007 SELECT * FROM t1;
1008
1009 perl;
1010 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
1011 ib_discard_tablespaces("test", "t1");
1012 ib_restore_tablespaces("test", "t1");
1013 EOF
1014
1015 --error ER_TABLE_SCHEMA_MISMATCH
1016 ALTER TABLE t1 IMPORT TABLESPACE;
1017
1018 perl;
1019 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
1020 ib_unlink_tablespace("test", "t1");
1021 EOF
1022
1023 DROP TABLE t1;
1024
1025
1026 CREATE TABLE t1(
1027 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
1028 c2 INT, INDEX idx(c2)) ENGINE=InnoDB
1029 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
1030 ALTER TABLE t1 DISCARD TABLESPACE;
1031 --error ER_TABLESPACE_DISCARDED
1032 SELECT * FROM t1;
1033
1034 perl;
1035 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
1036 ib_discard_tablespaces("test", "t1");
1037 ib_restore_tablespaces("test", "t1");
1038 EOF
1039
1040 --error ER_TABLE_SCHEMA_MISMATCH
1041 ALTER TABLE t1 IMPORT TABLESPACE;
1042
1043 perl;
1044 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
1045 ib_unlink_tablespace("test", "t1");
1046 EOF
1047
1048 DROP TABLE t1;
1049
1050 # This should be OK.
1051 CREATE TABLE t1(
1052 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
1053 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
1054
1055 ALTER TABLE t1 DISCARD TABLESPACE;
1056 --error ER_TABLESPACE_DISCARDED
1057 SELECT * FROM t1;
1058
1059 perl;
1060 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
1061 ib_discard_tablespaces("test", "t1");
1062 ib_restore_tablespaces("test", "t1");
1063 EOF
1064
1065 ALTER TABLE t1 IMPORT TABLESPACE;
1066 CHECK TABLE t1;
1067
1068 perl;
1069 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
1070 ib_cleanup("test", "t1");
1071 EOF
1072
1073 SHOW CREATE TABLE t1;
1074 SELECT * FROM t1;
1075
1076 DROP TABLE t1;
1077 --disable_query_log
1078 SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
1079 --enable_query_log
1080
1081
1082 CREATE TABLE t1 ( id INT NOT NULL, i1 INT, i2 INT, i3 INT, PRIMARY KEY (id)) engine=innodb;
1083 CREATE TABLE t2 ( id INT NOT NULL, i1 INT, i2 INT, PRIMARY KEY (id)) engine=innodb;
1084
1085 ALTER TABLE t2 DISCARD TABLESPACE;
1086 FLUSH TABLES t1 FOR EXPORT;
1087
1088 --copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd
1089 --copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t2.cfg
1090
1091 UNLOCK TABLES;
1092 --error ER_TABLE_SCHEMA_MISMATCH
1093 ALTER TABLE t2 IMPORT TABLESPACE;
1094
1095 DROP TABLE t1, t2;
1096
1097
1098 CREATE TABLE t1 ( id INT NOT NULL, i1 INT, i2 INT, PRIMARY KEY (id)) engine=innodb;
1099 CREATE TABLE t2 ( id INT NOT NULL, i1 INT, i2 INT, i3 INT, PRIMARY KEY (id)) engine=innodb;
1100
1101 ALTER TABLE t2 DISCARD TABLESPACE;
1102 FLUSH TABLES t1 FOR EXPORT;
1103
1104 --copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd
1105 --copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t2.cfg
1106
1107 UNLOCK TABLES;
1108 --error ER_TABLE_SCHEMA_MISMATCH
1109 ALTER TABLE t2 IMPORT TABLESPACE;
1110 DROP TABLE t1, t2;
1111
1112
1113 call mtr.add_suppression("Got error -1 when reading table '.*'");
1114 call mtr.add_suppression("InnoDB: Error: tablespace id and flags in file '.*'");
1115 call mtr.add_suppression("InnoDB: The table .* doesn't have a corresponding tablespace, it was discarded");