[MDEV-12152] KEY_BLOCK_SIZE strangeness in ALTER TABLE Created: 2017-02-28  Updated: 2021-08-18

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 5.5, 10.0, 10.1, 10.2
Fix Version/s: 10.4

Type: Bug Priority: Minor
Reporter: Sergei Golubchik Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 2
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-26400 ALTER TABLE does not remove KEY_BLOCK... Stalled
Relates
relates to MDEV-22367 Remove write support for ROW_FORMAT=C... Closed
relates to MDEV-23497 make ROW_FORMAT=COMPRESSED read-only ... Closed
relates to MDEV-11757 KEY_BLOCK_SIZE strangeness when UNCOM... Closed

 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.



 Comments   
Comment by Jean-François Gagné [ 2017-03-01 ]

I understand this mostly applies to ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; of MDEV-11757.

However, MDEV-11757 also mentioned below. Shoudn't above just "fail" with InnoDB and below be the right way to uncompress a table (should not produce a warning though) ?

mysql> ALTER TABLE t1 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 1 warning (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 1

Comment by Sergei Golubchik [ 2017-03-01 ]

I tend to agree with that. There's IGNORE_BAD_TABLE_OPTIONS sql_mode, but it's off by default, so by default one should not end up with a table that has ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=8.

Generated at Thu Feb 08 07:55:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.