Details

Type: Task
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Fix Version/s: 10.3.2
Component/s: Storage Engine - InnoDB
Labels:
- contribution
- foundation

Sprint:
10.2.4-5, 10.3.1-2

Description

Add support for instant add column for InnoDB when adding a new column with a default value last.

We have got patches from Alibaba and TenCent for doing this. We should choose either one or merge them.

The idea in both patches is to add a new row type to InnoDB where the number of columns
are stored in each row in 1-2 bytes. When creating a row we store the current number of columns. This allows us to fill any extra columns with default values on read.

Attachments

Issue Links

blocks

MDEV-15562 Instant DROP COLUMN or changing the order of columns

Closed

causes

MDEV-14022 Upgrade from 10.0/10.1 fails on assertion `!is_user_rec || !leaf || index->is_dummy || dict_index_is_ibuf(index) || n == n_fields || (n >= index->n_core_fields && n <= index->n_fields)'

Closed

MDEV-14062 Upgrade from 10.0 fails on assertion `!is_user_rec || n >= index->n_core_fields || n >= rec_offs_n_fields(offsets)' failed in rec_offs_make_valid

Closed

MDEV-14067 Assertion `node->pcur.btr_cur.low_match == node->ref->n_fields' failed

Closed

MDEV-14111 Inplace update assert after instant adding column

Closed

MDEV-14660 Assertion failure in lock_move_rec_list_start() after instant ADD COLUMN

Closed

MDEV-14663 Assertion `page_is_root(block->frame)' failed in innobase_add_instant_try

Closed

MDEV-14837 Duplicate primary keys are allowed after ADD COLUMN / UPDATE

Closed

MDEV-14897 After UPDATE of instant ADD COLUMN, PRIMARY KEY accepts duplicates

Closed

MDEV-14906 Assertion `index->is_instant()' failed in rec_init_offsets_comp_ordinary upon DELETE from table

Closed

MDEV-15060 Assertion `0' failed in row_log_table_apply_op after instant ADD when the table is emptied during subsequent ALTER TABLE

Closed

MDEV-15871 Assertion `ptr' failed in ut_align_down

Closed

MDEV-16017 galera mtr tests fail with sst_xtrabackup-v2

Closed

MDEV-16131 Assertion `is_instant() || id == DICT_INDEXES_ID' failed in dict_index_t::instant_field_value

Closed

MDEV-17126 UPDATE of a BLOB column with long DEFAULT value fails

Confirmed

MDEV-17196 Crash during instant ADD COLUMN with long DEFAULT value

Closed

MDEV-17197 "1713: Undo log record is too big" for instant ADD COLUMN with long DEFAULT for BLOB

Confirmed

MDEV-17383 BLOB leak when emptying an instantly altered table

Open

MDEV-17483 Insert on delete-marked record can wrongly inherit old values for instantly added column

Closed

MDEV-17821 Assertion `!page_rec_is_supremum(rec)' failed in btr_pcur_store_position or non-debug assertion failure in rem/rem0rec.cc upon HANDLER READ

Closed

MDEV-17823 Assertion `!rec_offs_nth_default(clust_offs, clust_pos)' failed in row_sel_sec_rec_is_for_clust_rec

Closed

MDEV-17881 Assertion failure in cmp_dtuple_rec_with_match_bytes after instant ADD COLUMN

Closed

MDEV-18295 IMPORT TABLESPACE fails with instant-altered tables

Closed

MDEV-19385 InnoDB: Failing assertion: !cursor->index->is_committed() with virtual columns / indexes, WITHOUT foreign keys and temporary tables

Closed

MDEV-19783 Random crashes and corrupt data in INSTANT-added columns

Closed

MDEV-19916 Corruption after instant ADD/DROP and shrinking the index tree

Closed

MDEV-20066 Wrong value on instantly added column after DELETE and UPDATE

Closed

MDEV-23072 Diskspace not reused for Blob in data file

Closed

MDEV-23334 SIGSEGV or assertion failures in rec_get_status upon ALTER TABLE

Closed

MDEV-24323 Crash on recovery after server kill during instant ADD COLUMN

Closed

MDEV-24620 ASAN heap-buffer-overflow in rec_init_offsets_comp_ordinary

Closed

MDEV-24653 Assertion `block->page.id().page_no() == index->page' failed in innobase_instant_try

Closed

MDEV-24730 Corruption in online ALTER TABLE with ROW_FORMAT=REDUNDANT

Closed

MDEV-26577 InnoDB: Failing assertion: dict_tf2_is_valid(flags, flags2) during ADD COLUMN

Closed

is duplicated by

MDEV-13076 AliSQL: [Feature] Issue#43 Add Column Dynamically

Closed

MDEV-14535 Duplicate entry on ALTER TABLE ADD COLUMN

Closed

is part of

MDEV-10137 Providing compatibility to other databases

Open

relates to

MDEV-11424 Instant ALTER TABLE of failure-free record format changes

Closed

MDEV-13134 Introduce ALTER TABLE attributes ALGORITHM=NOCOPY and ALGORITHM=INSTANT

Closed

MDEV-13176 ALTER TABLE…CHANGE col col TIMESTAMP NOT NULL DEFAULT… fails

Closed

MDEV-13745 ALTER TABLE…ADD COLUMN…POINT NOT NULL without DEFAULT should be refused if the table is not empty

Stalled

MDEV-13899 IMPORT TABLESPACE may corrupt ROW_FORMAT=REDUNDANT tables

Closed

MDEV-13942 Potential InnoDB SPATIAL INDEX corruption during root page split

Closed

MDEV-14016 Allow instant ADD COLUMN, ADD INDEX, LOCK=NONE

Closed

MDEV-14168 Unconditionally allow ALGORITHM=INPLACE for setting a column NOT NULL

Closed

MDEV-14246 Skip the ha_innobase::prepare_inplace_alter_table() phase if no inplace_alter_table() phase is needed

Closed

MDEV-14848 MariaDB 10.3 refuses InnoDB crash-upgrade from MariaDB 10.2

Closed

MDEV-16282 ALTER TABLE t ADD COLUMN c INT, ADD INDEX(c), ALGORITHM=NOCOPY fails

Stalled

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-18926 Support ALTER TABLE…ADD COLUMN…ADD INDEX, ALGORITHM=NOCOPY

Open

MDEV-21693 ALGORITHM=INSTANT does not work for partitioned tables

Closed

MDEV-23632 ALTER TABLE...ADD KEY creates corrupted index on virtual column

Closed

MDEV-25971 Instant ADD COLUMN fails to issue truncation warnings

Closed

MDEV-28462 AddressSanitizer: use-after-poison dict_index_t::get_n_nullable(unsigned long) const

Closed

MDEV-28912 Assertion `ha_alter_info->key_count + (dict_table_get_first_index(ctx->new_table))->is_gen_clust() + .... in bool prepare_inplace_alter_table_dict

Closed

MDEV-29440 InnoDB instant ALTER TABLE recovery wrongly uses READ COMMITTED isolation level instead of READ UNCOMMITTED

Closed

MDEV-14029 Server does not remove #sql*.frm files after crash during ALTER TABLE

Closed

MDEV-15522 Change galera suite MTR tests to use mariabackup instead of xtrabackup

Closed

MDEV-16830 ALTER TABLE DROP FOREIGN KEY - unexpected end of stream error

Closed

MDEV-18095 InnoDB assertion failure when access table

Closed

MDEV-18519 0x7f0118195700 InnoDB: Assertion failure in file /home/buildbot/buildbot/build/mariadb-10.3.7/storage/innobase/btr/btr0cur.cc line 4308

Closed

MDEV-19611 INPLACE ALTER does not fail on bad implicit default value

Closed

MDEV-20048 dtuple_get_nth_field(): Assertion 'n < tuple->n_fields' failed on ROLLBACK after instant DROP COLUMN

Closed

MDEV-20590 Introduce a file format constraint to ALTER TABLE

Closed

MDEV-24796 Assertion `page_has_next(block->frame) || rec_is_alter_metadata(p, *index) || block->page.id().page_no() != index->page' failed in btr_pcur_store_position and other related asserts

Closed

MDEV-32668 tables with UNIQUE blob columns cannot be alter_algorithm=INSTANT modified

Open

MDEV-34223 Innodb - add status variable for number of bulk inserts

Closed

(29 causes, 2 is duplicated by, 1 is part of, 32 relates to)

Activity

Ascending order - Click to sort in descending order

View 13 older comments

Marko Mäkelä added a comment - 2017-09-04 11:35 - edited

To avoid complicating import, export, and future data dictionary related tasks such as MDEV-11655, I am trying to prototype a design change that minimizes the format changes to the InnoDB data dictionary tables.
Instead of introducing a new table SYS_COLUMNS_ADDED to store the default values of instantly-added columns, my revised design would introduce a special 'default row' record at the start of the clustered index. This record would carry the magic REC_INFO_MIN_REC_FLAG, which was until now only set on the leftmost node pointer record of each non-leaf level.
We must also store the original number of columns (’core columns’) somewhere in the clustered index tree, instead of only storing it somewhere in the InnoDB data dictionary.
There is a chicken-and-egg problem here because of my unfortunate design mistake in MySQL 5.0.3 that reserves the n_nullable bits in node pointer records for ROW_FORMAT!=REDUNDANT. It is clearly not possible to navigate to the leftmost leaf page by reading child page numbers from mach_read_from_4(rec_get_next_ptr(node_ptr_with_min_rec_flag, 1)-4) because the next-record pointer may have been changed, for example if some pages were merged and the original next-record pointer was moved to the PAGE_FREE list.

It looks like instant ADD COLUMN should write 1+UT_BITS_IN_BYTES(n_nullable) to spare 8 bits in the clustered index root page. A good candidate would be the high-order byte of PAGE_DIRECTION, which was always written as 0 starting with MySQL 4.0.14 and 4.1.1, so always for ROW_FORMAT!=REDUNDANT. Originally, it was sometimes written as uninitialized garbage for ROW_FORMAT=REDUNDANT in the initial InnoDB version.

Marko Mäkelä added a comment - 2017-09-04 11:35 - edited To avoid complicating import, export, and future data dictionary related tasks such as MDEV-11655 , I am trying to prototype a design change that minimizes the format changes to the InnoDB data dictionary tables. Instead of introducing a new table SYS_COLUMNS_ADDED to store the default values of instantly-added columns, my revised design would introduce a special 'default row' record at the start of the clustered index. This record would carry the magic REC_INFO_MIN_REC_FLAG, which was until now only set on the leftmost node pointer record of each non-leaf level. We must also store the original number of columns (’core columns’) somewhere in the clustered index tree, instead of only storing it somewhere in the InnoDB data dictionary. There is a chicken-and-egg problem here because of my unfortunate design mistake in MySQL 5.0.3 that reserves the n_nullable bits in node pointer records for ROW_FORMAT!=REDUNDANT. It is clearly not possible to navigate to the leftmost leaf page by reading child page numbers from mach_read_from_4(rec_get_next_ptr(node_ptr_with_min_rec_flag, 1)-4) because the next-record pointer may have been changed, for example if some pages were merged and the original next-record pointer was moved to the PAGE_FREE list. It looks like instant ADD COLUMN should write 1+UT_BITS_IN_BYTES(n_nullable) to spare 8 bits in the clustered index root page. A good candidate would be the high-order byte of PAGE_DIRECTION, which was always written as 0 starting with MySQL 4.0.14 and 4.1.1 , so always for ROW_FORMAT!=REDUNDANT. Originally, it was sometimes written as uninitialized garbage for ROW_FORMAT=REDUNDANT in the initial InnoDB version .

Marko Mäkelä added a comment - 2017-09-12 08:21

I think that we must actually store the original number of clustered index fields in the root page, using 10 bits.
Because the PAGE_DIRECTION field may contain garbage for ROW_FORMAT=REDUNDANT tables, we must introduce a new FIL_PAGE_TYPE to tell if the new field PAGE_INSTANT is present in the former most significant 10 bits of PAGE_DIRECTION.
The new FIL_PAGE_TYPE on the clustered index root page will also prevent older MariaDB versions from opening or importing tables where instant ADD COLUMN has been used.

The special ‘default row’ would contain the values of instantly added columns. When the table is opened for the first time and the clustered index root page indicates that instant ADD COLUMN may have been used, we will fetch the ‘default row’. If its field count disagrees with the field count of the clustered index in the data dictionary, we will treat the table as corrupted.

Note: Whenever the clustered index becomes empty of user records, the root page should be reset to ‘non-instant’ form (FIL_PAGE_TYPE reset to FIL_PAGE_INDEX, and the PAGE_INSTANT bits be cleared, and the ‘default row’ deleted). This improves compatibility with old versions.

Marko Mäkelä added a comment - 2017-09-12 08:21 I think that we must actually store the original number of clustered index fields in the root page, using 10 bits. Because the PAGE_DIRECTION field may contain garbage for ROW_FORMAT=REDUNDANT tables, we must introduce a new FIL_PAGE_TYPE to tell if the new field PAGE_INSTANT is present in the former most significant 10 bits of PAGE_DIRECTION. The new FIL_PAGE_TYPE on the clustered index root page will also prevent older MariaDB versions from opening or importing tables where instant ADD COLUMN has been used. The special ‘default row’ would contain the values of instantly added columns. When the table is opened for the first time and the clustered index root page indicates that instant ADD COLUMN may have been used, we will fetch the ‘default row’. If its field count disagrees with the field count of the clustered index in the data dictionary, we will treat the table as corrupted. Note: Whenever the clustered index becomes empty of user records, the root page should be reset to ‘non-instant’ form (FIL_PAGE_TYPE reset to FIL_PAGE_INDEX, and the PAGE_INSTANT bits be cleared, and the ‘default row’ deleted). This improves compatibility with old versions.

Marko Mäkelä added a comment - 2017-09-22 16:45

I have now mostly implemented the revised design.
The record header format changes will be slightly different too. We will not introduce any new info_bits.
For ROW_FORMAT=REDUNDANT records, there is no change to the record format. For ROW_FORMAT=COMPACT and ROW_FORMAT=DYNAMIC, the status bit value REC_STATUS_COLUMNS_ADDED=4 will signal that the clustered index leaf page record has additional fields.

One change that I did not implement yet will be that for REC_STATUS_COLUMNS_ADDED records, the ‘number of fields’ stored in the record header will be n_fields-n_core_fields-1. That is, if 1 field was added, the value 0 will be encoded. This allows up to 128 instantly added columns to be represented in 1 additional header byte.

Marko Mäkelä added a comment - 2017-09-22 16:45 I have now mostly implemented the revised design. The record header format changes will be slightly different too. We will not introduce any new info_bits. For ROW_FORMAT=REDUNDANT records, there is no change to the record format. For ROW_FORMAT=COMPACT and ROW_FORMAT=DYNAMIC, the status bit value REC_STATUS_COLUMNS_ADDED=4 will signal that the clustered index leaf page record has additional fields. One change that I did not implement yet will be that for REC_STATUS_COLUMNS_ADDED records, the ‘number of fields’ stored in the record header will be n_fields-n_core_fields-1. That is, if 1 field was added, the value 0 will be encoded. This allows up to 128 instantly added columns to be represented in 1 additional header byte.

Marko Mäkelä added a comment - 2017-09-25 05:39 - edited

Changes to the undo log format

For inserting the ‘default row’ record, we need a new undo log record type TRX_UNDO_INSERT_DEFAULT. This is because the normal type TRX_UNDO_INSERT_REC can only be used for searching by key, not by the magic REC_INFO_MIN_REC_FLAG at the start of the index.

For updating records so that the number of clustered index fields is changing, we can use the existing undo log record types. We must merely treat the rollback of the ‘default row’ record specially. This is demonstrated by the tests innodb.instant_alter_crash and innodb.instant_alter_inject. The trick is that on rollback, we will truncate the ‘default row’ record just like we would truncate user records, as described below.

Optimizing storage

To optimize storage, if a record is inserted or updated in such a way that the clustered index fields corresponding to the last instantly added columns have the same value as in the instant ADD COLUMN…DEFAULT clause (same values as in the ‘default row’ record), these fields will be omitted from the record. Example:

CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB;

SET @ts = @@session.timestamp;

ALTER TABLE t1 ADD COLUMN (t TIMESTAMP NOT NULL DEFAULT current_timestamp(), i INT, v VARCHAR(20) NOT NULL DEFAULT 'abcde');

INSERT INTO t1 SET id=1;

In this example, the ‘default row’ record would store (current_timestamp(),NULL,'abcde') for the instantly added columns. The value of current_timestamp() would be the one that was evaluated during the ALTER TABLE statement. The clustered index record for the INSERT would explicitly store the values of the columns (id), or (id,t) if the default value expression current_timestamp() during the ALTER and INSERT evaluate to a different value. The values of the instantly added columns i,v would correspond to the ‘default row’ record and could be omitted. If the last instantly added column differs from the ‘default row’ record, then all fields would have to be stored in the clustered index record.
The same rules apply to update:

BEGIN;

UPDATE t1 SET v='foo';

ROLLBACK;

ALTER TABLE t1 CHANGE t t TIMESTAMP NOT NULL DEFAULT '2000-07-31 21:10:05';

SET timestamp=@ts;

UPDATE t1 SET t=current_timestamp(), v=DEFAULT;

The first UPDATE would store all columns (id,t,i,v) in the clustered index. The ROLLBACK would shrink the record back to (id) or (id,t). The second UPDATE would shrink the record back to (id), because the timestamp is being assigned to the same value that existed when the table was created.
Note: the current DEFAULT value of the column t does not matter. It is the DEFAULT value that was specified during instant ADD COLUMN that matters. This value is what the ‘default row’ record will contain.

Marko Mäkelä added a comment - 2017-09-25 05:39 - edited Changes to the undo log format For inserting the ‘default row’ record, we need a new undo log record type TRX_UNDO_INSERT_DEFAULT. This is because the normal type TRX_UNDO_INSERT_REC can only be used for searching by key, not by the magic REC_INFO_MIN_REC_FLAG at the start of the index. For updating records so that the number of clustered index fields is changing, we can use the existing undo log record types. We must merely treat the rollback of the ‘default row’ record specially. This is demonstrated by the tests innodb.instant_alter_crash and innodb.instant_alter_inject. The trick is that on rollback, we will truncate the ‘default row’ record just like we would truncate user records, as described below. Optimizing storage To optimize storage, if a record is inserted or updated in such a way that the clustered index fields corresponding to the last instantly added columns have the same value as in the instant ADD COLUMN…DEFAULT clause (same values as in the ‘default row’ record), these fields will be omitted from the record. Example: CREATE TABLE t1 (id INT PRIMARY KEY ) ENGINE=InnoDB; SET @ts = @@session. timestamp ; ALTER TABLE t1 ADD COLUMN (t TIMESTAMP NOT NULL DEFAULT current_timestamp (), i INT , v VARCHAR (20) NOT NULL DEFAULT 'abcde' ); INSERT INTO t1 SET id=1; In this example, the ‘default row’ record would store (current_timestamp(),NULL,'abcde') for the instantly added columns. The value of current_timestamp() would be the one that was evaluated during the ALTER TABLE statement. The clustered index record for the INSERT would explicitly store the values of the columns (id), or (id,t) if the default value expression current_timestamp() during the ALTER and INSERT evaluate to a different value. The values of the instantly added columns i,v would correspond to the ‘default row’ record and could be omitted. If the last instantly added column differs from the ‘default row’ record, then all fields would have to be stored in the clustered index record. The same rules apply to update: BEGIN ; UPDATE t1 SET v= 'foo' ; ROLLBACK ; ALTER TABLE t1 CHANGE t t TIMESTAMP NOT NULL DEFAULT '2000-07-31 21:10:05' ; SET timestamp =@ts; UPDATE t1 SET t= current_timestamp (), v= DEFAULT ; The first UPDATE would store all columns (id,t,i,v) in the clustered index. The ROLLBACK would shrink the record back to (id) or (id,t). The second UPDATE would shrink the record back to (id), because the timestamp is being assigned to the same value that existed when the table was created. Note: the current DEFAULT value of the column t does not matter. It is the DEFAULT value that was specified during instant ADD COLUMN that matters. This value is what the ‘default row’ record will contain.

Marko Mäkelä added a comment - 2017-10-04 07:51

A counter will be introduced to keep track of the instant ALTER TABLE operations that affect the columns of the table:

SELECT variable_value FROM information_schema.global_status

WHERE variable_name = 'innodb_instant_alter_column'

This same counter can be used in the follow-up task ~~MDEV-11424~~. For now, it is only incremented for instant ADD COLUMN operations.

Marko Mäkelä added a comment - 2017-10-04 07:51 A counter will be introduced to keep track of the instant ALTER TABLE operations that affect the columns of the table: SELECT variable_value FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column' This same counter can be used in the follow-up task MDEV-11424 . For now, it is only incremented for instant ADD COLUMN operations.

People

Assignee:: Marko Mäkelä

Reporter:: Michael Widenius

Votes:: 1 Vote for this issue

Watchers:: 11 Start watching this issue

Dates

Due:: 2016-12-11

Created:: 2016-11-28 10:36

Updated:: 2024-05-24 06:26

Resolved:: 2017-10-06 06:56

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Instant add column for InnoDB