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
Activity
InnoDB is only looking at the table-wide KEY_BLOCK_SIZE attribute. Each index of the table will use the same ROW_FORMAT=COMPRESSED page size. MariaDB could support per-index KEY_BLOCK_SIZE for InnoDB, if there is demand for it, but I do not think it can happen before 10.3. Inside InnoDB, it would likely require having one file for each distinct KEY_BLOCK_SIZE, or for each index, so it would not be a simple change.
SHOW TABLE STATUS or SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES should reflect the actual ROW_FORMAT and page size that are used in InnoDB. The .frm file can contain conflicting information.
So, I've fixed ALTER TABLE to rebuild the index if KEY_BLOCK_SIZE changes.
The rest of the issue is moved to MDEV-12152, it cannot be fixed in 10.0
The way this is "fixed" is unclear to me. Which ALTER TABLE will rebuild the index ?
Does that mean that ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; does not rebuild indexes for InnoDB in "old" versions (so does not fully uncompress the table ? If this is the case, this should be considered as an InnoDB bug (and what is the "clean" way of uncompressing an InnoDB table) ?
Wouldn't ALTER TABLE t1 ROW_FORMAT=DYNAMIC; be the right way of uncompressing an InnoDB table ? In this case, it should not produce a warning and it should not "push" the KEY_BLOCK_SIZE in KEYs (I am not sure if it does, I did not test).
Note: in MDEV-12152, I suggested that ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; should fail for InnoDB tables.
This is "fixed" in a way that
alter table t1 drop primary key, add primary key (id1) key_block_size=0, |
drop key id2, add unique (id2) key_block_size=0; |
would not be treated as a no-op, the server would actually notice that the old and new index definitions are different. That was clearly a bug and it's fixed in 10.0.
The rest of this issue is more like a change in behavior, not a bug fix, so we cannot do it in 10.0. It's now in MDEV-12152.
I am ok with a fix in 10.1.