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.
Traditionally, ALTER TABLE would be roughly equivalent to the following SQL statements:
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.
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-11369in 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-15562slightly 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.
User records in the clustered index leaf pages will have to indicate which format they correspond to.
- For other than ROW_FORMAT=REDUNDANT,
MDEV-11369introduced 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-11369simply stores the number of fields in the record header.
MDEV-11369and 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-15562will 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 of ROW_FORMAT=COMPACT and ROW_FORMAT=DYNAMIC to be in a format that resembles ROW_FORMAT=REDUNDANT.
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:
For simplicity and consistency, we could always return an error to the SELECT statements (after any ALTER TABLE).
In MariaDB Server 10.2, the following alter_table_operations might require a table to be rebuilt:
|ALTER_ADD_VIRTUAL_COLUMN||ADD COLUMN…AS||Virtual columns are always added instantly|
|ALTER_ADD_STORED_GENERATED_COLUMN||ADD COLUMN…PERSISTENT AS||cannot be instant; until MDEV-16354 requires rebuild with ALGORITHM=COPY|
|ALTER_ADD_PK_INDEX||ADD PRIMARY KEY||Requires all indexes to be rebuilt.|
|ALTER_DROP_PK_INDEX||DROP PRIMARY KEY||Requires all indexes to be rebuilt. Without ADD PRIMARY KEY, cannot even support online rebuilding (LOCK=NONE).|
|ALTER_CHANGE_CREATE_OPTION||ROW_FORMAT, KEY_BLOCK_SIZE, encryption||Requires rebuild; see MDEV-16291.|
|ALTER_COLUMN_NULLABLE||NULL||MDEV-17520 for COMPACT and DYNAMIC|
|ALTER_STORED_COLUMN_ORDER||FIRST, LAST, AFTER|
|ALTER_RECREATE_TABLE||FORCE||the sole purpose of this keyword is to explicitly request rebuild|
|ALTER_STORED_COLUMN_TYPE||CHANGE to wider type||MDEV-17520|
|ALTER_COLUMN_UNVERSIONED||CHANGE…WITH[OUT] SYSTEM VERSIONING|
|ALTER_ADD_SYSTEM_VERSIONING||ADD SYSTEM VERSIONING||Must rebuild the PRIMARY KEY and thus the full table|
|ALTER_DROP_SYSTEM_VERSIONING||DROP SYSTEM VERSIONING||Must rebuild the PRIMARY KEY and thus the full table|
|ALTER_ADD_PERIOD||ADD PERIOD FOR SYSTEM TIME||must be combined with ADD SYSTEM VERSIONING|
|ALTER_DROP_PERIOD||DROP PERIOD FOR SYSTEM TIME||must be combined with DROP SYSTEM VERSIONING|
|ALTER_ADD_CHECK_CONSTRAINT||ADD [CONSTRAINT] CHECK||MDEV-16356|
- 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