Bug #88220 compressing and uncompressing InnoDB tables seems to be inconsistent
Submitted: 25 Oct 2017 8:28 Modified: 16 Jan 5:45
Reporter: Simon Mudd (OCA) Email Updates:
Status: Won't fix Impact on me:
None 
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 8:28] Simon Mudd
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.
[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] Sinisa Milivojevic
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 5:45] Nisha 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.