[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: |
|
||||||||||||||||||||||||
| 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.
sets the table-level value and it's automatically copied to index-level values (assuming they weren't set before). Now
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 However,
| |||
| 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. |