Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
None
Description
There's a weird asymmetry in changing KEY_BLOCK_SIZE with ALTER TABLE. There is table-level value of KEY_BLOCK_SIZE and index-level values.
ALTER TABLE t1 KEY_BLOCK_SIZE=10 |
sets the table-level value and it's automatically copied to index-level values (assuming they weren't set before). Now
ALTER TABLE t1 KEY_BLOCK_SIZE=0 |
is supposed to revert the default value of the KEY_BLOCK_SIZE (in particular, SHOW CREATE TABLE shouldn't show KEY_BLOCK_SIZE anymore). But, again, it changes the table-level value and only those index-level values that didn't have values before. And, alas, all index-level KEY_BLOCK_SIZE have values (namely, for all indexes KEY_BLOCK_SIZE=10), so one need to drop and recreate every single index to reset index-level KEY_BLOCK_SIZE values to defaults.
So, one needs one ALTER TABLE on the table level to set KEY_BLOCK_SIZE, but needs to do it on table level and on index level for every index to remove KEY_BLOCK_SIZE settings. This is unexpected.
Also, there are engine-specific issues. MyISAM always sets index-level KEY_BLOCK_SIZE values to be the actual MYI index block sizes. InnoDB uses table-level KEY_BLOCK_SIZE value for all indexes, ignoring index-level values.
Attachments
Issue Links
- is duplicated by
-
MDEV-26400 ALTER TABLE does not remove KEY_BLOCK_SIZE for non-Compressed InnoDB tables
- Stalled
- relates to
-
MDEV-22367 Remove write support for ROW_FORMAT=COMPRESSED
- Closed
-
MDEV-23497 make ROW_FORMAT=COMPRESSED read-only by default
- Closed
-
MDEV-11757 KEY_BLOCK_SIZE strangeness when UNCOMPRESSing COMPRESSed InnoDB tables
- Closed