Bug #84467 ALTERing KEY_BLOCK_SIZE keeps the old kbs in KEYs.
Submitted: 11 Jan 2017 8:22 Modified: 25 Jul 2018 6:20
Reporter: Jean-François Gagné Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.17-log OS:Any
Assigned to: CPU Architecture:Any

[11 Jan 2017 8:22] Jean-François Gagné 
Description:
Hi,

This is probably related to bug #70534.

When changing KEY_BLOCK_SIZE (from 8 to 4 and 4 to 2 in "How to repeat" below), the 1st alter pushes the old kbs in KEYs and the 2nd keeps the old kbs in KEYs.

I would expect nothing to be added to KEYs.

Thanks,

JFG

How to repeat:
> select version();
+------------+
| version()  |
+------------+
| 5.7.17-log |
+------------+
1 row in set (0.00 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 KEY_BLOCK_SIZE=4;
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=COMPRESSED KEY_BLOCK_SIZE=4
1 row in set (0.00 sec)

> alter table test KEY_BLOCK_SIZE=2;
Query OK, 0 rows affected (0.01 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=COMPRESSED KEY_BLOCK_SIZE=2
1 row in set (0.00 sec)

Suggested fix:
Do not push KEY_BLOCK_SIZE in KEYs on ALTER table.
Provide  way of fixing affected table (ideally a meta-data change only).
Add this fix to mysql_upgrade (only if meta-data change).
[11 Jan 2017 9:15] MySQL Verification Team 
Hello Jean,

Thank you for the report and test case.

Thanks,
Umesh
[14 Jul 2017 9:46] Jean-François Gagné 
Hi,

Bug #70534 was closed as "Won't fix".  As this bug is related to this "Won't fix" bug, I would like to know if this bug is still being worked on or if it will be closed too.

Many thanks,

JFG
[25 Oct 2017 15:26] MySQL Verification Team 
This is a duplicate of the bug:

https://bugs.mysql.com/bug.php?id=88220
[25 Jul 2018 6:20] Jean-François Gagné 
Hi Sinisa, I am replying late to your 25 Oct 2017 comment...

I am not sure it is a duplicate.  Bug#70534 and Bug#88220 are about removing compression, and this one is about changing the compression level.

They might all be fixed at the same time, but I am not sure they are duplicate.

Cheers, JFG
[28 Jun 2019 19:51] Daniël van Eeden 
Also affects 8.0

mysql> select version();                                                                                                                                      
+-----------+
| version() |
+-----------+
| 8.0.13    |
+-----------+
1 row in set (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.01 sec)

mysql> alter table test KEY_BLOCK_SIZE=4;
Query OK, 0 rows affected (1.89 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=COMPRESSED KEY_BLOCK_SIZE=4
1 row in set (0.00 sec)

mysql> alter table test KEY_BLOCK_SIZE=2;
Query OK, 0 rows affected (1.06 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=COMPRESSED KEY_BLOCK_SIZE=2
1 row in set (0.00 sec)
[28 Jun 2019 19:53] Daniël van Eeden 
And re-creating the index can't be done in a single operation

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=COMPRESSED KEY_BLOCK_SIZE=2
1 row in set (0.00 sec)

mysql> ALTER TABLE test DROP KEY portal_id, ADD UNIQUE KEY `portal_id` (`portal_id`);
Query OK, 0 rows affected (0.01 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=COMPRESSED KEY_BLOCK_SIZE=2
1 row in set (0.00 sec)

mysql> ALTER TABLE test DROP KEY portal_id, ADD UNIQUE KEY `portal_id` (`portal_id`) KEY_BLOCK_SIZE=0;
Query OK, 0 rows affected (0.01 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=COMPRESSED KEY_BLOCK_SIZE=2
1 row in set (0.00 sec)

mysql> ALTER TABLE test DROP KEY portal_id; ALTER TABLE test ADD UNIQUE KEY `portal_id` (`portal_id`) KEY_BLOCK_SIZE=0;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
1 row in set (0.00 sec)