1call mtr.add_suppression("InnoDB: Unable to import tablespace .* because it already exists. Please DISCARD the tablespace before IMPORT\\."); 2call mtr.add_suppression("Index for table 't2' is corrupt; try to repair it"); 3FLUSH TABLES; 4CREATE TABLE t1 5(a INT AUTO_INCREMENT PRIMARY KEY, 6b char(22), 7c varchar(255), 8KEY (b)) 9ENGINE = InnoDB; 10CREATE TEMPORARY TABLE t (b char(22),c varchar(255)); 11INSERT INTO t VALUES 12('Apa', 'Filler........'), 13('Banan', 'Filler........'), ('Cavalry', '..asdasdfaeraf'), 14('Devotion', 'asdfuihknaskdf'), ('Evolution', 'lsjndofiabsoibeg'); 15INSERT INTO t1 (b,c) SELECT b,c FROM t,seq_1_to_128; 16DROP TEMPORARY TABLE t; 17SELECT COUNT(*) FROM t1; 18COUNT(*) 19640 20SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3; 21a b c 22636 Apa Filler........ 23631 Apa Filler........ 24626 Apa Filler........ 25SELECT * FROM t1 ORDER BY a DESC LIMIT 3; 26a b c 27640 Evolution lsjndofiabsoibeg 28639 Devotion asdfuihknaskdf 29638 Cavalry ..asdasdfaeraf 30CREATE TABLE t2(a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT; 31db.opt 32t1.frm 33t1.ibd 34t2.frm 35t2.ibd 36# restart 37FLUSH TABLE t1, t2 FOR EXPORT; 38# List before copying files 39db.opt 40t1.cfg 41t1.frm 42t1.ibd 43t2.cfg 44t2.frm 45t2.ibd 46UNLOCK TABLES; 47INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; 48SELECT COUNT(*) FROM t1; 49COUNT(*) 501280 51SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3; 52a b c 531276 Apa Filler........ 541271 Apa Filler........ 551266 Apa Filler........ 56SELECT * FROM t1 ORDER BY a DESC LIMIT 3; 57a b c 581280 Evolution lsjndofiabsoibeg 591279 Devotion asdfuihknaskdf 601278 Cavalry ..asdasdfaeraf 61# Restarting server 62# restart 63# Done restarting server 64# List before t1 DISCARD 65db.opt 66t1.frm 67t1.ibd 68t2.frm 69t2.ibd 70ALTER TABLE t1 DISCARD TABLESPACE; 71# List after t1 DISCARD 72db.opt 73t1.frm 74t2.frm 75t2.ibd 76ALTER TABLE t1 IMPORT TABLESPACE; 77ALTER TABLE t1 ENGINE InnoDB; 78SELECT COUNT(*) FROM t1; 79COUNT(*) 80640 81SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3; 82a b c 83636 Apa Filler........ 84631 Apa Filler........ 85626 Apa Filler........ 86SELECT * FROM t1 ORDER BY a DESC LIMIT 3; 87a b c 88640 Evolution lsjndofiabsoibeg 89639 Devotion asdfuihknaskdf 90638 Cavalry ..asdasdfaeraf 91db.opt 92t1.cfg 93t1.frm 94t1.ibd 95t2.frm 96t2.ibd 97SELECT COUNT(*) FROM t1; 98COUNT(*) 99640 100SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3; 101a b c 102636 Apa Filler........ 103631 Apa Filler........ 104626 Apa Filler........ 105SELECT * FROM t1 ORDER BY a DESC LIMIT 3; 106a b c 107640 Evolution lsjndofiabsoibeg 108639 Devotion asdfuihknaskdf 109638 Cavalry ..asdasdfaeraf 110DROP TABLE t1; 111ALTER TABLE t2 ROW_FORMAT=DYNAMIC; 112ALTER TABLE t2 DISCARD TABLESPACE; 113# List after t2 DISCARD 114db.opt 115t2.frm 116ALTER TABLE t2 IMPORT TABLESPACE; 117ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x1; .cfg file uses ROW_FORMAT=COMPACT) 118ALTER TABLE t2 IMPORT TABLESPACE; 119Warnings: 120Warning 1810 IO Read error: (2, No such file or directory) Error opening './test/t2.cfg', will attempt to import without schema verification 121DROP TABLE t2; 122SET GLOBAL innodb_file_per_table = 1; 123SELECT @@innodb_file_per_table; 124@@innodb_file_per_table 1251 126CREATE TABLE t1( 127c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 128c2 INT) ENGINE=InnoDB; 129INSERT INTO t1(c2) VALUES(1); 130ALTER TABLE t1 IMPORT TABLESPACE; 131ERROR HY000: Tablespace for table 'test/t1' exists. Please DISCARD the tablespace before IMPORT 132SELECT * FROM t1; 133c1 c2 1341 1 135DROP TABLE t1; 136CREATE TABLE t1( 137c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 138c2 INT) ENGINE=InnoDB; 139INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16; 140db.opt 141t1.frm 142t1.ibd 143FLUSH TABLES t1 FOR EXPORT; 144backup: t1 145db.opt 146t1.cfg 147t1.frm 148t1.ibd 149UNLOCK TABLES; 150DROP TABLE t1; 151CREATE TABLE t1( 152c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 153c2 INT) ENGINE=InnoDB; 154ALTER TABLE t1 DISCARD TABLESPACE; 155SELECT * FROM t1; 156ERROR HY000: Tablespace has been discarded for table `t1` 157restore: t1 .ibd and .cfg files 158db.opt 159t1.cfg 160t1.frm 161t1.ibd 162ALTER TABLE t1 IMPORT TABLESPACE; 163CHECK TABLE t1; 164Table Op Msg_type Msg_text 165test.t1 check status OK 166SELECT COUNT(*) FROM t1; 167COUNT(*) 16816 169DROP TABLE t1; 170CREATE TABLE t1( 171c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 172c2 INT) ENGINE=InnoDB; 173INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16; 174db.opt 175t1.frm 176t1.ibd 177FLUSH TABLES t1 FOR EXPORT; 178backup: t1 179db.opt 180t1.cfg 181t1.frm 182t1.ibd 183UNLOCK TABLES; 184db.opt 185t1.frm 186t1.ibd 187DROP TABLE t1; 188CREATE TABLE t1( 189c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 190c2 INT) ENGINE=InnoDB; 191ALTER TABLE t1 DISCARD TABLESPACE; 192SELECT * FROM t1; 193ERROR HY000: Tablespace has been discarded for table `t1` 194restore: t1 .ibd and .cfg files 195db.opt 196t1.cfg 197t1.frm 198t1.ibd 199ALTER TABLE t1 IMPORT TABLESPACE; 200CHECK TABLE t1; 201Table Op Msg_type Msg_text 202test.t1 check status OK 203SELECT COUNT(*) FROM t1; 204COUNT(*) 20516 206DROP TABLE t1; 207CREATE TABLE t1( 208c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 209c2 INT, INDEX(c2)) ENGINE=InnoDB; 210INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16; 211FLUSH TABLES t1 FOR EXPORT; 212backup: t1 213db.opt 214t1.cfg 215t1.frm 216t1.ibd 217UNLOCK TABLES; 218DROP TABLE t1; 219CREATE TABLE t1( 220c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 221c2 INT, INDEX(c2)) ENGINE=InnoDB; 222ALTER TABLE t1 DISCARD TABLESPACE; 223SELECT * FROM t1; 224ERROR HY000: Tablespace has been discarded for table `t1` 225restore: t1 .ibd and .cfg files 226ALTER TABLE t1 IMPORT TABLESPACE; 227CHECK TABLE t1; 228Table Op Msg_type Msg_text 229test.t1 check status OK 230SELECT COUNT(*) FROM t1 WHERE c2 = 1; 231COUNT(*) 23216 233DROP TABLE t1; 234CREATE TABLE t1( 235c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 236c2 INT, INDEX idx(c2)) ENGINE=InnoDB; 237INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16; 238FLUSH TABLES t1 FOR EXPORT; 239backup: t1 240UNLOCK TABLES; 241DROP TABLE t1; 242CREATE TABLE t1( 243c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 244c2 INT, INDEX x(c2)) ENGINE=InnoDB; 245ALTER TABLE t1 DISCARD TABLESPACE; 246SELECT * FROM t1; 247ERROR HY000: Tablespace has been discarded for table `t1` 248restore: t1 .ibd and .cfg files 249ALTER TABLE t1 IMPORT TABLESPACE; 250ERROR HY000: Schema mismatch (Index x not found in tablespace meta-data file.) 251select count(*) from t1; 252ERROR HY000: Tablespace has been discarded for table `t1` 253ALTER TABLE t1 DROP INDEX x; 254ALTER TABLE t1 DROP INDEX x, ALGORITHM=copy; 255ERROR 42000: Can't DROP INDEX `x`; check that it exists 256ALTER TABLE t1 ADD INDEX idx(c2); 257restore: t1 .ibd and .cfg files 258ALTER TABLE t1 IMPORT TABLESPACE; 259Warnings: 260Warning 1814 Tablespace has been discarded for table `t1` 261CHECK TABLE t1; 262Table Op Msg_type Msg_text 263test.t1 check status OK 264SELECT * FROM t1; 265c1 c2 2661 1 2672 1 2683 1 2694 1 2705 1 2716 1 2727 1 2738 1 2749 1 27510 1 27611 1 27712 1 27813 1 27914 1 28015 1 28116 1 282unlink: t1.cfg 283DROP TABLE t1; 284SET GLOBAL innodb_file_per_table = 0; 285CREATE TABLE t1( 286c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 287c2 INT) ENGINE=InnoDB; 288INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16; 289SHOW CREATE TABLE t1; 290Table Create Table 291t1 CREATE TABLE `t1` ( 292 `c1` int(11) NOT NULL AUTO_INCREMENT, 293 `c2` int(11) DEFAULT NULL, 294 PRIMARY KEY (`c1`) 295) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 296FLUSH TABLES t1 FOR EXPORT; 297Warnings: 298Warning 1809 Table `test`.`t1` in system tablespace 299UNLOCK TABLES; 300DROP TABLE t1; 301SET GLOBAL innodb_file_per_table = 1; 302CREATE TABLE t1( 303c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 304c2 INT, INDEX idx(c2)) ENGINE=InnoDB; 305INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16; 306SHOW CREATE TABLE t1; 307Table Create Table 308t1 CREATE TABLE `t1` ( 309 `c1` int(11) NOT NULL AUTO_INCREMENT, 310 `c2` int(11) DEFAULT NULL, 311 PRIMARY KEY (`c1`), 312 KEY `idx` (`c2`) 313) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 314FLUSH TABLES t1 FOR EXPORT; 315backup: t1 316UNLOCK TABLES; 317DROP TABLE t1; 318CREATE TABLE t1( 319c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 320c2 INT) ENGINE=InnoDB; 321ALTER TABLE t1 DISCARD TABLESPACE; 322SELECT * FROM t1; 323ERROR HY000: Tablespace has been discarded for table `t1` 324restore: t1 .ibd and .cfg files 325ALTER TABLE t1 IMPORT TABLESPACE; 326ERROR HY000: Schema mismatch (Number of indexes don't match, table has 1 indexes but the tablespace meta-data file has 2 indexes) 327unlink: t1.ibd 328unlink: t1.cfg 329DROP TABLE t1; 330CREATE TABLE t1( 331c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 332c2 INT, 333c3 INT, INDEX idx(c2)) ENGINE=InnoDB; 334ALTER TABLE t1 DISCARD TABLESPACE; 335SELECT * FROM t1; 336ERROR HY000: Tablespace has been discarded for table `t1` 337restore: t1 .ibd and .cfg files 338ALTER TABLE t1 IMPORT TABLESPACE; 339ERROR HY000: Schema mismatch (Column c3 not found in tablespace.) 340unlink: t1.ibd 341unlink: t1.cfg 342DROP TABLE t1; 343CREATE TABLE t1( 344c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 345c2 BIGINT, INDEX idx(c2)) ENGINE=InnoDB; 346ALTER TABLE t1 DISCARD TABLESPACE; 347SELECT * FROM t1; 348ERROR HY000: Tablespace has been discarded for table `t1` 349restore: t1 .ibd and .cfg files 350ALTER TABLE t1 IMPORT TABLESPACE; 351ERROR HY000: Schema mismatch (Column c2 precise type mismatch, it's 0X408 in the table and 0X403 in the tablespace meta file) 352unlink: t1.ibd 353unlink: t1.cfg 354DROP TABLE t1; 355CREATE TABLE t1( 356c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 357c2 INT, INDEX idx(c2)) ENGINE=InnoDB; 358ALTER TABLE t1 DISCARD TABLESPACE; 359SELECT * FROM t1; 360ERROR HY000: Tablespace has been discarded for table `t1` 361restore: t1 .ibd and .cfg files 362ALTER TABLE t1 IMPORT TABLESPACE; 363CHECK TABLE t1; 364Table Op Msg_type Msg_text 365test.t1 check status OK 366unlink: t1.cfg 367SHOW CREATE TABLE t1; 368Table Create Table 369t1 CREATE TABLE `t1` ( 370 `c1` int(11) NOT NULL AUTO_INCREMENT, 371 `c2` int(11) DEFAULT NULL, 372 PRIMARY KEY (`c1`), 373 KEY `idx` (`c2`) 374) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 375SELECT * FROM t1; 376c1 c2 3771 1 3782 1 3793 1 3804 1 3815 1 3826 1 3837 1 3848 1 3859 1 38610 1 38711 1 38812 1 38913 1 39014 1 39115 1 39216 1 393DROP TABLE t1; 394CREATE TABLE t1( 395c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 396c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; 397INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16; 398SHOW CREATE TABLE t1; 399Table Create Table 400t1 CREATE TABLE `t1` ( 401 `c1` int(11) NOT NULL AUTO_INCREMENT, 402 `c2` int(11) DEFAULT NULL, 403 PRIMARY KEY (`c1`), 404 KEY `idx` (`c2`) 405) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT 406FLUSH TABLES t1 FOR EXPORT; 407backup: t1 408UNLOCK TABLES; 409DROP TABLE t1; 410CREATE TABLE t1( 411c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 412c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; 413ALTER TABLE t1 DISCARD TABLESPACE; 414SELECT * FROM t1; 415ERROR HY000: Tablespace has been discarded for table `t1` 416restore: t1 .ibd and .cfg files 417ALTER TABLE t1 IMPORT TABLESPACE; 418CHECK TABLE t1; 419Table Op Msg_type Msg_text 420test.t1 check status OK 421unlink: t1.cfg 422SHOW CREATE TABLE t1; 423Table Create Table 424t1 CREATE TABLE `t1` ( 425 `c1` int(11) NOT NULL AUTO_INCREMENT, 426 `c2` int(11) DEFAULT NULL, 427 PRIMARY KEY (`c1`), 428 KEY `idx` (`c2`) 429) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT 430SELECT * FROM t1; 431c1 c2 4321 1 4332 1 4343 1 4354 1 4365 1 4376 1 4387 1 4398 1 4409 1 44110 1 44211 1 44312 1 44413 1 44514 1 44615 1 44716 1 448DROP TABLE t1; 449CREATE TABLE t1( 450c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 451c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT; 452ALTER TABLE t1 DISCARD TABLESPACE; 453SELECT * FROM t1; 454ERROR HY000: Tablespace has been discarded for table `t1` 455restore: t1 .ibd and .cfg files 456ALTER TABLE t1 IMPORT TABLESPACE; 457ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x1 and the meta-data file has 0x0; .cfg file uses ROW_FORMAT=REDUNDANT) 458unlink: t1.ibd 459unlink: t1.cfg 460DROP TABLE t1; 461CREATE TABLE t1( 462c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 463c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; 464ALTER TABLE t1 DISCARD TABLESPACE; 465SELECT * FROM t1; 466ERROR HY000: Tablespace has been discarded for table `t1` 467restore: t1 .ibd and .cfg files 468ALTER TABLE t1 IMPORT TABLESPACE; 469ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x0; .cfg file uses ROW_FORMAT=REDUNDANT) 470unlink: t1.ibd 471unlink: t1.cfg 472DROP TABLE t1; 473CREATE TABLE t1( 474c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 475c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; 476ALTER TABLE t1 DISCARD TABLESPACE; 477SELECT * FROM t1; 478ERROR HY000: Tablespace has been discarded for table `t1` 479restore: t1 .ibd and .cfg files 480ALTER TABLE t1 IMPORT TABLESPACE; 481ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x29 and the meta-data file has 0x0; .cfg file uses ROW_FORMAT=REDUNDANT) 482unlink: t1.ibd 483unlink: t1.cfg 484DROP TABLE t1; 485CREATE TABLE t1( 486c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 487c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; 488ALTER TABLE t1 DISCARD TABLESPACE; 489SELECT * FROM t1; 490ERROR HY000: Tablespace has been discarded for table `t1` 491restore: t1 .ibd and .cfg files 492ALTER TABLE t1 IMPORT TABLESPACE; 493unlink: t1.cfg 494SHOW CREATE TABLE t1; 495Table Create Table 496t1 CREATE TABLE `t1` ( 497 `c1` int(11) NOT NULL AUTO_INCREMENT, 498 `c2` int(11) DEFAULT NULL, 499 PRIMARY KEY (`c1`), 500 KEY `idx` (`c2`) 501) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT 502SELECT * FROM t1; 503c1 c2 5041 1 5052 1 5063 1 5074 1 5085 1 5096 1 5107 1 5118 1 5129 1 51310 1 51411 1 51512 1 51613 1 51714 1 51815 1 51916 1 520DROP TABLE t1; 521CREATE TABLE t1( 522c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 523c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT; 524INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16; 525SHOW CREATE TABLE t1; 526Table Create Table 527t1 CREATE TABLE `t1` ( 528 `c1` int(11) NOT NULL AUTO_INCREMENT, 529 `c2` int(11) DEFAULT NULL, 530 PRIMARY KEY (`c1`), 531 KEY `idx` (`c2`) 532) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT 533FLUSH TABLES t1 FOR EXPORT; 534backup: t1 535UNLOCK TABLES; 536DROP TABLE t1; 537CREATE TABLE t1( 538c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 539c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT; 540ALTER TABLE t1 DISCARD TABLESPACE; 541SELECT * FROM t1; 542ERROR HY000: Tablespace has been discarded for table `t1` 543restore: t1 .ibd and .cfg files 544ALTER TABLE t1 IMPORT TABLESPACE; 545CHECK TABLE t1; 546Table Op Msg_type Msg_text 547test.t1 check status OK 548unlink: t1.cfg 549SHOW CREATE TABLE t1; 550Table Create Table 551t1 CREATE TABLE `t1` ( 552 `c1` int(11) NOT NULL AUTO_INCREMENT, 553 `c2` int(11) DEFAULT NULL, 554 PRIMARY KEY (`c1`), 555 KEY `idx` (`c2`) 556) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT 557SELECT * FROM t1; 558c1 c2 5591 1 5602 1 5613 1 5624 1 5635 1 5646 1 5657 1 5668 1 5679 1 56810 1 56911 1 57012 1 57113 1 57214 1 57315 1 57416 1 575DROP TABLE t1; 576CREATE TABLE t1( 577c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 578c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; 579ALTER TABLE t1 DISCARD TABLESPACE; 580SELECT * FROM t1; 581ERROR HY000: Tablespace has been discarded for table `t1` 582restore: t1 .ibd and .cfg files 583ALTER TABLE t1 IMPORT TABLESPACE; 584ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x0 and the meta-data file has 0x1; .cfg file uses ROW_FORMAT=COMPACT) 585unlink: t1.ibd 586unlink: t1.cfg 587DROP TABLE t1; 588CREATE TABLE t1( 589c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 590c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; 591ALTER TABLE t1 DISCARD TABLESPACE; 592SELECT * FROM t1; 593ERROR HY000: Tablespace has been discarded for table `t1` 594restore: t1 .ibd and .cfg files 595ALTER TABLE t1 IMPORT TABLESPACE; 596ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x1; .cfg file uses ROW_FORMAT=COMPACT) 597unlink: t1.ibd 598unlink: t1.cfg 599DROP TABLE t1; 600CREATE TABLE t1( 601c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 602c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; 603ALTER TABLE t1 DISCARD TABLESPACE; 604SELECT * FROM t1; 605ERROR HY000: Tablespace has been discarded for table `t1` 606restore: t1 .ibd and .cfg files 607ALTER TABLE t1 IMPORT TABLESPACE; 608ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x29 and the meta-data file has 0x1; .cfg file uses ROW_FORMAT=COMPACT) 609unlink: t1.ibd 610unlink: t1.cfg 611DROP TABLE t1; 612CREATE TABLE t1( 613c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 614c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT; 615ALTER TABLE t1 DISCARD TABLESPACE; 616SELECT * FROM t1; 617ERROR HY000: Tablespace has been discarded for table `t1` 618restore: t1 .ibd and .cfg files 619ALTER TABLE t1 IMPORT TABLESPACE; 620CHECK TABLE t1; 621Table Op Msg_type Msg_text 622test.t1 check status OK 623unlink: t1.cfg 624SHOW CREATE TABLE t1; 625Table Create Table 626t1 CREATE TABLE `t1` ( 627 `c1` int(11) NOT NULL AUTO_INCREMENT, 628 `c2` int(11) DEFAULT NULL, 629 PRIMARY KEY (`c1`), 630 KEY `idx` (`c2`) 631) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT 632SELECT * FROM t1; 633c1 c2 6341 1 6352 1 6363 1 6374 1 6385 1 6396 1 6407 1 6418 1 6429 1 64310 1 64411 1 64512 1 64613 1 64714 1 64815 1 64916 1 650DROP TABLE t1; 651CREATE TABLE t1( 652c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 653c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; 654INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16; 655SHOW CREATE TABLE t1; 656Table Create Table 657t1 CREATE TABLE `t1` ( 658 `c1` int(11) NOT NULL AUTO_INCREMENT, 659 `c2` int(11) DEFAULT NULL, 660 PRIMARY KEY (`c1`), 661 KEY `idx` (`c2`) 662) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC 663FLUSH TABLES t1 FOR EXPORT; 664backup: t1 665UNLOCK TABLES; 666DROP TABLE t1; 667CREATE TABLE t1( 668c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 669c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; 670ALTER TABLE t1 DISCARD TABLESPACE; 671SELECT * FROM t1; 672ERROR HY000: Tablespace has been discarded for table `t1` 673restore: t1 .ibd and .cfg files 674ALTER TABLE t1 IMPORT TABLESPACE; 675CHECK TABLE t1; 676Table Op Msg_type Msg_text 677test.t1 check status OK 678unlink: t1.cfg 679SHOW CREATE TABLE t1; 680Table Create Table 681t1 CREATE TABLE `t1` ( 682 `c1` int(11) NOT NULL AUTO_INCREMENT, 683 `c2` int(11) DEFAULT NULL, 684 PRIMARY KEY (`c1`), 685 KEY `idx` (`c2`) 686) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC 687SELECT * FROM t1; 688c1 c2 6891 1 6902 1 6913 1 6924 1 6935 1 6946 1 6957 1 6968 1 6979 1 69810 1 69911 1 70012 1 70113 1 70214 1 70315 1 70416 1 705DROP TABLE t1; 706CREATE TABLE t1( 707c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 708c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT; 709ALTER TABLE t1 DISCARD TABLESPACE; 710SELECT * FROM t1; 711ERROR HY000: Tablespace has been discarded for table `t1` 712restore: t1 .ibd and .cfg files 713ALTER TABLE t1 IMPORT TABLESPACE; 714ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x1 and the meta-data file has 0x21; .cfg file uses ROW_FORMAT=DYNAMIC) 715unlink: t1.ibd 716unlink: t1.cfg 717DROP TABLE t1; 718CREATE TABLE t1( 719c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 720c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; 721ALTER TABLE t1 DISCARD TABLESPACE; 722SELECT * FROM t1; 723ERROR HY000: Tablespace has been discarded for table `t1` 724restore: t1 .ibd and .cfg files 725ALTER TABLE t1 IMPORT TABLESPACE; 726ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x0 and the meta-data file has 0x21; .cfg file uses ROW_FORMAT=DYNAMIC) 727unlink: t1.ibd 728unlink: t1.cfg 729DROP TABLE t1; 730CREATE TABLE t1( 731c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 732c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; 733ALTER TABLE t1 DISCARD TABLESPACE; 734SELECT * FROM t1; 735ERROR HY000: Tablespace has been discarded for table `t1` 736restore: t1 .ibd and .cfg files 737ALTER TABLE t1 IMPORT TABLESPACE; 738ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x29 and the meta-data file has 0x21; .cfg file uses ROW_FORMAT=DYNAMIC) 739unlink: t1.ibd 740unlink: t1.cfg 741DROP TABLE t1; 742CREATE TABLE t1( 743c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 744c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; 745ALTER TABLE t1 DISCARD TABLESPACE; 746SELECT * FROM t1; 747ERROR HY000: Tablespace has been discarded for table `t1` 748restore: t1 .ibd and .cfg files 749ALTER TABLE t1 IMPORT TABLESPACE; 750CHECK TABLE t1; 751Table Op Msg_type Msg_text 752test.t1 check status OK 753unlink: t1.cfg 754SHOW CREATE TABLE t1; 755Table Create Table 756t1 CREATE TABLE `t1` ( 757 `c1` int(11) NOT NULL AUTO_INCREMENT, 758 `c2` int(11) DEFAULT NULL, 759 PRIMARY KEY (`c1`), 760 KEY `idx` (`c2`) 761) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC 762SELECT * FROM t1; 763c1 c2 7641 1 7652 1 7663 1 7674 1 7685 1 7696 1 7707 1 7718 1 7729 1 77310 1 77411 1 77512 1 77613 1 77714 1 77815 1 77916 1 780DROP TABLE t1; 781CREATE TABLE t1( 782c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 783c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; 784INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16; 785SHOW CREATE TABLE t1; 786Table Create Table 787t1 CREATE TABLE `t1` ( 788 `c1` int(11) NOT NULL AUTO_INCREMENT, 789 `c2` int(11) DEFAULT NULL, 790 PRIMARY KEY (`c1`), 791 KEY `idx` (`c2`) 792) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED 793FLUSH TABLES t1 FOR EXPORT; 794backup: t1 795UNLOCK TABLES; 796DROP TABLE t1; 797CREATE TABLE t1( 798c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 799c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; 800ALTER TABLE t1 DISCARD TABLESPACE; 801SELECT * FROM t1; 802ERROR HY000: Tablespace has been discarded for table `t1` 803restore: t1 .ibd and .cfg files 804ALTER TABLE t1 IMPORT TABLESPACE; 805CHECK TABLE t1; 806Table Op Msg_type Msg_text 807test.t1 check status OK 808unlink: t1.cfg 809SHOW CREATE TABLE t1; 810Table Create Table 811t1 CREATE TABLE `t1` ( 812 `c1` int(11) NOT NULL AUTO_INCREMENT, 813 `c2` int(11) DEFAULT NULL, 814 PRIMARY KEY (`c1`), 815 KEY `idx` (`c2`) 816) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED 817SELECT * FROM t1; 818c1 c2 8191 1 8202 1 8213 1 8224 1 8235 1 8246 1 8257 1 8268 1 8279 1 82810 1 82911 1 83012 1 83113 1 83214 1 83315 1 83416 1 835DROP TABLE t1; 836CREATE TABLE t1( 837c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 838c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; 839ALTER TABLE t1 DISCARD TABLESPACE; 840SELECT * FROM t1; 841ERROR HY000: Tablespace has been discarded for table `t1` 842restore: t1 .ibd and .cfg files 843ALTER TABLE t1 IMPORT TABLESPACE; 844ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x0 and the meta-data file has 0x29; .cfg file uses ROW_FORMAT=COMPRESSED) 845unlink: t1.ibd 846unlink: t1.cfg 847DROP TABLE t1; 848CREATE TABLE t1( 849c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 850c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT; 851ALTER TABLE t1 DISCARD TABLESPACE; 852SELECT * FROM t1; 853ERROR HY000: Tablespace has been discarded for table `t1` 854restore: t1 .ibd and .cfg files 855ALTER TABLE t1 IMPORT TABLESPACE; 856ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x1 and the meta-data file has 0x29; .cfg file uses ROW_FORMAT=COMPRESSED) 857unlink: t1.ibd 858unlink: t1.cfg 859DROP TABLE t1; 860CREATE TABLE t1( 861c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 862c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; 863ALTER TABLE t1 DISCARD TABLESPACE; 864SELECT * FROM t1; 865ERROR HY000: Tablespace has been discarded for table `t1` 866restore: t1 .ibd and .cfg files 867ALTER TABLE t1 IMPORT TABLESPACE; 868ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x29; .cfg file uses ROW_FORMAT=COMPRESSED) 869unlink: t1.ibd 870unlink: t1.cfg 871DROP TABLE t1; 872CREATE TABLE t1( 873c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 874c2 INT, INDEX idx(c2)) ENGINE=InnoDB 875ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; 876ALTER TABLE t1 DISCARD TABLESPACE; 877SELECT * FROM t1; 878ERROR HY000: Tablespace has been discarded for table `t1` 879restore: t1 .ibd and .cfg files 880ALTER TABLE t1 IMPORT TABLESPACE; 881ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x23 and the meta-data file has 0x29; .cfg file uses ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8) 882unlink: t1.ibd 883unlink: t1.cfg 884DROP TABLE t1; 885CREATE TABLE t1( 886c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 887c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; 888ALTER TABLE t1 DISCARD TABLESPACE; 889SELECT * FROM t1; 890ERROR HY000: Tablespace has been discarded for table `t1` 891restore: t1 .ibd and .cfg files 892ALTER TABLE t1 IMPORT TABLESPACE; 893CHECK TABLE t1; 894Table Op Msg_type Msg_text 895test.t1 check status OK 896unlink: t1.cfg 897SHOW CREATE TABLE t1; 898Table Create Table 899t1 CREATE TABLE `t1` ( 900 `c1` int(11) NOT NULL AUTO_INCREMENT, 901 `c2` int(11) DEFAULT NULL, 902 PRIMARY KEY (`c1`), 903 KEY `idx` (`c2`) 904) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED 905SELECT * FROM t1; 906c1 c2 9071 1 9082 1 9093 1 9104 1 9115 1 9126 1 9137 1 9148 1 9159 1 91610 1 91711 1 91812 1 91913 1 92014 1 92115 1 92216 1 923DROP TABLE t1; 924CREATE TABLE t1 ( id INT NOT NULL, i1 INT, i2 INT, i3 INT, PRIMARY KEY (id)) engine=innodb; 925CREATE TABLE t2 ( id INT NOT NULL, i1 INT, i2 INT, PRIMARY KEY (id)) engine=innodb; 926ALTER TABLE t2 DISCARD TABLESPACE; 927FLUSH TABLES t1 FOR EXPORT; 928UNLOCK TABLES; 929ALTER TABLE t2 IMPORT TABLESPACE; 930ERROR HY000: Schema mismatch (Column DB_ROW_ID ordinal value mismatch, it's at 3 in the table and 4 in the tablespace meta-data file) 931DROP TABLE t1, t2; 932CREATE TABLE t1 ( id INT NOT NULL, i1 INT, i2 INT, PRIMARY KEY (id)) engine=innodb; 933CREATE TABLE t2 ( id INT NOT NULL, i1 INT, i2 INT, i3 INT, PRIMARY KEY (id)) engine=innodb; 934ALTER TABLE t2 DISCARD TABLESPACE; 935FLUSH TABLES t1 FOR EXPORT; 936UNLOCK TABLES; 937ALTER TABLE t2 IMPORT TABLESPACE; 938ERROR HY000: Schema mismatch (Column i3 not found in tablespace.) 939DROP TABLE t1, t2; 940call mtr.add_suppression("Got error -1 when reading table '.*'"); 941call mtr.add_suppression("InnoDB: Error: tablespace id and flags in file '.*'"); 942call mtr.add_suppression("InnoDB: The table .* doesn't have a corresponding tablespace, it was discarded"); 943