| Bug #88220 | compressing and uncompressing InnoDB tables seems to be inconsistent | ||
|---|---|---|---|
| Submitted: | 25 Oct 2017 8:28 | Modified: | 16 Jan 2018 5:45 |
| Reporter: | Simon Mudd (OCA) | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: Storage Engines | Severity: | S3 (Non-critical) |
| Version: | 5.7.18 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | compression, innodb, key_block_size | ||
[25 Oct 2017 9:38]
Simon Mudd
Possibly a duplicate of bug#70534 and bug#84467 but they haven't had much attention as far as I can see. My report just shows the current behaviour is inconsistent and needs fixing. the other bugs basically same the same thing.
[25 Oct 2017 15:21]
MySQL Verification Team
Hi!
Thank you for your bug report. I have managed to repeat the behaviour that you reported:
----------------------------------------------
*************************** 1. row ***************************
Table: tblock
Create Table: CREATE TABLE `tblock` (
`id` int(11) DEFAULT NULL,
`id2` int(11) NOT NULL,
PRIMARY KEY (`id2`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
*************************** 1. row ***************************
Table: tblock
Create Table: CREATE TABLE `tblock` (
`id` int(11) DEFAULT NULL,
`id2` int(11) NOT NULL,
PRIMARY KEY (`id2`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=8
*************************** 1. row ***************************
Table: tblock
Create Table: CREATE TABLE `tblock` (
`id` int(11) DEFAULT NULL,
`id2` int(11) NOT NULL,
PRIMARY KEY (`id2`) KEY_BLOCK_SIZE=8,
KEY `id` (`id`) KEY_BLOCK_SIZE=8
) ENGINE=InnoDB DEFAULT CHARSET=latin1
--------------------------------------------------------------------
I will update other bugs as duplicates of this one.
[16 Jan 2018 5:45]
Nisha Padmini Gopalakrishnan
Posted by developer: InnoDB supports only table level key_block_size as documented here: https://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-options If a table with indexes is created without specifying key_block_size is later altered to key_block_size=8. Then the the index trees are recreated to have page size 8k and data is compressed. When the table key_block_size is then altered to 0, the indexes are recreated with default page size without compressing the data in pages as documented here: https://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-options So this issue is related to only the display of the key_block_size for indexes. Following is the analysis: - CREATE TABLE has option of specifying KEY_BLOCK_SIZE on both table level and index level. While only table level key_block_size is used for InnoDB tables in compression, index level key_block_size is used with MyISAM tables for specifying page size of indexes. - Even if the table key_block_size is set to zero for the InnoDB table, (after ALTER TABLE), the index level key_block_size is used if the table is altered to MyIsam. - Any specific table option meant for a specific SE used in CREATE TABLE statement is stored. SHOW CREATE TABLE statement shows that options even when another SE is in use. This has been a legacy behavior. For example, Connection option is used for Federated engine but when this option is used in CREATE TABLE statement, it shows in SHOW CREATE TABLE statement. Changing this behavior would cause backward compatibility issues. Hence closing it as not feasible to fix.
Description: When compressing an Innodb table you just specify the key_block_size. When uncompressing the table it seems you have to also uncompress the "indexes" too. This seems inconsistent. The example below describes the problem and what looks odd. How to repeat: Run on 5.7.18 but probably more general than this. Not tried on 8.0, but I'd expect the same behaviour. root@myhost [test]> create table t ( id int, id2 int primary key, key (id) ) engine = innodb; -- create the table with PK and another index Query OK, 0 rows affected (0.01 sec) root@myhost [test]> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) DEFAULT NULL, `id2` int(11) NOT NULL, PRIMARY KEY (`id2`), KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) root@myhost [test]> alter table t key_block_size = 8; -- compress table Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 root@myhost [test]> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) DEFAULT NULL, `id2` int(11) NOT NULL, PRIMARY KEY (`id2`), KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 KEY_BLOCK_SIZE=8 -- only the table attributes here show key_block_size = 8 1 row in set (0.00 sec) root@myhost [test]> alter table t key_block_size = 0; -- uncompress table Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 root@myhost [test]> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) DEFAULT NULL, `id2` int(11) NOT NULL, PRIMARY KEY (`id2`) KEY_BLOCK_SIZE=8, -- now we have the primary key with a 8k block size and the other key too, yet the table is not compressed? KEY `id` (`id`) KEY_BLOCK_SIZE=8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) Suggested fix: * Make the behaviour consistent when compressing and uncompressing the table. Not doing this requires the DBA to check which indexes exist and uncompress them all explicitly. This is tedious and error prone and not consistent with the case when you compress the table * If you want to have a compressed index then doing this explicitly seems sensible so make it clear in the documentation that you can do this separately if needed.