| 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: | |
| 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 | |
[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]
MySQL Verification Team
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]
MySQL Verification Team
After fruitful discussion, we agreed to set this bug as "Verified".
[25 Oct 2017 15:25]
MySQL Verification Team
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.
[28 Jun 2019 19:47]
Daniël van Eeden
Looks like this is fixed in 8.0
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.13 |
+-----------+
1 row in set (0.00 sec)
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 warning (0.53 sec)
mysql> alter table test ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
Query OK, 0 rows affected (1.45 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`),
UNIQUE KEY `portal_id` (`portal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
[28 Jun 2019 19:56]
Daniël van Eeden
Some other legacy features and/or things for other SE's are seem to be handled slightly differently.
mysql> ALTER TABLE test PASSWORD 'secret';
Query OK, 0 rows affected (0.03 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)
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.