Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.18, 10.0.28, 10.2(EOL)
-
10.0.30
Description
Summary: If you uncompress a compressed InnoDB table, that you originally specified KEY_BLOCK_SIZE, you cannot eliminate KEY_BLOCK_SIZE from the table's structure. (It may indeed be ignored, but you cannot remove it, unles you re-create the table.)
Say you create the following compressed table:
CREATE TABLE `t1` (
|
`id1` bigint(20) NOT NULL,
|
`id2` bigint(20) NOT NULL,
|
PRIMARY KEY (`id1`),
|
UNIQUE KEY `id2` (`id2`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
|
ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
|
This throws a warning:
mysql> ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
|
Query OK, 0 rows affected, 1 warning (0.21 sec)
|
Records: 0 Duplicates: 0 Warnings: 1
|
mysql> SHOW WARNINGS;
|
+---------+------+-----------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+-----------------------------------------------------------------+
|
| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=8 unless ROW_FORMAT=COMPRESSED. |
|
+---------+------+-----------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
So now try the following instead to eliminate the warning (start with fresh `t1`):
ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
|
It runs fine, with no warnings:
mysql> ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0;
|
Query OK, 0 rows affected (0.11 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
But, now look at the SHOW CREATE TABLE output:
mysql> SHOW CREATE TABLE `t1`\G
|
*************************** 1. row ***************************
|
Table: t1
|
Create Table: CREATE TABLE `t1` (
|
`id1` bigint(20) NOT NULL,
|
`id2` bigint(20) NOT NULL,
|
PRIMARY KEY (`id1`) KEY_BLOCK_SIZE=8,
|
UNIQUE KEY `id2` (`id2`) KEY_BLOCK_SIZE=8
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
|
1 row in set (0.00 sec)
|
This removed KEY_BLOCK_SIZE from the end of the table structure, but instead it now added KEY_BLOCK_SIZE=8 to both KEY definitions.
So no matter what you do when uncompressing the table, it seems to retain the original KEY_BLOCK_SIZE somewhere, and I cannot seem to eliminate it.
Attachments
Issue Links
- relates to
-
-
- Open
-
-
-
- Stalled
-
- links to