Bug #88220 compressing and uncompressing InnoDB tables seems to be inconsistent
Submitted: 25 Oct 8:28 Modified: 25 Oct 15:21
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Storage Engines Severity:S3 (Non-critical)
Version:5.7.18 OS:Any
Assigned to:
Tags: compression, innodb, key_block_size

[25 Oct 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 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 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.