Debian Codegraph @

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

raw
1 DROP TABLE IF EXISTS t1;
2 SET GLOBAL innodb_file_per_table = 1;
3 SELECT @@innodb_file_per_table;
4 @@innodb_file_per_table
5 1
6 CREATE TABLE t1
7 (a INT AUTO_INCREMENT PRIMARY KEY,
8 b char(22),
9 c varchar(255),
10 KEY (b))
11 ENGINE = InnoDB;
12 insert into t1 (b, c) values ('Apa', 'Filler........'),
13 ('Banan', 'Filler........'), ('Cavalry', '..asdasdfaeraf'),
14 ('Devotion', 'asdfuihknaskdf'), ('Evolution', 'lsjndofiabsoibeg');
15 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
16 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
17 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
18 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
19 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
20 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
21 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
22 SELECT COUNT(*) FROM t1;
23 COUNT(*)
24 640
25 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
26 a b c
27 819 Apa Filler........
28 814 Apa Filler........
29 809 Apa Filler........
30 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
31 a b c
32 823 Evolution lsjndofiabsoibeg
33 822 Devotion asdfuihknaskdf
34 821 Cavalry ..asdasdfaeraf
35 CREATE TABLE t2(a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT;
36 t1.ibd
37 t2.ibd
38 # restart
39 FLUSH TABLE t1, t2 FOR EXPORT;
40 # List before copying files
41 t1.cfg
42 t1.ibd
43 t2.cfg
44 t2.ibd
45 UNLOCK TABLES;
46 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
47 SELECT COUNT(*) FROM t1;
48 COUNT(*)
49 1280
50 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
51 a b c
52 1459 Apa Filler........
53 1454 Apa Filler........
54 1449 Apa Filler........
55 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
56 a b c
57 1463 Evolution lsjndofiabsoibeg
58 1462 Devotion asdfuihknaskdf
59 1461 Cavalry ..asdasdfaeraf
60 # Restarting server
61 # restart
62 # Done restarting server
63 # List before t1 DISCARD
64 t1.ibd
65 t2.ibd
66 ALTER TABLE t1 DISCARD TABLESPACE;
67 # List after t1 DISCARD
68 t2.ibd
69 ALTER TABLE t1 IMPORT TABLESPACE;
70 ALTER TABLE t1 ENGINE InnoDB;
71 SELECT COUNT(*) FROM t1;
72 COUNT(*)
73 640
74 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
75 a b c
76 819 Apa Filler........
77 814 Apa Filler........
78 809 Apa Filler........
79 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
80 a b c
81 823 Evolution lsjndofiabsoibeg
82 822 Devotion asdfuihknaskdf
83 821 Cavalry ..asdasdfaeraf
84 t1.cfg
85 t1.ibd
86 t2.ibd
87 SELECT COUNT(*) FROM t1;
88 COUNT(*)
89 640
90 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
91 a b c
92 819 Apa Filler........
93 814 Apa Filler........
94 809 Apa Filler........
95 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
96 a b c
97 823 Evolution lsjndofiabsoibeg
98 822 Devotion asdfuihknaskdf
99 821 Cavalry ..asdasdfaeraf
100 DROP TABLE t1;
101 ALTER TABLE t2 ROW_FORMAT=DYNAMIC;
102 ALTER TABLE t2 DISCARD TABLESPACE;
103 # List after t2 DISCARD
104 ALTER TABLE t2 IMPORT TABLESPACE;
105 ERROR HY000: Schema mismatch (Table flags don't match, server table has ROW_TYPE_DYNAMIC and the meta-data file has ROW_TYPE_COMPACT)
106 ALTER TABLE t2 IMPORT TABLESPACE;
107 ERROR HY000: Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)
108 DROP TABLE t2;
109 SET GLOBAL innodb_file_per_table = 1;
110 SELECT @@innodb_file_per_table;
111 @@innodb_file_per_table
112 1
113 CREATE TABLE t1(
114 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
115 c2 INT) ENGINE=InnoDB;
116 INSERT INTO t1(c2) VALUES(1);
117 ALTER TABLE t1 IMPORT TABLESPACE;
118 ERROR HY000: Tablespace 'test/t1' exists.
119 SELECT * FROM t1;
120 c1 c2
121 1 1
122 DROP TABLE t1;
123 CREATE TABLE t1(
124 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
125 c2 INT) ENGINE=InnoDB;
126 INSERT INTO t1(c2) VALUES(1);
127 INSERT INTO t1(c2) SELECT c2 FROM t1;
128 INSERT INTO t1(c2) SELECT c2 FROM t1;
129 INSERT INTO t1(c2) SELECT c2 FROM t1;
130 INSERT INTO t1(c2) SELECT c2 FROM t1;
131 t1.ibd
132 FLUSH TABLES t1 FOR EXPORT;
133 SELECT COUNT(*) FROM t1;
134 COUNT(*)
135 16
136 backup: t1
137 t1.cfg
138 t1.ibd
139 UNLOCK TABLES;
140 DROP TABLE t1;
141 CREATE TABLE t1(
142 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
143 c2 INT) ENGINE=InnoDB;
144 ALTER TABLE t1 DISCARD TABLESPACE;
145 SELECT * FROM t1;
146 ERROR HY000: Tablespace has been discarded for table 't1'
147 restore: t1 .ibd and .cfg files
148 t1.cfg
149 t1.ibd
150 ALTER TABLE t1 IMPORT TABLESPACE;
151 CHECK TABLE t1;
152 Table Op Msg_type Msg_text
153 test.t1 check status OK
154 SELECT COUNT(*) FROM t1;
155 COUNT(*)
156 16
157 DROP TABLE t1;
158 CREATE TABLE t1(
159 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
160 c2 INT) ENGINE=InnoDB;
161 INSERT INTO t1(c2) VALUES(1);
162 INSERT INTO t1(c2) SELECT c2 FROM t1;
163 INSERT INTO t1(c2) SELECT c2 FROM t1;
164 INSERT INTO t1(c2) SELECT c2 FROM t1;
165 INSERT INTO t1(c2) SELECT c2 FROM t1;
166 t1.ibd
167 FLUSH TABLES t1 FOR EXPORT;
168 SELECT COUNT(*) FROM t1;
169 COUNT(*)
170 16
171 backup: t1
172 t1.cfg
173 t1.ibd
174 UNLOCK TABLES;
175 t1.ibd
176 INSERT INTO t1(c2) SELECT c2 FROM t1;
177 INSERT INTO t1(c2) SELECT c2 FROM t1;
178 DROP TABLE t1;
179 CREATE TABLE t1(
180 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
181 c2 INT) ENGINE=InnoDB;
182 ALTER TABLE t1 DISCARD TABLESPACE;
183 SELECT * FROM t1;
184 ERROR HY000: Tablespace has been discarded for table 't1'
185 restore: t1 .ibd and .cfg files
186 t1.cfg
187 t1.ibd
188 ALTER TABLE t1 IMPORT TABLESPACE;
189 CHECK TABLE t1;
190 Table Op Msg_type Msg_text
191 test.t1 check status OK
192 SELECT COUNT(*) FROM t1;
193 COUNT(*)
194 16
195 DROP TABLE t1;
196 CREATE TABLE t1(
197 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
198 c2 INT, INDEX(c2)) ENGINE=InnoDB;
199 INSERT INTO t1(c2) VALUES(1);
200 INSERT INTO t1(c2) SELECT c2 FROM t1;
201 INSERT INTO t1(c2) SELECT c2 FROM t1;
202 INSERT INTO t1(c2) SELECT c2 FROM t1;
203 INSERT INTO t1(c2) SELECT c2 FROM t1;
204 FLUSH TABLES t1 FOR EXPORT;
205 SELECT COUNT(*) FROM t1 WHERE c2 = 1;
206 COUNT(*)
207 16
208 backup: t1
209 t1.cfg
210 t1.ibd
211 UNLOCK TABLES;
212 INSERT INTO t1(c2) SELECT c2 FROM t1;
213 INSERT INTO t1(c2) SELECT c2 FROM t1;
214 DROP TABLE t1;
215 CREATE TABLE t1(
216 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
217 c2 INT, INDEX(c2)) ENGINE=InnoDB;
218 ALTER TABLE t1 DISCARD TABLESPACE;
219 SELECT * FROM t1;
220 ERROR HY000: Tablespace has been discarded for table 't1'
221 restore: t1 .ibd and .cfg files
222 ALTER TABLE t1 IMPORT TABLESPACE;
223 CHECK TABLE t1;
224 Table Op Msg_type Msg_text
225 test.t1 check status OK
226 SELECT COUNT(*) FROM t1 WHERE c2 = 1;
227 COUNT(*)
228 16
229 DROP TABLE t1;
230 CREATE TABLE t1(
231 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
232 c2 INT, INDEX idx(c2)) ENGINE=InnoDB;
233 INSERT INTO t1(c2) VALUES(1);
234 INSERT INTO t1(c2) SELECT c2 FROM t1;
235 INSERT INTO t1(c2) SELECT c2 FROM t1;
236 INSERT INTO t1(c2) SELECT c2 FROM t1;
237 INSERT INTO t1(c2) SELECT c2 FROM t1;
238 FLUSH TABLES t1 FOR EXPORT;
239 SELECT COUNT(*) FROM t1 WHERE c2 = 1;
240 COUNT(*)
241 16
242 backup: t1
243 UNLOCK TABLES;
244 INSERT INTO t1(c2) SELECT c2 FROM t1;
245 INSERT INTO t1(c2) SELECT c2 FROM t1;
246 DROP TABLE t1;
247 CREATE TABLE t1(
248 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
249 c2 INT, INDEX x(c2)) ENGINE=InnoDB;
250 ALTER TABLE t1 DISCARD TABLESPACE;
251 SELECT * FROM t1;
252 ERROR HY000: Tablespace has been discarded for table 't1'
253 restore: t1 .ibd and .cfg files
254 ALTER TABLE t1 IMPORT TABLESPACE;
255 ERROR HY000: Schema mismatch (Index x not found in tablespace meta-data file.)
256 ALTER TABLE t1 DROP INDEX x;
257 Warnings:
258 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
259 ALTER TABLE t1 ADD INDEX idx(c2);
260 Warnings:
261 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
262 restore: t1 .ibd and .cfg files
263 ALTER TABLE t1 IMPORT TABLESPACE;
264 CHECK TABLE t1;
265 Table Op Msg_type Msg_text
266 test.t1 check status OK
267 SELECT * FROM t1;
268 c1 c2
269 1 1
270 2 1
271 3 1
272 4 1
273 6 1
274 7 1
275 8 1
276 9 1
277 13 1
278 14 1
279 15 1
280 16 1
281 17 1
282 18 1
283 19 1
284 20 1
285 unlink: t1.cfg
286 DROP TABLE t1;
287 SET GLOBAL innodb_file_per_table = 0;
288 CREATE TABLE t1(
289 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
290 c2 INT) ENGINE=InnoDB;
291 INSERT INTO t1(c2) VALUES(1);
292 INSERT INTO t1(c2) SELECT c2 FROM t1;
293 INSERT INTO t1(c2) SELECT c2 FROM t1;
294 INSERT INTO t1(c2) SELECT c2 FROM t1;
295 INSERT INTO t1(c2) SELECT c2 FROM t1;
296 SELECT COUNT(*) FROM t1;
297 COUNT(*)
298 16
299 SHOW CREATE TABLE t1;
300 Table Create Table
301 t1 CREATE TABLE `t1` (
302 `c1` int NOT NULL AUTO_INCREMENT,
303 `c2` int DEFAULT NULL,
304 PRIMARY KEY (`c1`)
305 ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
306 FLUSH TABLES t1 FOR EXPORT;
307 Warnings:
308 Warning 1809 InnoDB: Table '`test`.`t1`' in system tablespace
309 UNLOCK TABLES;
310 DROP TABLE t1;
311 SET GLOBAL innodb_file_per_table = 1;
312 CREATE TABLE t1(
313 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
314 c2 INT, INDEX idx(c2)) ENGINE=InnoDB;
315 INSERT INTO t1(c2) VALUES(1);
316 INSERT INTO t1(c2) SELECT c2 FROM t1;
317 INSERT INTO t1(c2) SELECT c2 FROM t1;
318 INSERT INTO t1(c2) SELECT c2 FROM t1;
319 INSERT INTO t1(c2) SELECT c2 FROM t1;
320 INSERT INTO t1(c2) SELECT c2 FROM t1;
321 SHOW CREATE TABLE t1;
322 Table Create Table
323 t1 CREATE TABLE `t1` (
324 `c1` int NOT NULL AUTO_INCREMENT,
325 `c2` int DEFAULT NULL,
326 PRIMARY KEY (`c1`),
327 KEY `idx` (`c2`)
328 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
329 SELECT * FROM t1;
330 c1 c2
331 1 1
332 2 1
333 3 1
334 4 1
335 6 1
336 7 1
337 8 1
338 9 1
339 13 1
340 14 1
341 15 1
342 16 1
343 17 1
344 18 1
345 19 1
346 20 1
347 28 1
348 29 1
349 30 1
350 31 1
351 32 1
352 33 1
353 34 1
354 35 1
355 36 1
356 37 1
357 38 1
358 39 1
359 40 1
360 41 1
361 42 1
362 43 1
363 FLUSH TABLES t1 FOR EXPORT;
364 backup: t1
365 UNLOCK TABLES;
366 DROP TABLE t1;
367 CREATE TABLE t1(
368 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
369 c2 INT) ENGINE=InnoDB;
370 ALTER TABLE t1 DISCARD TABLESPACE;
371 SELECT * FROM t1;
372 ERROR HY000: Tablespace has been discarded for table 't1'
373 restore: t1 .ibd and .cfg files
374 ALTER TABLE t1 IMPORT TABLESPACE;
375 ERROR HY000: Schema mismatch (Number of indexes don't match, table has 1 indexes but the tablespace meta-data file has 3 indexes)
376 unlink: t1.ibd
377 unlink: t1.cfg
378 DROP TABLE t1;
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 ALTER TABLE t1 DISCARD TABLESPACE;
384 SELECT * FROM t1;
385 ERROR HY000: Tablespace has been discarded for table 't1'
386 restore: t1 .ibd and .cfg files
387 ALTER TABLE t1 IMPORT TABLESPACE;
388 ERROR HY000: Schema mismatch (Number of columns don't match, table has 6 columns but the tablespace meta-data file has 5 columns)
389 unlink: t1.ibd
390 unlink: t1.cfg
391 DROP TABLE t1;
392 CREATE TABLE t1(
393 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
394 c2 BIGINT, INDEX idx(c2)) ENGINE=InnoDB;
395 ALTER TABLE t1 DISCARD TABLESPACE;
396 SELECT * FROM t1;
397 ERROR HY000: Tablespace has been discarded for table 't1'
398 restore: t1 .ibd and .cfg files
399 ALTER TABLE t1 IMPORT TABLESPACE;
400 ERROR HY000: Schema mismatch (Column c2 precise type mismatch.)
401 unlink: t1.ibd
402 unlink: t1.cfg
403 DROP TABLE t1;
404 CREATE TABLE t1(
405 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
406 c2 INT, INDEX idx(c2)) ENGINE=InnoDB;
407 ALTER TABLE t1 DISCARD TABLESPACE;
408 SELECT * FROM t1;
409 ERROR HY000: Tablespace has been discarded for table 't1'
410 restore: t1 .ibd and .cfg files
411 ALTER TABLE t1 IMPORT TABLESPACE;
412 CHECK TABLE t1;
413 Table Op Msg_type Msg_text
414 test.t1 check status OK
415 unlink: t1.cfg
416 SHOW CREATE TABLE t1;
417 Table Create Table
418 t1 CREATE TABLE `t1` (
419 `c1` int NOT NULL AUTO_INCREMENT,
420 `c2` int DEFAULT NULL,
421 PRIMARY KEY (`c1`),
422 KEY `idx` (`c2`)
423 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
424 SELECT * FROM t1;
425 c1 c2
426 1 1
427 2 1
428 3 1
429 4 1
430 6 1
431 7 1
432 8 1
433 9 1
434 13 1
435 14 1
436 15 1
437 16 1
438 17 1
439 18 1
440 19 1
441 20 1
442 28 1
443 29 1
444 30 1
445 31 1
446 32 1
447 33 1
448 34 1
449 35 1
450 36 1
451 37 1
452 38 1
453 39 1
454 40 1
455 41 1
456 42 1
457 43 1
458 DROP TABLE t1;
459 CREATE TABLE t1(
460 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
461 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
462 INSERT INTO t1(c2) VALUES(1);
463 INSERT INTO t1(c2) SELECT c2 FROM t1;
464 INSERT INTO t1(c2) SELECT c2 FROM t1;
465 INSERT INTO t1(c2) SELECT c2 FROM t1;
466 INSERT INTO t1(c2) SELECT c2 FROM t1;
467 INSERT INTO t1(c2) SELECT c2 FROM t1;
468 SHOW CREATE TABLE t1;
469 Table Create Table
470 t1 CREATE TABLE `t1` (
471 `c1` int NOT NULL AUTO_INCREMENT,
472 `c2` int DEFAULT NULL,
473 PRIMARY KEY (`c1`),
474 KEY `idx` (`c2`)
475 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
476 SELECT * FROM t1;
477 c1 c2
478 1 1
479 2 1
480 3 1
481 4 1
482 6 1
483 7 1
484 8 1
485 9 1
486 13 1
487 14 1
488 15 1
489 16 1
490 17 1
491 18 1
492 19 1
493 20 1
494 28 1
495 29 1
496 30 1
497 31 1
498 32 1
499 33 1
500 34 1
501 35 1
502 36 1
503 37 1
504 38 1
505 39 1
506 40 1
507 41 1
508 42 1
509 43 1
510 FLUSH TABLES t1 FOR EXPORT;
511 backup: t1
512 UNLOCK TABLES;
513 DROP TABLE t1;
514 CREATE TABLE t1(
515 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
516 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
517 ALTER TABLE t1 DISCARD TABLESPACE;
518 SELECT * FROM t1;
519 ERROR HY000: Tablespace has been discarded for table 't1'
520 restore: t1 .ibd and .cfg files
521 ALTER TABLE t1 IMPORT TABLESPACE;
522 CHECK TABLE t1;
523 Table Op Msg_type Msg_text
524 test.t1 check status OK
525 unlink: t1.cfg
526 SHOW CREATE TABLE t1;
527 Table Create Table
528 t1 CREATE TABLE `t1` (
529 `c1` int NOT NULL AUTO_INCREMENT,
530 `c2` int DEFAULT NULL,
531 PRIMARY KEY (`c1`),
532 KEY `idx` (`c2`)
533 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
534 SELECT * FROM t1;
535 c1 c2
536 1 1
537 2 1
538 3 1
539 4 1
540 6 1
541 7 1
542 8 1
543 9 1
544 13 1
545 14 1
546 15 1
547 16 1
548 17 1
549 18 1
550 19 1
551 20 1
552 28 1
553 29 1
554 30 1
555 31 1
556 32 1
557 33 1
558 34 1
559 35 1
560 36 1
561 37 1
562 38 1
563 39 1
564 40 1
565 41 1
566 42 1
567 43 1
568 DROP TABLE t1;
569 CREATE TABLE t1(
570 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
571 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
572 ALTER TABLE t1 DISCARD TABLESPACE;
573 SELECT * FROM t1;
574 ERROR HY000: Tablespace has been discarded for table 't1'
575 restore: t1 .ibd and .cfg files
576 ALTER TABLE t1 IMPORT TABLESPACE;
577 ERROR HY000: Schema mismatch (Table flags don't match, server table has ROW_TYPE_COMPACT and the meta-data file has ROW_TYPE_REDUNDANT)
578 unlink: t1.ibd
579 unlink: t1.cfg
580 DROP TABLE t1;
581 CREATE TABLE t1(
582 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
583 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
584 ALTER TABLE t1 DISCARD TABLESPACE;
585 SELECT * FROM t1;
586 ERROR HY000: Tablespace has been discarded for table 't1'
587 restore: t1 .ibd and .cfg files
588 ALTER TABLE t1 IMPORT TABLESPACE;
589 ERROR HY000: Schema mismatch (Table flags don't match, server table has ROW_TYPE_DYNAMIC and the meta-data file has ROW_TYPE_REDUNDANT)
590 unlink: t1.ibd
591 unlink: t1.cfg
592 DROP TABLE t1;
593 CREATE TABLE t1(
594 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
595 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
596 ALTER TABLE t1 DISCARD TABLESPACE;
597 SELECT * FROM t1;
598 ERROR HY000: Tablespace has been discarded for table 't1'
599 restore: t1 .ibd and .cfg files
600 ALTER TABLE t1 IMPORT TABLESPACE;
601 unlink: t1.cfg
602 SHOW CREATE TABLE t1;
603 Table Create Table
604 t1 CREATE TABLE `t1` (
605 `c1` int NOT NULL AUTO_INCREMENT,
606 `c2` int DEFAULT NULL,
607 PRIMARY KEY (`c1`),
608 KEY `idx` (`c2`)
609 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
610 SELECT * FROM t1;
611 c1 c2
612 1 1
613 2 1
614 3 1
615 4 1
616 6 1
617 7 1
618 8 1
619 9 1
620 13 1
621 14 1
622 15 1
623 16 1
624 17 1
625 18 1
626 19 1
627 20 1
628 28 1
629 29 1
630 30 1
631 31 1
632 32 1
633 33 1
634 34 1
635 35 1
636 36 1
637 37 1
638 38 1
639 39 1
640 40 1
641 41 1
642 42 1
643 43 1
644 DROP TABLE t1;
645 CREATE TABLE t1(
646 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
647 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
648 INSERT INTO t1(c2) VALUES(1);
649 INSERT INTO t1(c2) SELECT c2 FROM t1;
650 INSERT INTO t1(c2) SELECT c2 FROM t1;
651 INSERT INTO t1(c2) SELECT c2 FROM t1;
652 INSERT INTO t1(c2) SELECT c2 FROM t1;
653 INSERT INTO t1(c2) SELECT c2 FROM t1;
654 SHOW CREATE TABLE t1;
655 Table Create Table
656 t1 CREATE TABLE `t1` (
657 `c1` int NOT NULL AUTO_INCREMENT,
658 `c2` int DEFAULT NULL,
659 PRIMARY KEY (`c1`),
660 KEY `idx` (`c2`)
661 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
662 SELECT * FROM t1;
663 c1 c2
664 1 1
665 2 1
666 3 1
667 4 1
668 6 1
669 7 1
670 8 1
671 9 1
672 13 1
673 14 1
674 15 1
675 16 1
676 17 1
677 18 1
678 19 1
679 20 1
680 28 1
681 29 1
682 30 1
683 31 1
684 32 1
685 33 1
686 34 1
687 35 1
688 36 1
689 37 1
690 38 1
691 39 1
692 40 1
693 41 1
694 42 1
695 43 1
696 FLUSH TABLES t1 FOR EXPORT;
697 backup: t1
698 UNLOCK TABLES;
699 DROP TABLE t1;
700 CREATE TABLE t1(
701 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
702 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
703 ALTER TABLE t1 DISCARD TABLESPACE;
704 SELECT * FROM t1;
705 ERROR HY000: Tablespace has been discarded for table 't1'
706 restore: t1 .ibd and .cfg files
707 ALTER TABLE t1 IMPORT TABLESPACE;
708 CHECK TABLE t1;
709 Table Op Msg_type Msg_text
710 test.t1 check status OK
711 unlink: t1.cfg
712 SHOW CREATE TABLE t1;
713 Table Create Table
714 t1 CREATE TABLE `t1` (
715 `c1` int NOT NULL AUTO_INCREMENT,
716 `c2` int DEFAULT NULL,
717 PRIMARY KEY (`c1`),
718 KEY `idx` (`c2`)
719 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
720 SELECT * FROM t1;
721 c1 c2
722 1 1
723 2 1
724 3 1
725 4 1
726 6 1
727 7 1
728 8 1
729 9 1
730 13 1
731 14 1
732 15 1
733 16 1
734 17 1
735 18 1
736 19 1
737 20 1
738 28 1
739 29 1
740 30 1
741 31 1
742 32 1
743 33 1
744 34 1
745 35 1
746 36 1
747 37 1
748 38 1
749 39 1
750 40 1
751 41 1
752 42 1
753 43 1
754 DROP TABLE t1;
755 CREATE TABLE t1(
756 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
757 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
758 ALTER TABLE t1 DISCARD TABLESPACE;
759 SELECT * FROM t1;
760 ERROR HY000: Tablespace has been discarded for table 't1'
761 restore: t1 .ibd and .cfg files
762 ALTER TABLE t1 IMPORT TABLESPACE;
763 ERROR HY000: Schema mismatch (Table flags don't match, server table has ROW_TYPE_REDUNDANT and the meta-data file has ROW_TYPE_COMPACT)
764 unlink: t1.ibd
765 unlink: t1.cfg
766 DROP TABLE t1;
767 CREATE TABLE t1(
768 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
769 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
770 ALTER TABLE t1 DISCARD TABLESPACE;
771 SELECT * FROM t1;
772 ERROR HY000: Tablespace has been discarded for table 't1'
773 restore: t1 .ibd and .cfg files
774 ALTER TABLE t1 IMPORT TABLESPACE;
775 ERROR HY000: Schema mismatch (Table flags don't match, server table has ROW_TYPE_DYNAMIC and the meta-data file has ROW_TYPE_COMPACT)
776 unlink: t1.ibd
777 unlink: t1.cfg
778 DROP TABLE t1;
779 CREATE TABLE t1(
780 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
781 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
782 ALTER TABLE t1 DISCARD TABLESPACE;
783 SELECT * FROM t1;
784 ERROR HY000: Tablespace has been discarded for table 't1'
785 restore: t1 .ibd and .cfg files
786 ALTER TABLE t1 IMPORT TABLESPACE;
787 CHECK TABLE t1;
788 Table Op Msg_type Msg_text
789 test.t1 check status OK
790 unlink: t1.cfg
791 SHOW CREATE TABLE t1;
792 Table Create Table
793 t1 CREATE TABLE `t1` (
794 `c1` int NOT NULL AUTO_INCREMENT,
795 `c2` int DEFAULT NULL,
796 PRIMARY KEY (`c1`),
797 KEY `idx` (`c2`)
798 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
799 SELECT * FROM t1;
800 c1 c2
801 1 1
802 2 1
803 3 1
804 4 1
805 6 1
806 7 1
807 8 1
808 9 1
809 13 1
810 14 1
811 15 1
812 16 1
813 17 1
814 18 1
815 19 1
816 20 1
817 28 1
818 29 1
819 30 1
820 31 1
821 32 1
822 33 1
823 34 1
824 35 1
825 36 1
826 37 1
827 38 1
828 39 1
829 40 1
830 41 1
831 42 1
832 43 1
833 DROP TABLE t1;
834 CREATE TABLE t1(
835 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
836 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
837 INSERT INTO t1(c2) VALUES(1);
838 INSERT INTO t1(c2) SELECT c2 FROM t1;
839 INSERT INTO t1(c2) SELECT c2 FROM t1;
840 INSERT INTO t1(c2) SELECT c2 FROM t1;
841 INSERT INTO t1(c2) SELECT c2 FROM t1;
842 INSERT INTO t1(c2) SELECT c2 FROM t1;
843 SHOW CREATE TABLE t1;
844 Table Create Table
845 t1 CREATE TABLE `t1` (
846 `c1` int NOT NULL AUTO_INCREMENT,
847 `c2` int DEFAULT NULL,
848 PRIMARY KEY (`c1`),
849 KEY `idx` (`c2`)
850 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
851 SELECT * FROM t1;
852 c1 c2
853 1 1
854 2 1
855 3 1
856 4 1
857 6 1
858 7 1
859 8 1
860 9 1
861 13 1
862 14 1
863 15 1
864 16 1
865 17 1
866 18 1
867 19 1
868 20 1
869 28 1
870 29 1
871 30 1
872 31 1
873 32 1
874 33 1
875 34 1
876 35 1
877 36 1
878 37 1
879 38 1
880 39 1
881 40 1
882 41 1
883 42 1
884 43 1
885 FLUSH TABLES t1 FOR EXPORT;
886 backup: t1
887 UNLOCK TABLES;
888 DROP TABLE t1;
889 CREATE TABLE t1(
890 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
891 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
892 ALTER TABLE t1 DISCARD TABLESPACE;
893 SELECT * FROM t1;
894 ERROR HY000: Tablespace has been discarded for table 't1'
895 restore: t1 .ibd and .cfg files
896 ALTER TABLE t1 IMPORT TABLESPACE;
897 CHECK TABLE t1;
898 Table Op Msg_type Msg_text
899 test.t1 check status OK
900 unlink: t1.cfg
901 SHOW CREATE TABLE t1;
902 Table Create Table
903 t1 CREATE TABLE `t1` (
904 `c1` int NOT NULL AUTO_INCREMENT,
905 `c2` int DEFAULT NULL,
906 PRIMARY KEY (`c1`),
907 KEY `idx` (`c2`)
908 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
909 SELECT * FROM t1;
910 c1 c2
911 1 1
912 2 1
913 3 1
914 4 1
915 6 1
916 7 1
917 8 1
918 9 1
919 13 1
920 14 1
921 15 1
922 16 1
923 17 1
924 18 1
925 19 1
926 20 1
927 28 1
928 29 1
929 30 1
930 31 1
931 32 1
932 33 1
933 34 1
934 35 1
935 36 1
936 37 1
937 38 1
938 39 1
939 40 1
940 41 1
941 42 1
942 43 1
943 DROP TABLE t1;
944 CREATE TABLE t1(
945 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
946 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
947 ALTER TABLE t1 DISCARD TABLESPACE;
948 SELECT * FROM t1;
949 ERROR HY000: Tablespace has been discarded for table 't1'
950 restore: t1 .ibd and .cfg files
951 ALTER TABLE t1 IMPORT TABLESPACE;
952 ERROR HY000: Schema mismatch (Table flags don't match, server table has ROW_TYPE_COMPACT and the meta-data file has ROW_TYPE_DYNAMIC)
953 unlink: t1.ibd
954 unlink: t1.cfg
955 DROP TABLE t1;
956 CREATE TABLE t1(
957 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
958 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
959 ALTER TABLE t1 DISCARD TABLESPACE;
960 SELECT * FROM t1;
961 ERROR HY000: Tablespace has been discarded for table 't1'
962 restore: t1 .ibd and .cfg files
963 ALTER TABLE t1 IMPORT TABLESPACE;
964 ERROR HY000: Schema mismatch (Table flags don't match, server table has ROW_TYPE_REDUNDANT and the meta-data file has ROW_TYPE_DYNAMIC)
965 unlink: t1.ibd
966 unlink: t1.cfg
967 DROP TABLE t1;
968 CREATE TABLE t1(
969 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
970 c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
971 ALTER TABLE t1 DISCARD TABLESPACE;
972 SELECT * FROM t1;
973 ERROR HY000: Tablespace has been discarded for table 't1'
974 restore: t1 .ibd and .cfg files
975 ALTER TABLE t1 IMPORT TABLESPACE;
976 CHECK TABLE t1;
977 Table Op Msg_type Msg_text
978 test.t1 check status OK
979 unlink: t1.cfg
980 SHOW CREATE TABLE t1;
981 Table Create Table
982 t1 CREATE TABLE `t1` (
983 `c1` int NOT NULL AUTO_INCREMENT,
984 `c2` int DEFAULT NULL,
985 PRIMARY KEY (`c1`),
986 KEY `idx` (`c2`)
987 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
988 SELECT * FROM t1;
989 c1 c2
990 1 1
991 2 1
992 3 1
993 4 1
994 6 1
995 7 1
996 8 1
997 9 1
998 13 1
999 14 1
1000 15 1
1001 16 1
1002 17 1
1003 18 1
1004 19 1
1005 20 1
1006 28 1
1007 29 1
1008 30 1
1009 31 1
1010 32 1
1011 33 1
1012 34 1
1013 35 1
1014 36 1
1015 37 1
1016 38 1
1017 39 1
1018 40 1
1019 41 1
1020 42 1
1021 43 1
1022 DROP TABLE t1;
1023 #
1024 # Bug#20631305 - INNODB: PREVENT FLUSH FOR TABLES IN A GENERAL TABLESPACE
1025 #
1026 CREATE TABLESPACE s_def ADD DATAFILE 's_def.ibd';
1027 CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB, TABLESPACE=s_def;
1028 CREATE TABLE t2(a INT PRIMARY KEY);
1029 FLUSH TABLE t1,t2 FOR EXPORT;
1030 Warnings:
1031 Warning 1235 InnoDB: This version of MySQL doesn't yet support 'FLUSH TABLES FOR EXPORT on table `test`.`t1` in a general tablespace.'
1032 UNLOCK TABLES;
1033 DROP TABLE t1;
1034 DROP TABLE t2;
1035 DROP TABLESPACE s_def;
1036 SET GLOBAL INNODB_FILE_PER_TABLE=1;