This is an umbrella task for allowing ALTER TABLE to be instantaneous in cases that cannot fail due to existing records being incompatible with the altered table definition. Later, MDEV-16356 and MDEV-16291 could extend this to support ALGORITHM=NOCOPY for operations that can avoid rebuilding tables, but need to validate the data.

Introduction

Traditionally, ALTER TABLE would be roughly equivalent to the following SQL statements:

CREATE TABLE `#sql-…` (…);

INSERT INTO `#sql-…` SELECT … FROM t;

RENAME TABLE t TO `#sql2-…`, `#sql-…` TO t;

DROP TABLE `#sql2-…`;

This mode of operation is still available by specifying ALGORITHM=COPY or SET old_alter_table=1 (or starting with MDEV-13134 in MariaDB 10.3, SET alter_algorithm=copy).

Copying a table and rebuilding all its indexes can be a very expensive operation. While InnoDB mostly allows tables to be rebuilt online since MariaDB Server version 10.0, the temporary files can occupy a significant amount of space and I/O capacity.

There are cases where the data format is not affected by the ALTER TABLE operation; only metadata needs to be updated. Examples include renaming columns, changing the default values of columns, and changing the maximum length of a VARCHAR column such that the storage format does not change.

A goal for MariaDB Server is to allow instantaneous execution of any ALTER TABLE operation where data conversions cannot fail, and indexes do not need to be rebuilt. Even in cases where some affected indexes have to be rebuilt, it will be more efficient to only rebuild some indexes than to copy the whole table.

The goal can be reformulated as: Avoid rebuilding the table.

How to avoid rebuilding the table, if the underlying storage format would be affected by the ALTER TABLE operation? By extending the storage format in a way that allows the data to be in ‘non-canonical’ format. The main examples of this are MDEV-11369 and MDEV-15562, which implement ADD COLUMN, DROP COLUMN and changing the order of columns.

The original InnoDB storage format (retroactively named ROW_FORMAT=REDUNDANT) is very generic, basically allowing NULL values and arbitrary length for every column. For it, MDEV-15562 would be the only storage format change needed to avoid unnecessary rebuild of the table.

Note: Whenever the PRIMARY KEY is changed, all indexes will have to be rebuilt. Likewise, some operations on indexed columns may require the indexes to be rebuilt.

The space-optimized row formats COMPACT and DYNAMIC omit ‘is null’ flags for NOT NULL columns and length information for fixed-length columns. MDEV-17520 could extend MDEV-15563 to these ROW_FORMAT by using a more flexible encoding for all clustered index pages that have been modified since the ALTER TABLE operation.

Operations that involve adding or dropping indexes (also DROP COLUMN can imply this) will not be supported for ALGORITHM=INSTANT; they will be supported with ALGORITHM=NOCOPY. ALTER TABLE…ADD [UNIQUE] INDEX supports concurrent modifications to the table since MariaDB 10.0. MDEV-16223 could defer ADD INDEX to a background operation.

Operations that will continue to be refused by ALGORITHM=INSTANT (and ALGORITHM=NOCOPY even after MDEV-16291) include:

• Changing ROW_FORMAT or ENGINE
• Altering a table that is in ROW_FORMAT=COMPRESSED
• Dropping, adding or changing PRIMARY KEY columns, or ADD/DROP PRIMARY KEY

Any ALTER TABLE that would be refused with ALGORITHM=NOCOPY (anything that rebuilds the clustered index) will drop any ‘instant ALTER TABLE’ metadata. The metadata would also be deleted if a rebuild is explicitly requested by the use of the FORCE keyword.

Metadata format changes

In InnoDB, instant ALTER TABLE affects clustered index page leaf records only. The data dictionary will reflect the most recent table definition. Additional metadata for interpreting records that correspond to an earlier version of the table definition will be stored in the clustered index tree as follows.

• MDEV-11369 in MariaDB 10.3.4 changed the root page type code to FIL_PAGE_TYPE_INSTANT to indicate that instant ALTER TABLE has been used. It also introduced a hidden metadata record at the start of the clustered index, on the leftmost leaf page. The new page type code prevents older MariaDB versions from opening the table.
• MDEV-15562 slightly modifies the format of the metadata record to represent dropped and reordered index fields. (All columns will internally be added last in the user records in the clustered index leaf pages.) MariaDB 10.3 will refuse to open such tables, because new info_bits will be set in the metadata record.

Data format changes

User records in the clustered index leaf pages will have to indicate which format they correspond to.

• For other than ROW_FORMAT=REDUNDANT, MDEV-11369 introduced REC_STATUS_COLUMNS_ADDED that indicates the presence of an optional record header that encodes the number of 'instantly added' columns that are present in the record.
• For ROW_FORMAT=REDUNDANT, MDEV-11369 simply stores the number of fields in the record header.
• In MDEV-11369 and MDEV-15562, any 'instantly added' columns whose values are missing from the end of the clustered index record will be substituted with the values stored in the metadata record.
• MDEV-15562 will not change the user record format in any way. Instantly added columns are always added as last fields in the clustered index leaf page records.
• MDEV-17520 would allow clustered index leaf pages to be in a format where the metadata version of each record is identified.

A note on MVCC

Because ha_innobase::commit_inplace_alter_table() will be invoked while holding MDL_EXCLUSIVE, any transactions that read or modified the table must finish before the ALTER TABLE can commit. But it is possible that some old transaction tries to do its first access to the table after the ALTER TABLE committed. Such transactions may receive an error message 'table definition changed', as noted in MySQL Bug#28432. It would be too much effort to support MVCC if a transaction after ALTER modified a record (converting it to newer dictionary version) that would otherwise be visible to the old transaction.
Here is the scenario in SQL:

connection con1;

START TRANSACTION WITH CONSISTENT SNAPSHOT; -- creates read view

connection con2;

ALTER TABLE t CHANGE COLUMN b b INT NULL;

UPDATE t SET b=1 WHERE a=100;

connection con1;

SELECT * FROM t WHERE a=1; -- might be OK, if the record is still in old format

SELECT * FROM t WHERE a=100; -- error: record is in to new format

For simplicity and consistency, we could always return an error to the SELECT statements (after any ALTER TABLE).

ALTER TABLE operations that potentially affect the format of a row

In MariaDB Server 10.2, the following alter_table_operations might require a table to be rebuilt:

Legend:

• can be performed instantly
• can be performed instantly, except if any secondary indexes need to be rebuilt
• not instantaneous; could later be performed without rebuild, with validation
• will continue to require full table rebuild