Reproducible on MySQL 5.7.

This is a known issue, ‘submarine attributes’. A similar thing happens with ALTER TABLE…ENGINE when some table attributes that are stored in the .frm file are ignored by certain storage engines. An example could be

CREATE TABLE t (a INT) ENGINE=MyISAM ROW_FORMAT=FIXED;

ALTER TABLE t ENGINE=InnoDB;

ALTER TABLE t ENGINE=MyISAM;

In InnoDB, the table would actually get some different ROW_FORMAT. When converting back to MyISAM, what happens might depend on the server version. The table could get the original ROW_FORMAT=FIXED from the .frm file, or the SQL layer could use the ROW_FORMAT that it got from the storage engine. It is possible that SHOW CREATE TABLE displays what the .frm file says, while SHOW TABLES or SHOW TABLE STATUS would display what the storage engine says.

MySQL 8.0 might fix this in the Global Data Dictionary (at least it was discussed a couple of years ago, and WL#7811 where I proposed a fix was design-approved more than 2 years ago). We will soon see how much they fixed this, as more 8.0 releases are coming out. A related thing is the need to distinguish implicit and explicit attributes. The SQL layer developers of MySQL would not want SHOW CREATE TABLE to display something that was implied by the storage engine (but they are happy to display NOT NULL that was implied for PRIMARY KEY columns). So, they would want to distinguish if an attribute was specified by the user.

For MariaDB, I have proposed a design fix in MDEV-11655. That is, we would basically write the CREATE TABLE string into a table, and this string would be the source of metadata. .frm files could still be used when manually copying files into the data directory.

In any case, the reported bug is not something that can be fixed in storage engines such as InnoDB. It must be fixed in the SQL layer, in the code that writes the .frm file in ALTER TABLE.

This is kind of intentional. KEY_BLOCK_SIZE is per-key attribute, specifying it on the table level is just a shortcut for setting it for every key that doesn't have it otherwise set.

When you create a table with KEY_BLOCK_SIZE=8, all keys get KEY_BLOCK_SIZE=8. When you do ALTER TABLE ... KEY_BLOCK_SIZE=0, it sets it for "every key that doesn't have it otherwise set", but all keys do, so this doesn't change anything per-key. You also need to recreate keys

alter table t1 drop primary key, add primary key (id1) key_block_size=0,

               drop key id2, add unique (id2) key_block_size=0;

After that KEY_BLOCK_SIZE will be completely gone from your SHOW CREATE TABLE.

There's an issue, though. With such an ALTER TABLE, MariaDB doesn't notice that the keys are changed (it compares the old and the new key definitions, but not key block sizes), so it treats the whole ALTER as no-op.
I've fixed it now, in new 10.0 (and 10.1, 10.2) this ALTER will work.

I know it might be intentional to have different KEY_BLOCK_SIZE on different KEYs.
So what is the good way to uncompress a table ? Is that what you share above Sergei (drop PK, add PK, drop KEY1, add KEY1, ...) ?
Thanks.

First, I thought that the first command ALTER TABLE ... ROW_FORMAT=DYNAMIC did uncompress the table, it only left a (harmless) warning for the ignored KEY_BLOCK_SIZE value. Was I wrong?

Second, I don't quite like the asymmetry in setting and clearing key block size attributes — one can set it with one ALTER TABLE ...KEY_BLOCK_SIZE=8, but needs to clear it on the table level and for every single index. I believe we can fix that, but not in 10.0.

I am ok with a fix in 10.1.

InnoDB is only looking at the table-wide KEY_BLOCK_SIZE attribute. Each index of the table will use the same ROW_FORMAT=COMPRESSED page size. MariaDB could support per-index KEY_BLOCK_SIZE for InnoDB, if there is demand for it, but I do not think it can happen before 10.3. Inside InnoDB, it would likely require having one file for each distinct KEY_BLOCK_SIZE, or for each index, so it would not be a simple change.

SHOW TABLE STATUS or SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES should reflect the actual ROW_FORMAT and page size that are used in InnoDB. The .frm file can contain conflicting information.

So, I've fixed ALTER TABLE to rebuild the index if KEY_BLOCK_SIZE changes.

The rest of the issue is moved to MDEV-12152, it cannot be fixed in 10.0

The way this is "fixed" is unclear to me. Which ALTER TABLE will rebuild the index ?

Does that mean that ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; does not rebuild indexes for InnoDB in "old" versions (so does not fully uncompress the table ? If this is the case, this should be considered as an InnoDB bug (and what is the "clean" way of uncompressing an InnoDB table) ?

Wouldn't ALTER TABLE t1 ROW_FORMAT=DYNAMIC; be the right way of uncompressing an InnoDB table ? In this case, it should not produce a warning and it should not "push" the KEY_BLOCK_SIZE in KEYs (I am not sure if it does, I did not test).

Note: in MDEV-12152, I suggested that ALTER TABLE t1 ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=0; should fail for InnoDB tables.

This is "fixed" in a way that

alter table t1 drop primary key, add primary key (id1) key_block_size=0,

               drop key id2, add unique (id2) key_block_size=0;

would not be treated as a no-op, the server would actually notice that the old and new index definitions are different. That was clearly a bug and it's fixed in 10.0.

The rest of this issue is more like a change in behavior, not a bug fix, so we cannot do it in 10.0. It's now in MDEV-12152.