Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
MDEV-11369 implemented instant ALTER TABLE…ADD COLUMN by introducing a hidden metadata record (originally called ‘default row’) that stores metadata of the original table structure. Based on this record, we know the original structure of the clustered index (where the data is stored together with the PRIMARY KEY). The latest structure is persisted in the data dictionary.
We can extend the format of the hidden record to store a mapping between clustered index fields and table columns. Any ADD COLUMN would be instantly added as last field in the clustered index record, but the field-to-column map would allow it to be positioned anywhere in the table. Any DROP COLUMN will simply detach the clustered index field from the table columns.
Note: DROP COLUMN of an indexed column would imply DROP INDEX (and in the case of a non-UNIQUE multi-column index, possibly ADD INDEX). These will no be allowed with ALGORITHM=INSTANT, but unlike before this work, they can be allowed with ALGORITHM=NOCOPY. Also, MDEV-16223 could defer the ADD INDEX, allowing the DROP COLUMN operation to complete faster.
Note: While this work would enable the ordering of columns in the SELECT * FROM t to be changed instantaneously, changing the ordering of PRIMARY KEY columns (such as changing PRIMARY KEY(a,b) to PRIMARY KEY(b,a)) would continue to require a full table rebuild. The syntax for this is:
ALTER TABLE t DROP PRIMARY KEY, ADD PRIMARY KEY(b,a); |
Data format changes
Instantly added columns are added last in the user records, in the format that was introduced by MDEV-11369.
For instantly dropped columns, INSERT and UPDATE will continue to write dummy values as follows:
- In ROW_FORMAT=REDUNDANT, write 0-byte fields.
- In ROW_FORMAT=COMPACT or ROW_FORMAT=DYNAMIC, for columns that allow NULL, write NULL.
- In ROW_FORMAT=COMPACT or ROW_FORMAT=DYNAMIC, for variable-length NOT NULL columns, write 0-byte fields.
- In ROW_FORMAT=COMPACT or ROW_FORMAT=DYNAMIC, for fixed-length NOT NULL columns, zero-fill the fixed length of bytes.
Metadata format changes
We will change the format of the metadata record that was introduced in MDEV-11369 as follows:
- In info_bits, we will set the REC_INFO_DELETED_FLAG to indicate the presence of column reordering information (a BLOB field).
- If REC_INFO_DELETED_FLAG is set, then the 'number of instantly added columns' will be encoded as starting from 1, so that 0 will mean that only the new BLOB field is present.
- If REC_INFO_DELETED_FLAG is set, then after the dummy PRIMARY KEY value and the DB_TRX_ID,DB_ROLL_PTR we will store a BLOB pointer to a page that stores information about dropped and reordered columns.
- After the BLOB pointer, we will include dummy or default values of all non-dropped columns.
- In COMPACT or DYNAMIC ROW_FORMAT, for dropped columns, the 'null bits' bitmap will indicate if the dropped column allowed NULL values.
- In COMPACT or DYNAMIC ROW_FORMAT, for dropped columns, the 'length' array will include 1 or 2 lengths for each dropped column, using variable-length encoding: the fixed length (0 if variable-length), and the maximum length if variable-length. Both lengths will use the variable-length encoding (1 byte for 0..127, else 2 bytes).
- In ROW_FORMAT=REDUNDANT, for any subsequently updated or inserted records, instantly dropped columns will be stored as 0 bytes. (In this format, each column is basically stored as variable-length.)
The metadata BLOB format
The payload of the BLOB will contain the following:
- 32 bits: Number of fields in the clustered index, including any instantly added or dropped non-virtual columns
- An array of 16 bits * n_fields: The location of each field in the physical clustered index record.
- If the most significant bit is set, it means that the column was dropped, and the remaining bits contain the NOT NULL flag and the fixed_len (0 if variable-length).
- Else, the entry will be 0‥n_fields-1, mapping the dict_index_t::fields[] of the clustered index of the latest table definition to fields in the clustered index records.
- This BLOB will always be stored off-page. Only the 20-byte BLOB pointer will be added to the metadata record, in any supported ROW_FORMAT.
- The unused trailer of the BLOB page must be zero-initialized. If there are nonzero bytes in the unknown area, we will refuse to open the table. This area is reserved for future extension.
Limitations
The maximum number of fields in a clustered index is a little less than 1023 fields. This limit is imposed by the redo log format as well as some main-memory data structures. This number includes any instantly dropped columns in the history of the table.
The minimum record length will include instantly dropped columns that were NOT NULL and fixed-length. It must be possible to insert at least 2 minimum-length records into a clustered index leaf page.
If these limits would be violated by ADD COLUMN after instant DROP COLUMN, then the operation cannot be performed as an instant operation, but it can be executed as a table-rebuild (ALGORITHM=INSTANT or ALGORITHM=COPY, but not ALGORITHM=NOCOPY or ALGORITHM=INSTANT).
Attachments
Issue Links
- blocks
-
MDEV-11424 Instant ALTER TABLE of failure-free record format changes
- Closed
-
MDEV-17520 Instant ALTER TABLE for failure-free column type changes
- Stalled
- causes
-
MDEV-17494 Refuse ALGORITHM=INSTANT when the row size is too large
- Closed
-
MDEV-17721 Various assertion failures due to MDEV-15562 instant ALTER TABLE
- Closed
-
MDEV-17735 Assertion failure in row_parse_int() on first ADD/DROP COLUMN when an AUTO_INCREMENT column is not in PRIMARY KEY
- Closed
-
MDEV-17793 Crash in purge after instant DROP and emptying the table
- Closed
-
MDEV-17813 Crash in instant ALTER TABLE due to purge concurrently emptying table
- Closed
-
MDEV-17844 Assertion `ulint(rec) == offsets[2]' failed in rec_offs_validate
- Closed
-
MDEV-17899 Instant ADD/DROP multiple failure assertions
- Closed
-
MDEV-17901 Assertion failure in file storage/innobase/include/row0row.ic line 211 after dropping column
- Closed
-
MDEV-18007 innodb.instant_alter_crash : Assertion failed: n < tuple->n_fields, data0data.ic, line 433
- Closed
-
MDEV-18098 Crash after rollback of instant DROP COLUMN
- Closed
-
MDEV-18190 A table with variable-length PRIMARY KEY is unaccessible after instant DROP COLUMN
- Closed
-
MDEV-18543 IMPORT TABLESPACE fails after instant DROP COLUMN
- Closed
-
MDEV-19202 Server crash in dict_col_t::name or Assertion `n' failed in find_old_col_no after invalid FK addition
- Open
-
MDEV-19485 [FATAL] InnoDB: Data field type 0, len 32 in dfield_check_typed, ASAN global-buffer-overflow in rtree_get_geometry_mbr
- Closed
-
MDEV-20048 dtuple_get_nth_field(): Assertion 'n < tuple->n_fields' failed on ROLLBACK after instant DROP COLUMN
- Closed
-
MDEV-20117 Assertion `0' failed in row_sel_get_clust_rec_for_mysql
- Closed
-
MDEV-20198 Instant ALTER TABLE is not crash-safe
- Closed
-
MDEV-21088 Table cannot be loaded after instant ADD/DROP COLUMN
- Closed
-
MDEV-21485 ASAN use-after-poison in dfield_get_len or Assertion `pos < index->n_def' failed
- Closed
-
MDEV-21645 SEGV in innobase_get_computed_value
- Closed
-
MDEV-21658 Error on online ADD PRIMARY KEY after instant DROP/reorder
- Closed
-
MDEV-22651 Assertion `dict_col_get_fixed_size(f.col, not_redundant()) <= 768' failed in dict_table_t::init_instant
- Closed
-
MDEV-22902 Assertion `!page_has_siblings(block->frame)' failed. in btr_pcur_store_position
- Closed
-
MDEV-23672 Assertion `v.v_indexes.empty()' failed in dict_table_t::instant_column
- Closed
-
MDEV-24512 Assertion failed in rec_is_metadata() in btr_discard_only_page_on_level() on rollback or purge
- Closed
-
MDEV-26141 Crash in instant ALTER TABLE after IMPORT TABLESPACE
- Confirmed
-
MDEV-26420 Buffer overflow on instant ADD/DROP of generated column
- Closed
-
MDEV-28919 Assertion `(((core_null) + 7) >> 3) == oindex.n_core_null_bytes || !not_redundant()' failed
- Closed
-
MDEV-35122 Incorrect NULL value handling for instantly dropped BLOB columns
- Closed
- is blocked by
-
MDEV-11369 Instant add column for InnoDB
- Closed
- is duplicated by
-
MDEV-18622 Instant DDL, including drop column, change column ordering etc.
- Closed
- relates to
-
MDEV-17459 Allow instant ALTER TABLE even if FULLTEXT INDEX exists
- Stalled
-
MDEV-17468 Avoid table rebuild on operations on generated columns
- Stalled
-
MDEV-17831 Assertion `supports_instant()' failed in dict_table_t::prepare_instant upon ADD COLUMN on table with KEY_BLOCK_SIZE
- Closed
-
MDEV-18775 Server crashes in dict_table_t::instant_column upon ADD COLUMN
- Closed
-
MDEV-20419 ADD COLUMN with DROP COLUMN wrongly inherits attributes
- Confirmed
-
MDEV-21693 ALGORITHM=INSTANT does not work for partitioned tables
- Closed
-
MDEV-28462 AddressSanitizer: use-after-poison dict_index_t::get_n_nullable(unsigned long) const
- Closed
-
MDEV-29440 InnoDB instant ALTER TABLE recovery wrongly uses READ COMMITTED isolation level instead of READ UNCOMMITTED
- Closed
-
MDEV-28760 Row size too large error after multiple drops/adds to an InnoDB table
- Closed
-
MDEV-29438 Recovery or backup of instant ALTER TABLE is incorrect
- Closed
-
MDEV-32668 tables with UNIQUE blob columns cannot be alter_algorithm=INSTANT modified
- Open