KEY_BLOCK_SIZE strangeness when UNCOMPRESSing COMPRESSed InnoDB tables
source link: https://jira.mariadb.org/browse/MDEV-11757
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
KEY_BLOCK_SIZE strangeness when UNCOMPRESSing COMPRESSed InnoDB tables
Details
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.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK