Bug #70534 Removing table compression leaves compressed keys
Submitted: 5 Oct 2013 19:23 Modified: 19 Jul 2017 12:35
Reporter: monty solomon Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.5.34, 5.6.23, 5.7.17 OS:Linux
Assigned to: CPU Architecture:Any

[5 Oct 2013 19:23] monty solomon
Description:
Removing the compression from a table leaves KEY_BLOCK_SIZE=8 on each of the keys.

How to repeat:
mysql> CREATE TABLE `test` (
    ->   `id` bigint(20) NOT NULL,
    ->   `portal_id` bigint(20) NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `portal_id` (`portal_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` bigint(20) NOT NULL,
  `portal_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `portal_id` (`portal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
1 row in set (0.00 sec)

mysql> alter table test ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` bigint(20) NOT NULL,
  `portal_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`) KEY_BLOCK_SIZE=8,
  UNIQUE KEY `portal_id` (`portal_id`) KEY_BLOCK_SIZE=8
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

Suggested fix:
Remove KEY_BLOCK_SIZE from keys when compression is removed from the table.
[6 Oct 2013 17:55] Valeriy Kravchuk
MySQL 5.6.14 is also affected:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3314 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.6.14-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `test` (
    ->   `id` bigint(20) NOT NULL,
    ->   `portal_id` bigint(20) NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `portal_id` (`portal_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE
=8;
Query OK, 0 rows affected (1.75 sec)

mysql> alter table test ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
Query OK, 0 rows affected (1.88 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` bigint(20) NOT NULL,
  `portal_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`) KEY_BLOCK_SIZE=8,
  UNIQUE KEY `portal_id` (`portal_id`) KEY_BLOCK_SIZE=8
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
1 row in set (0.01 sec)
[7 Oct 2013 8:31] Umesh Shastry
Hello Monty,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh
[10 Oct 2014 7:47] Daniël van Eeden
According to http://dev.mysql.com/doc/refman/5.6/en/create-table.html KEY_BLOCK_SIZE=0 will result in a key block size of innodb_page_size/2.

But even when I try KEY_BLOCK_SIZE=16 the compressed keys remains.
[10 Apr 2015 22:05] monty solomon
What are the plans to fix this bug?
[10 Apr 2015 22:06] monty solomon
Added 5.6.23 to the affected version.
[7 Jan 2017 3:15] Jean-François Gagné
Hi,

This can also be reproduced on 5.7.17:

> select version();
+------------+
| version()  |
+------------+
| 5.7.17-log |
+------------+
1 row in set (0.00 sec)

> CREATE TABLE `test` (
    ->   `id` bigint(20) NOT NULL,
    ->   `portal_id` bigint(20) NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `portal_id` (`portal_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.01 sec)

> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` bigint(20) NOT NULL,
  `portal_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `portal_id` (`portal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
1 row in set (0.00 sec)

> alter table test ROW_FORMAT=DYNAMIC;
ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'KEY_BLOCK_SIZE'

> alter table test ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` bigint(20) NOT NULL,
  `portal_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`) KEY_BLOCK_SIZE=8,
  UNIQUE KEY `portal_id` (`portal_id`) KEY_BLOCK_SIZE=8
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

Thanks,

JFG
[11 Jan 2017 8:23] Jean-François Gagné
Probably related: Bug #84467.
[24 May 2017 9:36] Erlend Dahl
Posted by developer:

[6 Jan 2016 1:22] Abhishek Ar Ranjan

This is a legacy issue of mysql.

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. 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.

This behaviour is not to change for now to support backward compatibility.
[24 May 2017 10:10] Jean-François Gagné
This has been closed at "Won't fix".

However, unless I am missing something, there is no way to remove table compression without leaving "compressed keys".

I could understand that "removing table compression" the wrong way would not be fixed, but I think a way to remove table compression without leaving "compressed keys" should be provided.
[19 Jul 2017 12:35] Sinisa Milivojevic
After fruitful discussion, we agreed to set this bug as "Verified".
[25 Oct 2017 15:25] Sinisa Milivojevic
This is a duplicate of the bug :

https://bugs.mysql.com/bug.php?id=88220
[25 Oct 2017 19:17] Daniël van Eeden
I thing Bug #88220 is a duplicate of this bug, not the other way around.