ALTER TABLE does not remove KEY_BLOCK_SIZE for non-Compressed InnoDB tables

[MDEV-26400] ALTER TABLE does not remove KEY_BLOCK_SIZE for non-Compressed InnoDB tables

CREATE TABLE innodb_test (id int PRIMARY KEY) KEY_BLOCK_SIZE=4;

MariaDB [test]> ALTER TABLE innodb_test KEY_BLOCK_SIZE=DEFAULT, ROW_FORMAT=DYNAMIC;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DEFAULT, ROW_FORMAT=DYNAMIC' at line 1

MariaDB [test]> ALTER TABLE innodb_test KEY_BLOCK_SIZE=0, ROW_FORMAT=DYNAMIC;

Query OK, 0 rows affected (0.027 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> SHOW CREATE TABLE innodb_test\G

*************************** 1. row ***************************

       Table: innodb_test

Create Table: CREATE TABLE `innodb_test` (

  `id` int(11) NOT NULL,

  PRIMARY KEY (`id`) KEY_BLOCK_SIZE=4

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

1 row in set (0.000 sec)

This is an interesting bug, and I do not know if there is a satisfactory way to fix it.

When I developed the original form of InnoDB compression, which was initially released in a product that was distributed independently from MySQL ("InnoDB Plugin for MySQL 5.1"), back when Oracle owned Innobase Oy but not MySQL, we were unable to change any syntax. We had to repurpose an existing attribute. Nowadays, MariaDB Server would provide a HA_xOPTION interface that would allow a storage engine to define attributes on columns, indexes, or tables. But that did not exist in MySQL back then (and still does not).

I noticed that the attribute KEY_BLOCK_SIZE had been recently introduced for some other purpose. The syntax ROW_FORMAT=COMPRESSED already existed; if I remember correctly, MyISAM supported compressed read-only tables from an early point on. We needed something that would allow the compressed page size to be specified independently of the InnoDB page size (which before the introduction of the global parameter innodb_page_size was fixed at 16384 bytes).

Unlike the much later page_compressed format which resembles Stacker or DoubleSpace by compressing fixed-size input blocks to variable-size output blocks, the InnoDB ROW_FORMAT=COMPRESSED format requires one to specify the target compressed page size upfront and essentially treats the input as variable-size blocks. If the compressed page fills up, a page split on the uncompressed page will be forced.

The default value of the compressed page size is half the uncompressed page size. Someone thought that because the SQL parser does not support suffixes like KEY_BLOCK_SIZE=4K or KEY_BLOCK_SIZE=4KiB (MDEV-25652), it would be awkward to require the user to type larger powers of two, like KEY_BLOCK_SIZE=4096. So, the unit was chosen to be 1024 bytes instead of bytes.

Because a storage engine cannot rewrite the table definition that is stored in the .frm file, we ended up with this awkward situation. Specifying KEY_BLOCK_SIZE alone without any ROW_FORMAT will imply ROW_FORMAT=COMPRESSED, but SHOW CREATE TABLE will not indicate that. Specifying KEY_BLOCK_SIZE with any other ROW_FORMAT than COMPRESSED or DEFAULT (which I believe is the same as specifying no ROW_FORMAT) will result in an error.

Based on the description, I assume that KEY_BLOCK_SIZE was originally conceived as an index attribute, and that the table-wide attribute would specify the default for all indexes, similar to how columns may default to character set or collation attributes specified at the table level. But, InnoDB always treated it as a table attribute. I do not know if any other storage engine would interpret KEY_BLOCK_SIZE, or allow different indexes to use a different page size. ENGINE=Aria or ENGINE=MyISAM would be my best guesses for that.

The only easy improvements that I might foresee are outside InnoDB:

1. We could interpret KEY_BLOCK_SIZE=DEFAULT as a request to remove the attribute. Currently, I think that this can only be achieved with KEY_BLOCK_SIZE=0.
2. If all indexes use the same KEY_BLOCK_SIZE as the table-level attribute, then changing (or removing) the table-level KEY_BLOCK_SIZE in ALTER TABLE must remove the index-specific attributes.

With these suggestions, I think that the following should remove all traces of ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE:

CREATE TABLE innodb_test (id int PRIMARY KEY) KEY_BLOCK_SIZE=4;

ALTER TABLE innodb_test KEY_BLOCK_SIZE=DEFAULT, ROW_FORMAT=DYNAMIC;