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: | |
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
[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