EVOLUTION-MANAGER

Edit File: wl5522_zip.result

call mtr.add_suppression("InnoDB: Unable to import tablespace .* because it already exists. Please DISCARD the tablespace before IMPORT."); SET SESSION innodb_strict_mode=1; CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b char(22), c varchar(255), KEY (b)) ENGINE = InnoDB ROW_FORMAT=COMPRESSED ; insert into t1 (b, c) values ('Apa', 'Filler........'), ('Banan', 'Filler........'), ('Cavalry', '..asdasdfaeraf'), ('Devotion', 'asdfuihknaskdf'), ('Evolution', 'lsjndofiabsoibeg'); INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; SELECT COUNT(*) FROM t1; COUNT(*) 640 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3; a b c 819 Apa Filler........ 814 Apa Filler........ 809 Apa Filler........ SELECT * FROM t1 ORDER BY a DESC LIMIT 3; a b c 823 Evolution lsjndofiabsoibeg 822 Devotion asdfuihknaskdf 821 Cavalry ..asdasdfaeraf db.opt t1.frm t1.ibd # Restarting server # Done restarting server FLUSH TABLE t1 FOR EXPORT; # List before copying files db.opt t1.cfg t1.frm t1.ibd UNLOCK TABLES; INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; SELECT COUNT(*) FROM t1; COUNT(*) 1280 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3; a b c 1459 Apa Filler........ 1454 Apa Filler........ 1449 Apa Filler........ SELECT * FROM t1 ORDER BY a DESC LIMIT 3; a b c 1463 Evolution lsjndofiabsoibeg 1462 Devotion asdfuihknaskdf 1461 Cavalry ..asdasdfaeraf # Restarting server # Done restarting server # List before t1 DISCARD db.opt t1.frm t1.ibd ALTER TABLE t1 DISCARD TABLESPACE; # List after t1 DISCARD db.opt t1.frm ALTER TABLE t1 IMPORT TABLESPACE; ALTER TABLE t1 ENGINE InnoDB; SELECT COUNT(*) FROM t1; COUNT(*) 640 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3; a b c 819 Apa Filler........ 814 Apa Filler........ 809 Apa Filler........ SELECT * FROM t1 ORDER BY a DESC LIMIT 3; a b c 823 Evolution lsjndofiabsoibeg 822 Devotion asdfuihknaskdf 821 Cavalry ..asdasdfaeraf db.opt t1.cfg t1.frm t1.ibd SELECT COUNT(*) FROM t1; COUNT(*) 640 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3; a b c 819 Apa Filler........ 814 Apa Filler........ 809 Apa Filler........ SELECT * FROM t1 ORDER BY a DESC LIMIT 3; a b c 823 Evolution lsjndofiabsoibeg 822 Devotion asdfuihknaskdf 821 Cavalry ..asdasdfaeraf DROP TABLE t1; SET SESSION innodb_strict_mode=1; CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; INSERT INTO t1(c2) VALUES(1); ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Tablespace for table 'test/t1' exists. Please DISCARD the tablespace before IMPORT SELECT * FROM t1; c1 c2 1 1 DROP TABLE t1; CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; INSERT INTO t1(c2) VALUES(1); INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; db.opt t1.frm t1.ibd FLUSH TABLES t1 FOR EXPORT; SELECT COUNT(*) FROM t1; COUNT(*) 16 backup: t1 db.opt t1.cfg t1.frm t1.ibd UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files db.opt t1.cfg t1.frm t1.ibd ALTER TABLE t1 IMPORT TABLESPACE; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT COUNT(*) FROM t1; COUNT(*) 16 DROP TABLE t1; CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; INSERT INTO t1(c2) VALUES(1); INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; db.opt t1.frm t1.ibd FLUSH TABLES t1 FOR EXPORT; SELECT COUNT(*) FROM t1; COUNT(*) 16 backup: t1 db.opt t1.cfg t1.frm t1.ibd UNLOCK TABLES; db.opt t1.frm t1.ibd INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; DROP TABLE t1; CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files db.opt t1.cfg t1.frm t1.ibd ALTER TABLE t1 IMPORT TABLESPACE; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT COUNT(*) FROM t1; COUNT(*) 16 DROP TABLE t1; CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; INSERT INTO t1(c2) VALUES(1); INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; FLUSH TABLES t1 FOR EXPORT; SELECT COUNT(*) FROM t1 WHERE c2 = 1; COUNT(*) 16 backup: t1 db.opt t1.cfg t1.frm t1.ibd UNLOCK TABLES; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; DROP TABLE t1; CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT COUNT(*) FROM t1 WHERE c2 = 1; COUNT(*) 16 DROP TABLE t1; CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; INSERT INTO t1(c2) VALUES(1); INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; FLUSH TABLES t1 FOR EXPORT; SELECT COUNT(*) FROM t1 WHERE c2 = 1; COUNT(*) 16 backup: t1 UNLOCK TABLES; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; DROP TABLE t1; CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT, INDEX x(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (Index x not found in tablespace meta-data file.) ALTER TABLE t1 DROP INDEX x; Warnings: Warning 1814 Tablespace has been discarded for table `t1` ALTER TABLE t1 ADD INDEX idx(c2); Warnings: Warning 1814 Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SELECT * FROM t1; c1 c2 1 1 2 1 3 1 4 1 6 1 7 1 8 1 9 1 13 1 14 1 15 1 16 1 17 1 18 1 19 1 20 1 unlink: t1.cfg DROP TABLE t1; SET GLOBAL innodb_file_per_table = 0; CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT) ENGINE=InnoDB; INSERT INTO t1(c2) VALUES(1); INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; SELECT COUNT(*) FROM t1; COUNT(*) 16 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 FLUSH TABLES t1 FOR EXPORT; Warnings: Warning 1809 Table `test`.`t1` in system tablespace UNLOCK TABLES; DROP TABLE t1; SET GLOBAL innodb_file_per_table = 1; CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; INSERT INTO t1(c2) VALUES(1); INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`c1`), KEY `idx` (`c2`) ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED SELECT * FROM t1; c1 c2 1 1 2 1 3 1 4 1 6 1 7 1 8 1 9 1 13 1 14 1 15 1 16 1 17 1 18 1 19 1 20 1 28 1 29 1 30 1 31 1 32 1 33 1 34 1 35 1 36 1 37 1 38 1 39 1 40 1 41 1 42 1 43 1 FLUSH TABLES t1 FOR EXPORT; backup: t1 UNLOCK TABLES; DROP TABLE t1; CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (Number of indexes don't match, table has 1 indexes but the tablespace meta-data file has 2 indexes) unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT, c3 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (Number of columns don't match, table has 6 columns but the tablespace meta-data file has 5 columns) unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 BIGINT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (Column c2 precise type mismatch.) unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Schema mismatch unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK unlink: t1.cfg SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`c1`), KEY `idx` (`c2`) ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED SELECT * FROM t1; c1 c2 1 1 2 1 3 1 4 1 6 1 7 1 8 1 9 1 13 1 14 1 15 1 16 1 17 1 18 1 19 1 20 1 28 1 29 1 30 1 31 1 32 1 33 1 34 1 35 1 36 1 37 1 38 1 39 1 40 1 41 1 42 1 43 1 DROP TABLE t1; call mtr.add_suppression("Got error -1 when reading table '.*'"); call mtr.add_suppression("InnoDB: Error: tablespace id and flags in file '.*'.*"); call mtr.add_suppression("InnoDB: The table .* doesn't have a corresponding tablespace, it was discarded");