If a table is altered using the new INSTANT algorithm, it can force the table to use a non-canonical format:
Some operations cause a table's tablespace file to use a non-canonical storage format when the INSTANT algorithm is used. The affected operations include:
- Adding a column.
- Dropping a column.
- Reordering columns.
These operations require the following non-canonical changes to the storage format:
- A hidden metadata record at the start of the clustered index is used to store each column's DEFAULT value. This makes it possible to add new columns that have default values without rebuilding the table.
- A BLOB in the hidden metadata record is used to store column mappings. This makes it possible to drop or reorder columns without rebuilding the table. This also makes it possible to add columns to any position or drop columns from any position in the table without rebuilding the table.
- If a column is dropped, old records will contain garbage in that column's former position, and new records will be written with NULL values, empty strings, or dummy values.
This is generally not a problem. However, there may be cases where users may want to avoid putting a table into this format. For example, users may want to avoid bugs similar to
MDEV-19783 and MDEV-20066:
There are some known bugs that could lead to issues when an InnoDB DDL operation is performed using the INSTANT algorithm. This algorithm will usually be chosen by default if the operation supports the algorithm.
The effect of many of these bugs is that the table seems to forget that its tablespace file is in the non-canonical storage format.
We may want to add a way for users to avoid putting tables into the non-canonical INSTANT format, if they would like.
How would it be implemented?:
- A new value for the ALGORITHM clause and the alter_algorithm system variable that would lead to data files using the non-canonical data format? i.e. CANON_DATAFILES, NOBLOBMAP, STANDARD_DATAFILES, etc.
- A new InnoDB system variable that could enable/disable operations that would lead to data files using the non-canonical data format? i.e. innodb_noncanonical_alter=0