Implement instant ALTER TABLE operations where the specification of non-indexed columns is changed, for the cases that cannot be handled more efficiently. The special cases with efficient handling are:
MDEV-11369Instant ADD COLUMN…LAST
- MDEV-15562 Instant DROP COLUMN, ADD COLUMN anywhere, changing the order of columns
- MDEV-15564 Avoid table rebuild in ALTER TABLE on collation or charset changes
This work extends MDEV-15563 (Instant NOT NULL removal and conversion to a wider type for ROW_FORMAT=REDUNDANT) to ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPACT tables. (As always, instant ALTER TABLE is not supported for ROW_FORMAT=COMPRESSED.)
Operations that involve adding or dropping indexes (also DROP COLUMN can imply this) are outside the scope of this work; they are supported with ALGORITHM=NOCOPY. ALTER TABLE…ADD [UNIQUE] INDEX supports concurrent modifications to the table since MariaDB 10.0. MDEV-16223 would 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’ history. The history 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 'default row' 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 will slightly modify the format of the 'default row' 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 'default row' 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 'default row' 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-15563 changes the interpretation of clustered index leaf page records in ROW_FORMAT=REDUNDANT.
- This work extends MDEV-15563 to ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPACT tables.
Any INSERT or UPDATE after an instant ALTER that removes a NOT NULL constraint or changes a column to a wider type will cause all records in the affected clustered index leaf page to be rewritten in a format that resembles ROW_FORMAT=REDUNDANT, with the following differences:
- NULL columns will occupy 0 bytes of storage, also when fixed-length.
- CHAR(n) (unless NULL) will occupy n*mbminlen‥n*mbmaxlen bytes, instead of n*mbmaxlen.
- For ROW_FORMAT=DYNAMIC, no local prefix of off-page columns will be stored. For ROW_FORMAT=COMPACT, we will continue to write the 768-byte prefixes.
In ROW_FORMAT=REDUNDANT, the record header will store the length of each column (including fixed-length and NULL columns), using n_fields or 2·n_fields bytes. The fixed-length record header is 6 instead of 5 bytes. This will increase the size of each record by at least 1 byte, up to 2·n_fields+1 bytes.
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-15563 for ROW_FORMAT=REDUNDANT|
|ALTER_COLUMN_NULLABLE||NULL||COMPACT and DYNAMIC|
|ALTER_COLUMN_NOT_NULLABLE||NOT NULL||needs validation; MDEV-16291 will allow NOCOPY|
|ALTER_STORED_COLUMN_ORDER||FIRST, LAST, AFTER||MDEV-15562|
|ALTER_RECREATE_TABLE||FORCE||the sole purpose of this keyword is to explicitly request rebuild|
|ALTER_STORED_COLUMN_TYPE||CHANGE to wider type||MDEV-15563 for ROW_FORMAT=REDUNDANT|
|ALTER_STORED_COLUMN_TYPE||CHANGE to wider type||COMPACT and DYNAMIC|
|ALTER_STORED_GCOL_EXPR||CHANGE expr||will require validation and possible updates of all data; depends on MDEV-16291|
|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 without adding conversion recipes
- can be performed without adding conversion recipes; not instant if any secondary indexes need to be rebuilt
- can be performed instantly by adding a conversion recipe (as part of this work)
- not instant; could later be performed with validation
- will continue to require full table rebuild