1set global innodb_compression_algorithm = 1; 2create table innodb_normal(c1 bigint not null, b char(200)) engine=innodb; 3create table innodb_compact(c1 bigint not null, b char(200)) engine=innodb row_format=compact page_compressed=1; 4create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_format=dynamic page_compressed=1; 5create table innodb_compressed(c1 bigint not null, b char(200)) engine=innodb row_format=compressed page_compressed=1; 6ERROR HY000: Can't create table `test`.`innodb_compressed` (errno: 140 "Wrong create options") 7show warnings; 8Level Code Message 9Warning 140 InnoDB: PAGE_COMPRESSED table can't have ROW_TYPE=COMPRESSED 10Error 1005 Can't create table `test`.`innodb_compressed` (errno: 140 "Wrong create options") 11Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB 12show create table innodb_compact; 13Table Create Table 14innodb_compact CREATE TABLE `innodb_compact` ( 15 `c1` bigint(20) NOT NULL, 16 `b` char(200) DEFAULT NULL 17) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT `page_compressed`=1 18show create table innodb_dynamic; 19Table Create Table 20innodb_dynamic CREATE TABLE `innodb_dynamic` ( 21 `c1` bigint(20) NOT NULL, 22 `b` char(200) DEFAULT NULL 23) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC `page_compressed`=1 24create table innodb_redundant(c1 bigint not null, b char(200)) engine=innodb row_format=redundant page_compressed=1; 25ERROR HY000: Can't create table `test`.`innodb_redundant` (errno: 140 "Wrong create options") 26show warnings; 27Level Code Message 28Warning 140 InnoDB: PAGE_COMPRESSED table can't have ROW_TYPE=REDUNDANT 29Error 1005 Can't create table `test`.`innodb_redundant` (errno: 140 "Wrong create options") 30Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB 31create table innodb_redundant(c1 bigint not null, b char(200)) engine=innodb row_format=redundant; 32show create table innodb_redundant; 33Table Create Table 34innodb_redundant CREATE TABLE `innodb_redundant` ( 35 `c1` bigint(20) NOT NULL, 36 `b` char(200) DEFAULT NULL 37) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT 38alter table innodb_redundant page_compressed=1; 39ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'PAGE_COMPRESSED' 40show warnings; 41Level Code Message 42Warning 140 InnoDB: PAGE_COMPRESSED table can't have ROW_TYPE=REDUNDANT 43Error 1478 Table storage engine 'InnoDB' does not support the create option 'PAGE_COMPRESSED' 44show create table innodb_redundant; 45Table Create Table 46innodb_redundant CREATE TABLE `innodb_redundant` ( 47 `c1` bigint(20) NOT NULL, 48 `b` char(200) DEFAULT NULL 49) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT 50alter table innodb_redundant row_format=compact page_compressed=1; 51show create table innodb_redundant; 52Table Create Table 53innodb_redundant CREATE TABLE `innodb_redundant` ( 54 `c1` bigint(20) NOT NULL, 55 `b` char(200) DEFAULT NULL 56) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT `page_compressed`=1 57drop table innodb_redundant; 58# 59# MDEV-24455 Assertion `!m_freed_space' failed in mtr_t::start 60# 61CREATE TABLE t1 (a TEXT, b TEXT) ENGINE=InnoDB PAGE_COMPRESSED='ON'; 62BEGIN; 63INSERT INTO t1 VALUES(REPEAT('x',81),REPEAT('x',8034)); 64ROLLBACK; 65DROP TABLE t1; 66create procedure innodb_insert_proc (repeat_count int) 67begin 68declare current_num int; 69set current_num = 0; 70while current_num < repeat_count do 71insert into innodb_normal values(current_num, substring(MD5(RAND()), -64)); 72set current_num = current_num + 1; 73end while; 74end// 75commit; 76set autocommit=0; 77call innodb_insert_proc(5000); 78commit; 79set autocommit=1; 80insert into innodb_compact select * from innodb_normal; 81insert into innodb_dynamic select * from innodb_normal; 82update innodb_compact set c1 = c1 + 1; 83update innodb_dynamic set c1 = c1 + 1; 84select count(*) from innodb_compact where c1 < 1500000; 85count(*) 865000 87select count(*) from innodb_dynamic where c1 < 1500000; 88count(*) 895000 90# restart 91update innodb_compact set c1 = c1 + 1; 92update innodb_dynamic set c1 = c1 + 1; 93select count(*) from innodb_compact where c1 < 1500000; 94count(*) 955000 96select count(*) from innodb_dynamic where c1 < 1500000; 97count(*) 985000 99set global innodb_compression_algorithm = 0; 100alter table innodb_compact page_compressed=DEFAULT, algorithm=instant; 101ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Changing table options requires the table to be rebuilt. Try ALGORITHM=INPLACE 102alter table innodb_compact page_compressed=DEFAULT; 103alter table innodb_dynamic page_compressed=DEFAULT, algorithm=instant; 104ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Changing table options requires the table to be rebuilt. Try ALGORITHM=INPLACE 105alter table innodb_dynamic page_compressed=DEFAULT; 106show create table innodb_compact; 107Table Create Table 108innodb_compact CREATE TABLE `innodb_compact` ( 109 `c1` bigint(20) NOT NULL, 110 `b` char(200) DEFAULT NULL 111) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT 112show create table innodb_dynamic; 113Table Create Table 114innodb_dynamic CREATE TABLE `innodb_dynamic` ( 115 `c1` bigint(20) NOT NULL, 116 `b` char(200) DEFAULT NULL 117) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC 118update innodb_compact set c1 = c1 + 1; 119update innodb_dynamic set c1 = c1 + 1; 120select count(*) from innodb_compact where c1 < 1500000; 121count(*) 1225000 123select count(*) from innodb_dynamic where c1 < 1500000; 124count(*) 1255000 126drop procedure innodb_insert_proc; 127drop table innodb_normal; 128drop table innodb_compact; 129drop table innodb_dynamic; 130CREATE TABLE no_compression (id INT NOT NULL, name VARCHAR(200)) ENGINE=InnoDB; 131SET SESSION innodb_compression_default = 1; 132CREATE TABLE default_compression (id INT NOT NULL, name VARCHAR(200)) ENGINE=InnoDB; 133CREATE TABLE explicit_no_compression (id INT NOT NULL, name VARCHAR(200)) ENGINE=InnoDB PAGE_COMPRESSED=0; 134SHOW CREATE TABLE no_compression; 135Table Create Table 136no_compression CREATE TABLE `no_compression` ( 137 `id` int(11) NOT NULL, 138 `name` varchar(200) DEFAULT NULL 139) ENGINE=InnoDB DEFAULT CHARSET=latin1 140SHOW CREATE TABLE default_compression; 141Table Create Table 142default_compression CREATE TABLE `default_compression` ( 143 `id` int(11) NOT NULL, 144 `name` varchar(200) DEFAULT NULL 145) ENGINE=InnoDB DEFAULT CHARSET=latin1 `PAGE_COMPRESSED`='ON' 146SHOW CREATE TABLE explicit_no_compression; 147Table Create Table 148explicit_no_compression CREATE TABLE `explicit_no_compression` ( 149 `id` int(11) NOT NULL, 150 `name` varchar(200) DEFAULT NULL 151) ENGINE=InnoDB DEFAULT CHARSET=latin1 `PAGE_COMPRESSED`=0 152DROP TABLE no_compression; 153DROP TABLE default_compression; 154DROP TABLE explicit_no_compression; 155SET SESSION innodb_compression_default = 0; 156