Debian Codegraph @

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

raw
1 --source include/no_valgrind_without_big.inc
2
3
4 --disable_query_log
5 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* Cannot calculate statistics for table `test`\.`t1` because the \.ibd file is missing");
6 call mtr.add_suppression("Got error -1 when reading table '.*'");
7 call mtr.add_suppression("Error: tablespace id and flags in file '.*'.*");
8 call mtr.add_suppression("The table .* doesn't have a corresponding tablespace, it was discarded");
9 call mtr.add_suppression("Unable to import tablespace `test`\.`t1` because it already exists. Please DISCARD the tablespace before IMPORT.");
10 --enable_query_log
11
12 --disable_warnings
13 DROP TABLE IF EXISTS t1;
14 --enable_warnings
15
16 let $innodb_file_per_table = `SELECT @@innodb_file_per_table`;
17
18 SET GLOBAL innodb_file_per_table = 1;
19 SELECT @@innodb_file_per_table;
20
21 let $MYSQLD_TMPDIR = `SELECT @@tmpdir`;
22 let $MYSQLD_DATADIR = `SELECT @@datadir`;
23
24 CREATE TABLE t1
25 (a INT AUTO_INCREMENT PRIMARY KEY,
26 b char(22),
27 c varchar(255),
28 KEY (b))
29 ENGINE = InnoDB;
30
31 insert into t1 (b, c) values ('Apa', 'Filler........'),
32 ('Banan', 'Filler........'), ('Cavalry', '..asdasdfaeraf'),
33 ('Devotion', 'asdfuihknaskdf'), ('Evolution', 'lsjndofiabsoibeg');
34
35 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
36 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
37 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
38 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
39 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
40 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
41 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
42 SELECT COUNT(*) FROM t1;
43 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
44 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
45
46 CREATE TABLE t2(a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT;
47
48 --list_files $MYSQLD_DATADIR/test
49 --source include/restart_mysqld.inc
50 FLUSH TABLE t1, t2 FOR EXPORT;
51 --echo # List before copying files
52 --list_files $MYSQLD_DATADIR/test
53 --copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_TMPDIR/t1.cfg
54 --copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_TMPDIR/t1.ibd
55 --move_file $MYSQLD_DATADIR/test/t2.cfg $MYSQLD_TMPDIR/t2.cfg
56 --copy_file $MYSQLD_DATADIR/test/t2.ibd $MYSQLD_TMPDIR/t2.ibd
57 UNLOCK TABLES;
58 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
59 SELECT COUNT(*) FROM t1;
60 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
61 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
62 --echo # Restarting server
63 -- source include/restart_mysqld.inc
64 --echo # Done restarting server
65 --echo # List before t1 DISCARD
66 --list_files $MYSQLD_DATADIR/test
67 ALTER TABLE t1 DISCARD TABLESPACE;
68 --echo # List after t1 DISCARD
69 --list_files $MYSQLD_DATADIR/test
70 --copy_file $MYSQLD_TMPDIR/t1.cfg $MYSQLD_DATADIR/test/t1.cfg
71 --copy_file $MYSQLD_TMPDIR/t1.ibd $MYSQLD_DATADIR/test/t1.ibd
72 ALTER TABLE t1 IMPORT TABLESPACE;
73 ALTER TABLE t1 ENGINE InnoDB;
74 SELECT COUNT(*) FROM t1;
75 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
76 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
77 --list_files $MYSQLD_DATADIR/test
78 SELECT COUNT(*) FROM t1;
79 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
80 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
81 DROP TABLE t1;
82 --remove_file $MYSQLD_TMPDIR/t1.cfg
83 --remove_file $MYSQLD_TMPDIR/t1.ibd
84
85 ALTER TABLE t2 ROW_FORMAT=DYNAMIC;
86 ALTER TABLE t2 DISCARD TABLESPACE;
87 --echo # List after t2 DISCARD
88 --list_files $MYSQLD_DATADIR/test
89 --move_file $MYSQLD_TMPDIR/t2.ibd $MYSQLD_DATADIR/test/t2.ibd
90 --move_file $MYSQLD_TMPDIR/t2.cfg $MYSQLD_DATADIR/test/t2.cfg
91 --error ER_TABLE_SCHEMA_MISMATCH
92 ALTER TABLE t2 IMPORT TABLESPACE;
93 --remove_file $MYSQLD_DATADIR/test/t2.cfg
94 --error ER_TABLE_SCHEMA_MISMATCH
95 ALTER TABLE t2 IMPORT TABLESPACE;
96 DROP TABLE t2;
97 --remove_file $MYSQLD_DATADIR/test/t2.ibd
98
99 SET GLOBAL innodb_file_per_table = 1;
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, with --innodb-file-per-table=1
116 CREATE TABLE t1(
117 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
118 c2 INT) ENGINE=InnoDB;
119
120 INSERT INTO t1(c2) VALUES(1);
121 INSERT INTO t1(c2) SELECT c2 FROM t1;
122 INSERT INTO t1(c2) SELECT c2 FROM t1;
123 INSERT INTO t1(c2) SELECT c2 FROM t1;
124 INSERT INTO t1(c2) SELECT c2 FROM t1;
125
126 --list_files $MYSQLD_DATADIR/test
127
128 FLUSH TABLES t1 FOR EXPORT;
129 SELECT COUNT(*) FROM t1;
130 perl;
131 require 'include/innodb-util.inc';
132 ib_backup_tablespaces("test", "t1");
133 EOF
134
135 --list_files $MYSQLD_DATADIR/test
136
137 UNLOCK TABLES;
138
139 DROP TABLE t1;
140
141 CREATE TABLE t1(
142 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
143 c2 INT) ENGINE=InnoDB;
144
145 ALTER TABLE t1 DISCARD TABLESPACE;
146 --error ER_TABLESPACE_DISCARDED
147 SELECT * FROM t1;
148
149 perl;
150 require 'include/innodb-util.inc';
151 ib_discard_tablespaces("test", "t1");
152 ib_restore_tablespaces("test", "t1");
153 EOF
154
155 --list_files $MYSQLD_DATADIR/test
156
157 ALTER TABLE t1 IMPORT TABLESPACE;
158 CHECK TABLE t1;
159
160 SELECT COUNT(*) FROM t1;
161
162 DROP TABLE t1;
163
164 # Export/import on the same instance, with --innodb-file-per-table=1
165 # Insert some more records to move the LSN forward and then drop the
166 # table and restore
167 CREATE TABLE t1(
168 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
169 c2 INT) ENGINE=InnoDB;
170
171 INSERT INTO t1(c2) VALUES(1);
172 INSERT INTO t1(c2) SELECT c2 FROM t1;
173 INSERT INTO t1(c2) SELECT c2 FROM t1;
174 INSERT INTO t1(c2) SELECT c2 FROM t1;
175 INSERT INTO t1(c2) SELECT c2 FROM t1;
176
177 --list_files $MYSQLD_DATADIR/test
178 FLUSH TABLES t1 FOR EXPORT;
179 SELECT COUNT(*) FROM t1;
180 perl;
181 require 'include/innodb-util.inc';
182 ib_backup_tablespaces("test", "t1");
183 EOF
184 --list_files $MYSQLD_DATADIR/test
185 UNLOCK TABLES;
186
187 --list_files $MYSQLD_DATADIR/test
188
189 # Move the LSN forward
190 INSERT INTO t1(c2) SELECT c2 FROM t1;
191 INSERT INTO t1(c2) SELECT c2 FROM t1;
192
193 DROP TABLE t1;
194
195 CREATE TABLE t1(
196 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
197 c2 INT) ENGINE=InnoDB;
198
199 ALTER TABLE t1 DISCARD TABLESPACE;
200 --error ER_TABLESPACE_DISCARDED
201 SELECT * FROM t1;
202
203 perl;
204 require 'include/innodb-util.inc';
205 ib_discard_tablespaces("test", "t1");
206 ib_restore_tablespaces("test", "t1");
207 EOF
208
209 --list_files $MYSQLD_DATADIR/test
210
211 ALTER TABLE t1 IMPORT TABLESPACE;
212 CHECK TABLE t1;
213
214 SELECT COUNT(*) FROM t1;
215
216 DROP TABLE t1;
217
218 # Export/import on the same instance, with --innodb-file-per-table=1
219 # Insert some more records to move the LSN forward and then drop the
220 # table and restore, this time the table has a secondary index too.
221 CREATE TABLE t1(
222 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
223 c2 INT, INDEX(c2)) ENGINE=InnoDB;
224
225 INSERT INTO t1(c2) VALUES(1);
226 INSERT INTO t1(c2) SELECT c2 FROM t1;
227 INSERT INTO t1(c2) SELECT c2 FROM t1;
228 INSERT INTO t1(c2) SELECT c2 FROM t1;
229 INSERT INTO t1(c2) SELECT c2 FROM t1;
230
231 FLUSH TABLES t1 FOR EXPORT;
232 SELECT COUNT(*) FROM t1 WHERE c2 = 1;
233 perl;
234 require 'include/innodb-util.inc';
235 ib_backup_tablespaces("test", "t1");
236 EOF
237 --list_files $MYSQLD_DATADIR/test
238 UNLOCK TABLES;
239
240 # Move the LSN forward
241 INSERT INTO t1(c2) SELECT c2 FROM t1;
242 INSERT INTO t1(c2) SELECT c2 FROM t1;
243
244 DROP TABLE t1;
245
246 CREATE TABLE t1(
247 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
248 c2 INT, INDEX(c2)) ENGINE=InnoDB;
249
250 ALTER TABLE t1 DISCARD TABLESPACE;
251 --error ER_TABLESPACE_DISCARDED
252 SELECT * FROM t1;
253
254 perl;
255 require 'include/innodb-util.inc';
256 ib_discard_tablespaces("test", "t1");
257 ib_restore_tablespaces("test", "t1");
258 EOF
259
260 ALTER TABLE t1 IMPORT TABLESPACE;
261 CHECK TABLE t1;
262
263 SELECT COUNT(*) FROM t1 WHERE c2 = 1;
264
265 DROP TABLE t1;
266
267 # Export/import on the same instance, with --innodb-file-per-table=1
268 # Insert some more records to move the LSN forward and then drop the
269 # table and restore, this time the table has a secondary index too.
270 # Rename the index on the create so that the IMPORT fails, drop index
271 # Create with proper name and then do an IMPORT.
272 CREATE TABLE t1(
273 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
274 c2 INT, INDEX idx(c2)) ENGINE=InnoDB;
275
276 INSERT INTO t1(c2) VALUES(1);
277 INSERT INTO t1(c2) SELECT c2 FROM t1;
278 INSERT INTO t1(c2) SELECT c2 FROM t1;
279 INSERT INTO t1(c2) SELECT c2 FROM t1;
280 INSERT INTO t1(c2) SELECT c2 FROM t1;
281
282 FLUSH TABLES t1 FOR EXPORT;
283 SELECT COUNT(*) FROM t1 WHERE c2 = 1;
284 perl;
285 require 'include/innodb-util.inc';
286 ib_backup_tablespaces("test", "t1");
287 EOF
288 UNLOCK TABLES;
289
290 # Move the LSN forward
291 INSERT INTO t1(c2) SELECT c2 FROM t1;
292 INSERT INTO t1(c2) SELECT c2 FROM t1;
293
294 DROP TABLE t1;
295
296 CREATE TABLE t1(
297 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
298 c2 INT, INDEX x(c2)) ENGINE=InnoDB;
299
300 ALTER TABLE t1 DISCARD TABLESPACE;
301 --error ER_TABLESPACE_DISCARDED
302 SELECT * FROM t1;
303
304 perl;
305 require 'include/innodb-util.inc';
306 ib_discard_tablespaces("test", "t1");
307 ib_restore_tablespaces("test", "t1");
308 EOF
309
310 # This is really a name mismatch error, need better error codes.
311 -- error ER_TABLE_SCHEMA_MISMATCH
312 ALTER TABLE t1 IMPORT TABLESPACE;
313
314 ALTER TABLE t1 DROP INDEX x;
315 ALTER TABLE t1 ADD INDEX idx(c2);
316
317 perl;
318 require 'include/innodb-util.inc';
319 ib_restore_tablespaces("test", "t1");
320 EOF
321
322 ALTER TABLE t1 IMPORT TABLESPACE;
323 CHECK TABLE t1;
324
325 SELECT * FROM t1;
326
327 perl;
328 require 'include/innodb-util.inc';
329 ib_cleanup("test", "t1");
330 EOF
331
332 DROP TABLE t1;
333
334 #
335 # Export/import on the same instance, with --innodb-file-per-table=0
336 # This should fail because it is not supported
337 SET GLOBAL innodb_file_per_table = 0;
338
339 CREATE TABLE t1(
340 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
341 c2 INT) ENGINE=InnoDB;
342
343 INSERT INTO t1(c2) VALUES(1);
344 INSERT INTO t1(c2) SELECT c2 FROM t1;
345 INSERT INTO t1(c2) SELECT c2 FROM t1;
346 INSERT INTO t1(c2) SELECT c2 FROM t1;
347 INSERT INTO t1(c2) SELECT c2 FROM t1;
348
349 SELECT COUNT(*) FROM t1;
350
351 SHOW CREATE TABLE t1;
352
353 # This should fail, InnoDB should return a warning
354 FLUSH TABLES t1 FOR EXPORT;
355
356 UNLOCK TABLES;
357
358 DROP TABLE t1;
359
360 #
361 # Tests that check for schema mismatch during IMPORT
362 #
363
364 SET GLOBAL innodb_file_per_table = 1;
365
366 CREATE TABLE t1(
367 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
368 c2 INT, INDEX idx(c2)) ENGINE=InnoDB;
369
370 INSERT INTO t1(c2) VALUES(1);
371 INSERT INTO t1(c2) SELECT c2 FROM t1;
372 INSERT INTO t1(c2) SELECT c2 FROM t1;
373 INSERT INTO t1(c2) SELECT c2 FROM t1;
374 INSERT INTO t1(c2) SELECT c2 FROM t1;
375 INSERT INTO t1(c2) SELECT c2 FROM t1;
376
377 SHOW CREATE TABLE t1;
378 SELECT * FROM t1;
379
380 FLUSH TABLES t1 FOR EXPORT;
381
382 perl;
383 require 'include/innodb-util.inc';
384 ib_backup_tablespaces("test", "t1");
385 EOF
386
387 UNLOCK TABLES;
388
389 DROP TABLE t1;
390
391 # Table without the secondary index
392 CREATE TABLE t1(
393 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
394 c2 INT) ENGINE=InnoDB;
395
396 ALTER TABLE t1 DISCARD TABLESPACE;
397 --error ER_TABLESPACE_DISCARDED
398 SELECT * FROM t1;
399
400 perl;
401 require 'include/innodb-util.inc';
402 ib_discard_tablespaces("test", "t1");
403 ib_restore_tablespaces("test", "t1");
404 EOF
405
406 # This should fail because of a missing secondary index
407 -- error ER_TABLE_SCHEMA_MISMATCH
408 ALTER TABLE t1 IMPORT TABLESPACE;
409
410 perl;
411 require 'include/innodb-util.inc';
412 ib_unlink_tablespace("test", "t1");
413 EOF
414
415 DROP TABLE t1;
416
417 # Table with an additional column
418 CREATE TABLE t1(
419 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
420 c2 INT,
421 c3 INT, INDEX idx(c2)) ENGINE=InnoDB;
422
423 ALTER TABLE t1 DISCARD TABLESPACE;
424 --error ER_TABLESPACE_DISCARDED
425 SELECT * FROM t1;
426
427 perl;
428 require 'include/innodb-util.inc';
429 ib_discard_tablespaces("test", "t1");
430 ib_restore_tablespaces("test", "t1");
431 EOF
432
433 # This should fail because the table has an additional column
434 -- error ER_TABLE_SCHEMA_MISMATCH
435 ALTER TABLE t1 IMPORT TABLESPACE;
436
437 perl;
438 require 'include/innodb-util.inc';
439 ib_unlink_tablespace("test", "t1");
440 EOF
441
442 DROP TABLE t1;
443
444 # Change the column type of c2
445 CREATE TABLE t1(
446 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
447 c2 BIGINT, INDEX idx(c2)) ENGINE=InnoDB;
448
449 ALTER TABLE t1 DISCARD TABLESPACE;
450 --error ER_TABLESPACE_DISCARDED
451 SELECT * FROM t1;
452
453 perl;
454 require 'include/innodb-util.inc';
455 ib_discard_tablespaces("test", "t1");
456 ib_restore_tablespaces("test", "t1");
457 EOF
458
459 # This should fail because c2 is now a BIGINT and not INT
460 -- error ER_TABLE_SCHEMA_MISMATCH
461 ALTER TABLE t1 IMPORT TABLESPACE;
462
463 perl;
464 require 'include/innodb-util.inc';
465 ib_unlink_tablespace("test", "t1");
466 EOF
467
468 DROP TABLE t1;
469
470 # This should be OK.
471 CREATE TABLE t1(
472 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
473 c2 INT, INDEX idx(c2)) ENGINE=InnoDB;
474
475 ALTER TABLE t1 DISCARD TABLESPACE;
476 --error ER_TABLESPACE_DISCARDED
477 SELECT * FROM t1;
478
479 perl;
480 require 'include/innodb-util.inc';
481 ib_discard_tablespaces("test", "t1");
482 ib_restore_tablespaces("test", "t1");
483 EOF
484
485 ALTER TABLE t1 IMPORT TABLESPACE;
486 CHECK TABLE t1;
487
488 perl;
489 require 'include/innodb-util.inc';
490 ib_cleanup("test", "t1");
491 EOF
492
493 SHOW CREATE TABLE t1;
494 SELECT * FROM t1;
495
496 DROP TABLE t1;
497
498 #
499 # Row format tests [EXPORT REDUNDANT - IMPORT COMPACT & DYNAMIC]
500 #
501 CREATE TABLE t1(
502 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
503 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
504
505 INSERT INTO t1(c2) VALUES(1);
506 INSERT INTO t1(c2) SELECT c2 FROM t1;
507 INSERT INTO t1(c2) SELECT c2 FROM t1;
508 INSERT INTO t1(c2) SELECT c2 FROM t1;
509 INSERT INTO t1(c2) SELECT c2 FROM t1;
510 INSERT INTO t1(c2) SELECT c2 FROM t1;
511
512 SHOW CREATE TABLE t1;
513 SELECT * FROM t1;
514
515 FLUSH TABLES t1 FOR EXPORT;
516
517 perl;
518 require 'include/innodb-util.inc';
519 ib_backup_tablespaces("test", "t1");
520 EOF
521
522 UNLOCK TABLES;
523
524 DROP TABLE t1;
525
526 CREATE TABLE t1(
527 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
528 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
529
530 ALTER TABLE t1 DISCARD TABLESPACE;
531 --error ER_TABLESPACE_DISCARDED
532 SELECT * FROM t1;
533
534 perl;
535 require 'include/innodb-util.inc';
536 ib_discard_tablespaces("test", "t1");
537 ib_restore_tablespaces("test", "t1");
538 EOF
539
540 ALTER TABLE t1 IMPORT TABLESPACE;
541 CHECK TABLE t1;
542
543 perl;
544 require 'include/innodb-util.inc';
545 ib_cleanup("test", "t1");
546 EOF
547
548 SHOW CREATE TABLE t1;
549 SELECT * FROM t1;
550
551 DROP TABLE t1;
552
553 CREATE TABLE t1(
554 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
555 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
556
557 ALTER TABLE t1 DISCARD TABLESPACE;
558 --error ER_TABLESPACE_DISCARDED
559 SELECT * FROM t1;
560
561 perl;
562 require 'include/innodb-util.inc';
563 ib_discard_tablespaces("test", "t1");
564 ib_restore_tablespaces("test", "t1");
565 EOF
566
567 --error ER_TABLE_SCHEMA_MISMATCH
568 ALTER TABLE t1 IMPORT TABLESPACE;
569
570 perl;
571 require 'include/innodb-util.inc';
572 ib_unlink_tablespace("test", "t1");
573 EOF
574
575 DROP TABLE t1;
576
577 CREATE TABLE t1(
578 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
579 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
580
581 ALTER TABLE t1 DISCARD TABLESPACE;
582 --error ER_TABLESPACE_DISCARDED
583 SELECT * FROM t1;
584
585 perl;
586 require 'include/innodb-util.inc';
587 ib_discard_tablespaces("test", "t1");
588 ib_restore_tablespaces("test", "t1");
589 EOF
590
591 --error ER_TABLE_SCHEMA_MISMATCH
592 ALTER TABLE t1 IMPORT TABLESPACE;
593
594 perl;
595 require 'include/innodb-util.inc';
596 ib_unlink_tablespace("test", "t1");
597 EOF
598
599 DROP TABLE t1;
600
601 # This should be OK.
602 CREATE TABLE t1(
603 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
604 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
605
606 ALTER TABLE t1 DISCARD TABLESPACE;
607 --error ER_TABLESPACE_DISCARDED
608 SELECT * FROM t1;
609
610 perl;
611 require 'include/innodb-util.inc';
612 ib_discard_tablespaces("test", "t1");
613 ib_restore_tablespaces("test", "t1");
614 EOF
615
616 ALTER TABLE t1 IMPORT TABLESPACE;
617
618 perl;
619 require 'include/innodb-util.inc';
620 ib_cleanup("test", "t1");
621 EOF
622
623 SHOW CREATE TABLE t1;
624 SELECT * FROM t1;
625
626 DROP TABLE t1;
627
628 #
629 # Row format tests [EXPORT COMPACT - IMPORT REDUNDANT & DYNAMIC]
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 INSERT INTO t1(c2) VALUES(1);
636 INSERT INTO t1(c2) SELECT c2 FROM t1;
637 INSERT INTO t1(c2) SELECT c2 FROM t1;
638 INSERT INTO t1(c2) SELECT c2 FROM t1;
639 INSERT INTO t1(c2) SELECT c2 FROM t1;
640 INSERT INTO t1(c2) SELECT c2 FROM t1;
641
642 SHOW CREATE TABLE t1;
643 SELECT * FROM t1;
644
645 FLUSH TABLES t1 FOR EXPORT;
646
647 perl;
648 require 'include/innodb-util.inc';
649 ib_backup_tablespaces("test", "t1");
650 EOF
651
652 UNLOCK TABLES;
653
654 DROP TABLE t1;
655
656 CREATE TABLE t1(
657 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
658 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
659
660 ALTER TABLE t1 DISCARD TABLESPACE;
661 --error ER_TABLESPACE_DISCARDED
662 SELECT * FROM t1;
663
664 perl;
665 require 'include/innodb-util.inc';
666 ib_discard_tablespaces("test", "t1");
667 ib_restore_tablespaces("test", "t1");
668 EOF
669
670 ALTER TABLE t1 IMPORT TABLESPACE;
671 CHECK TABLE t1;
672
673 perl;
674 require 'include/innodb-util.inc';
675 ib_cleanup("test", "t1");
676 EOF
677
678 SHOW CREATE TABLE t1;
679 SELECT * FROM t1;
680
681 DROP TABLE t1;
682
683 CREATE TABLE t1(
684 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
685 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
686
687 ALTER TABLE t1 DISCARD TABLESPACE;
688 --error ER_TABLESPACE_DISCARDED
689 SELECT * FROM t1;
690
691 perl;
692 require 'include/innodb-util.inc';
693 ib_discard_tablespaces("test", "t1");
694 ib_restore_tablespaces("test", "t1");
695 EOF
696
697 --error ER_TABLE_SCHEMA_MISMATCH
698 ALTER TABLE t1 IMPORT TABLESPACE;
699
700 perl;
701 require 'include/innodb-util.inc';
702 ib_unlink_tablespace("test", "t1");
703 EOF
704
705 DROP TABLE t1;
706
707 CREATE TABLE t1(
708 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
709 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
710
711 ALTER TABLE t1 DISCARD TABLESPACE;
712 --error ER_TABLESPACE_DISCARDED
713 SELECT * FROM t1;
714
715 perl;
716 require 'include/innodb-util.inc';
717 ib_discard_tablespaces("test", "t1");
718 ib_restore_tablespaces("test", "t1");
719 EOF
720
721 --error ER_TABLE_SCHEMA_MISMATCH
722 ALTER TABLE t1 IMPORT TABLESPACE;
723
724 perl;
725 require 'include/innodb-util.inc';
726 ib_unlink_tablespace("test", "t1");
727 EOF
728
729 DROP TABLE t1;
730
731 # This should be OK.
732 CREATE TABLE t1(
733 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
734 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
735
736 ALTER TABLE t1 DISCARD TABLESPACE;
737 --error ER_TABLESPACE_DISCARDED
738 SELECT * FROM t1;
739
740 perl;
741 require 'include/innodb-util.inc';
742 ib_discard_tablespaces("test", "t1");
743 ib_restore_tablespaces("test", "t1");
744 EOF
745
746 ALTER TABLE t1 IMPORT TABLESPACE;
747 CHECK TABLE t1;
748
749 perl;
750 require 'include/innodb-util.inc';
751 ib_cleanup("test", "t1");
752 EOF
753
754 SHOW CREATE TABLE t1;
755 SELECT * FROM t1;
756
757 DROP TABLE t1;
758
759 #
760 # Row format tests [EXPORT DYNAMIC- IMPORT REDUNDANT & DYNAMIC]
761 #
762 CREATE TABLE t1(
763 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
764 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
765
766 INSERT INTO t1(c2) VALUES(1);
767 INSERT INTO t1(c2) SELECT c2 FROM t1;
768 INSERT INTO t1(c2) SELECT c2 FROM t1;
769 INSERT INTO t1(c2) SELECT c2 FROM t1;
770 INSERT INTO t1(c2) SELECT c2 FROM t1;
771 INSERT INTO t1(c2) SELECT c2 FROM t1;
772
773 SHOW CREATE TABLE t1;
774 SELECT * FROM t1;
775
776 FLUSH TABLES t1 FOR EXPORT;
777
778 perl;
779 require 'include/innodb-util.inc';
780 ib_backup_tablespaces("test", "t1");
781 EOF
782
783 UNLOCK TABLES;
784
785 DROP TABLE t1;
786
787 CREATE TABLE t1(
788 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
789 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
790
791 ALTER TABLE t1 DISCARD TABLESPACE;
792 --error ER_TABLESPACE_DISCARDED
793 SELECT * FROM t1;
794
795 perl;
796 require 'include/innodb-util.inc';
797 ib_discard_tablespaces("test", "t1");
798 ib_restore_tablespaces("test", "t1");
799 EOF
800
801 ALTER TABLE t1 IMPORT TABLESPACE;
802 CHECK TABLE t1;
803
804 perl;
805 require 'include/innodb-util.inc';
806 ib_cleanup("test", "t1");
807 EOF
808
809 SHOW CREATE TABLE t1;
810 SELECT * FROM t1;
811
812 DROP TABLE t1;
813
814 CREATE TABLE t1(
815 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
816 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
817
818 ALTER TABLE t1 DISCARD TABLESPACE;
819 --error ER_TABLESPACE_DISCARDED
820 SELECT * FROM t1;
821
822 perl;
823 require 'include/innodb-util.inc';
824 ib_discard_tablespaces("test", "t1");
825 ib_restore_tablespaces("test", "t1");
826 EOF
827
828 --error ER_TABLE_SCHEMA_MISMATCH
829 ALTER TABLE t1 IMPORT TABLESPACE;
830
831 perl;
832 require 'include/innodb-util.inc';
833 ib_unlink_tablespace("test", "t1");
834 EOF
835
836 DROP TABLE t1;
837
838 CREATE TABLE t1(
839 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
840 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
841
842 ALTER TABLE t1 DISCARD TABLESPACE;
843 --error ER_TABLESPACE_DISCARDED
844 SELECT * FROM t1;
845
846 perl;
847 require 'include/innodb-util.inc';
848 ib_discard_tablespaces("test", "t1");
849 ib_restore_tablespaces("test", "t1");
850 EOF
851
852 --error ER_TABLE_SCHEMA_MISMATCH
853 ALTER TABLE t1 IMPORT TABLESPACE;
854
855 perl;
856 require 'include/innodb-util.inc';
857 ib_unlink_tablespace("test", "t1");
858 EOF
859
860 DROP TABLE t1;
861
862 # This should be OK.
863 CREATE TABLE t1(
864 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
865 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
866
867 ALTER TABLE t1 DISCARD TABLESPACE;
868 --error ER_TABLESPACE_DISCARDED
869 SELECT * FROM t1;
870
871 perl;
872 require 'include/innodb-util.inc';
873 ib_discard_tablespaces("test", "t1");
874 ib_restore_tablespaces("test", "t1");
875 EOF
876
877 ALTER TABLE t1 IMPORT TABLESPACE;
878 CHECK TABLE t1;
879
880 perl;
881 require 'include/innodb-util.inc';
882 ib_cleanup("test", "t1");
883 EOF
884
885 SHOW CREATE TABLE t1;
886 SELECT * FROM t1;
887
888 DROP TABLE t1;
889
890 --echo #
891 --echo # Bug#20631305 - INNODB: PREVENT FLUSH FOR TABLES IN A GENERAL TABLESPACE
892 --echo #
893 CREATE TABLESPACE s_def ADD DATAFILE 's_def.ibd';
894 CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB, TABLESPACE=s_def;
895 CREATE TABLE t2(a INT PRIMARY KEY);
896 FLUSH TABLE t1,t2 FOR EXPORT;
897 UNLOCK TABLES;
898 DROP TABLE t1;
899 DROP TABLE t2;
900 DROP TABLESPACE s_def;
901
902 # cleanup
903 --remove_file $MYSQLTEST_VARDIR/tmp/t1.cfg
904 --remove_file $MYSQLTEST_VARDIR/tmp/t1.ibd
905
906 eval SET GLOBAL INNODB_FILE_PER_TABLE=$innodb_file_per_table;