Bug #90019 table compression on table created by like with foreign key did not work
Submitted: 12 Mar 2018 10:42 Modified: 13 Mar 2018 10:46
Reporter: z z Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.6.25 OS:Any
Assigned to: CPU Architecture:Any

[12 Mar 2018 10:42] z z
Description:
i want to compress a table with foreign key ,but the table did't compressed and online documentation does not describe this situation.

How to repeat:
CREATE TABLE `history` (
  `h_c_id` int(11) DEFAULT NULL,
  `h_c_d_id` tinyint(4) DEFAULT NULL,
  `h_c_w_id` smallint(6) DEFAULT NULL,
  `h_d_id` tinyint(4) DEFAULT NULL,
  `h_w_id` smallint(6) DEFAULT NULL,
  `h_date` datetime DEFAULT NULL,
  `h_amount` decimal(6,2) DEFAULT NULL,
  `h_data` varchar(24) DEFAULT NULL,
  KEY `fkey_history_1` (`h_c_w_id`,`h_c_d_id`,`h_c_id`),
  KEY `fkey_history_2` (`h_w_id`,`h_d_id`),
  CONSTRAINT `fkey_history_1` FOREIGN KEY (`h_c_w_id`, `h_c_d_id`, `h_c_id`) REFERENCES `customer` (`c_w_id`, `c_d_id`, `c_id`),
  CONSTRAINT `fkey_history_2` FOREIGN KEY (`h_w_id`, `h_d_id`) REFERENCES `district` (`d_w_id`, `d_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

create table history_compress_4 like history;
alter table history_compress_4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
insert into history_compress_4 select * from history;

ll|grep history

-rw-rw---- 1 my8888 mysql        8814 Mar 12 18:11 history_compress_4.frm
-rw-rw---- 1 my8888 mysql  3137339392 Mar 12 18:15 history_compress_4.ibd
-rw-rw---- 1 my8888 mysql        8814 Jan 21 14:19 history.frm
-rw-rw---- 1 my8888 mysql  3137339392 Jan 21 17:28 history.ibd
[12 Mar 2018 13:57] MySQL Verification Team
Hi!

Thank you for your bug report. However, this is not a bug ......

I have repeated the exact commands that you have run and created the compressed table `t1` from the table `salary'.

Even with such a small table a difference is quite clear:

-rw-r-----   1 sinisa  staff      8622 Jul 21  2017 salary.frm
-rw-r-----   1 sinisa  staff    163840 Jul 21  2017 salary.ibd
-rw-r-----   1 sinisa  staff      8622 Mar 12 15:50 t1.frm
-rw-r-----   1 sinisa  staff     81920 Mar 12 15:51 t1.ibd

You have either specified a wrong key block size, or much more likely, this is something that is already fixed in 5.7.

Not a bug.
[13 Mar 2018 10:46] z z
i'm so sorry ,the before test has some thing wrong, i forgot add constraints by manual,but even i added constriaints on table history_compress_4 ,the table was not compressed  in 5.6 ,indeed in 5.7 it has been fixed, here is my new test.
 in 5.6:
create table history_compress_4 like history;
ALTER TABLE history_compress_4 ADD CONSTRAINT fkey_history_cmp_1 FOREIGN KEY (`h_c_w_id`, `h_c_d_id`, `h_c_id`) REFERENCES `customer` (`c_w_id`, `c_d_id`, `c_id`);`);
ALTER TABLE history_compress_4 ADD CONSTRAINT fkey_history_cmp_2 FOREIGN KEY (`h_w_id`, `h_d_id`) REFERENCES `district` (`d_w_id`, `d_id`);

alter table history_compress_4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
insert into history_compress_4 select * from history;
 CREATE TABLE `history_compress_4` (
  `h_c_id` int(11) DEFAULT NULL,
  `h_c_d_id` tinyint(4) DEFAULT NULL,
  `h_c_w_id` smallint(6) DEFAULT NULL,
  `h_d_id` tinyint(4) DEFAULT NULL,
  `h_w_id` smallint(6) DEFAULT NULL,
  `h_date` datetime DEFAULT NULL,
  `h_amount` decimal(6,2) DEFAULT NULL,
  `h_data` varchar(24) DEFAULT NULL,
  KEY `fkey_history_cmp_1` (`h_c_w_id`,`h_c_d_id`,`h_c_id`),
  KEY `fkey_history_cmp_2` (`h_w_id`,`h_d_id`),
  CONSTRAINT `fkey_history_cmp_1` FOREIGN KEY (`h_c_w_id`, `h_c_d_id`, `h_c_id`) REFERENCES `customer` (`c_w_id`, `c_d_id`, `c_id`),
  CONSTRAINT `fkey_history_cmp_2` FOREIGN KEY (`h_w_id`, `h_d_id`) REFERENCES `district` (`d_w_id`, `d_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4

the tables size are still the same:
-rw-rw---- 1 my8888 mysql        8814 Mar 13 18:05 history_compress_4.frm
-rw-rw---- 1 my8888 mysql  3137339392 Mar 13 18:10 history_compress_4.ibd
-rw-rw---- 1 my8888 mysql        8814 Jan 21 14:19 history.frm
-rw-rw---- 1 my8888 mysql  3137339392 Jan 21 17:28 history.ibd

in 5.7

| CREATE TABLE `history_compress_1_on57` (
  `h_c_id` int(11) DEFAULT NULL,
  `h_c_d_id` tinyint(4) DEFAULT NULL,
  `h_c_w_id` smallint(6) DEFAULT NULL,
  `h_d_id` tinyint(4) DEFAULT NULL,
  `h_w_id` smallint(6) DEFAULT NULL,
  `h_date` datetime DEFAULT NULL,
  `h_amount` decimal(6,2) DEFAULT NULL,
  `h_data` varchar(24) DEFAULT NULL,
  KEY `fkey_history_1` (`h_c_w_id`,`h_c_d_id`,`h_c_id`),
  KEY `fkey_history_2` (`h_w_id`,`h_d_id`),
  CONSTRAINT `fkey_history_cmp_1` FOREIGN KEY (`h_c_w_id`, `h_c_d_id`, `h_c_id`) REFERENCES `customer` (`c_w_id`, `c_d_id`, `c_id`),
  CONSTRAINT `fkey_history_cmp_2` FOREIGN KEY (`h_w_id`, `h_d_id`) REFERENCES `district` (`d_w_id`, `d_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 |

the tables  size is different,it works:

-rw-r----- 1 my3334 mysql        8814 Mar 13 18:10 history_compress_1_on57.frm
-rw-r----- 1 my3334 mysql  1522532352 Mar 13 18:42 history_compress_1_on57.ibd
-rw-r----- 1 my3334 mysql        8814 Mar 13 14:04 history.frm
-rw-r----- 1 my3334 mysql  3137339392 Mar 13 14:19 history.ibd
[13 Mar 2018 10:59] MySQL Verification Team
Hi,

> the tables  size is different,it works:

So as Sinisa already stated, this is fixed in 5.7

take care
Bogdan