1call mtr.add_suppression("InnoDB: Table .* tablespace is set as discarded"); 2SET GLOBAL innodb_compression_algorithm = 1; 3create table t1(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb encrypted=yes encryption_key_id=4; 4create table t2(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb encrypted=yes encryption_key_id=1; 5create table t3(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb page_compressed=yes; 6create table t4(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb page_compressed=yes encrypted=yes encryption_key_id=4; 7create table t5(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb; 8insert into t1 values (NULL, 'verysecretmessage'); 9insert into t1(b) select b from t1; 10insert into t1(b) select b from t1; 11insert into t1(b) select b from t1; 12insert into t1(b) select b from t1; 13insert into t1(b) select b from t1; 14insert into t1(b) select b from t1; 15insert into t1(b) select b from t1; 16insert into t1(b) select b from t1; 17insert into t2 select * from t1; 18insert into t3 select * from t1; 19insert into t4 select * from t1; 20insert into t5 select * from t1; 21FLUSH TABLE t1,t2,t3,t4,t5 FOR EXPORT; 22backup: t1 23backup: t2 24backup: t3 25backup: t4 26backup: t5 27db.opt 28t1.cfg 29t1.frm 30t1.ibd 31t2.cfg 32t2.frm 33t2.ibd 34t3.cfg 35t3.frm 36t3.ibd 37t4.cfg 38t4.frm 39t4.ibd 40t5.cfg 41t5.frm 42t5.ibd 43UNLOCK TABLES; 44ALTER TABLE t1 DISCARD TABLESPACE; 45ALTER TABLE t2 DISCARD TABLESPACE; 46ALTER TABLE t3 DISCARD TABLESPACE; 47ALTER TABLE t4 DISCARD TABLESPACE; 48ALTER TABLE t5 DISCARD TABLESPACE; 49DROP TABLE t1; 50DROP TABLE t3; 51DROP TABLE t4; 52DROP TABLE t5; 53create table t6(a int) engine=innodb; 54create table t5(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb; 55create table t3(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb page_compressed=yes; 56create table t1(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb encrypted=yes encryption_key_id=4; 57create table t4(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb page_compressed=yes encrypted=yes encryption_key_id=4; 58ALTER TABLE t1 DISCARD TABLESPACE; 59ALTER TABLE t3 DISCARD TABLESPACE; 60ALTER TABLE t4 DISCARD TABLESPACE; 61ALTER TABLE t5 DISCARD TABLESPACE; 62restore: t1 .ibd and .cfg files 63restore: t2 .ibd and .cfg files 64restore: t3 .ibd and .cfg files 65restore: t4 .ibd and .cfg files 66restore: t5 .ibd and .cfg files 67ALTER TABLE t1 IMPORT TABLESPACE; 68SHOW CREATE TABLE t1; 69Table Create Table 70t1 CREATE TABLE `t1` ( 71 `c1` bigint(20) NOT NULL AUTO_INCREMENT, 72 `b` char(200) DEFAULT NULL, 73 PRIMARY KEY (`c1`) 74) ENGINE=InnoDB AUTO_INCREMENT=504 DEFAULT CHARSET=latin1 `encrypted`=yes `encryption_key_id`=4 75SELECT COUNT(*) FROM t1; 76COUNT(*) 77256 78ALTER TABLE t2 IMPORT TABLESPACE; 79SELECT COUNT(*) FROM t2; 80COUNT(*) 81256 82ALTER TABLE t3 IMPORT TABLESPACE; 83SELECT COUNT(*) FROM t3; 84COUNT(*) 85256 86ALTER TABLE t4 IMPORT TABLESPACE; 87SELECT COUNT(*) FROM t4; 88COUNT(*) 89256 90ALTER TABLE t5 IMPORT TABLESPACE; 91SELECT COUNT(*) FROM t5; 92COUNT(*) 93256 94# t1 encrypted expecting NOT FOUND 95NOT FOUND /verysecretmessage/ in t1.ibd 96# t2 encrypted expecting NOT FOUND 97NOT FOUND /verysecretmessage/ in t2.ibd 98# t3 page compressed expecting NOT FOUND 99NOT FOUND /verysecretmessage/ in t3.ibd 100# t4 page compressed and encrypted expecting NOT FOUND 101NOT FOUND /verysecretmessage/ in t4.ibd 102# t5 normal expecting FOUND 103FOUND 289 /verysecretmessage/ in t5.ibd 104DROP TABLE t1,t2,t3,t4,t5,t6; 105