Details
-
Task
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
Description
Implement online ALTER TABLE above the storage engine layer by mimicking what InnoDB does since MariaDB 10.0.
Intro
ALTER TABLE can perform many various table metadata alterations, individually or batched (many alterations at once). It supports different algorithms for applying those alterations and different lock levels restricting access to the table while it's being altered. What algorithm and lock level to use depends on the storage engine, requested alterations and explicitly specified algorithm and lock, if any. If no algorithm or lock level is explicitly specified, the server is supposed to select the best algorithm/lock combination automatically.
While certain alterations (like adding a column) can be done by certain storage engines (like InnoDB) internally (using InnoDB-specific ALGORITHM=INSTANT) and without locking the table (LOCK=NONE), the most universal ALTER TABLE algorithm that supports arbitrary alterations in arbitrary combinations is the COPY algorithm and it locks the table, allowing only read access during the whole ALTER TABLE duration. When the server has to resort to the COPY algorithm (because no other one can perform the requested set of alterations) it often means long periods of the application being essentially down, because the table cannot be written into.
The goal of this task is to allow the COPY algorithm to work without read-locking the table. In other words, this should make the combination ALGORITHM=COPY, LOCK=NONE possible.
Implementation
The COPY algorithm for ALTER ONLINE TABLE is supposed to do the following:
- Exclusively acquire the table Metadata Lock (MDL).
- Acquire the table lock for read (TL_READ)
- Read the first record. In table is empty, online is skipped (goto 11).
- Set up (a separate, per-table one) row-based replication for tracking changes from concurrent DMLs ("online changes").
- Downgrade the MDL lock.
- Copy the table contents (using a non-locking read if supported by the storage engine).
- Apply the online changes from the replicated contents.
- Unlock the table lock
- Exclusively lock the table MDL (upgrade to MDL_SHARED_WRITE).
- Apply any remaining online changes.
- Swap the old and new table, unlock, drop the old table.
This would remove some limitations that currently exist with the InnoDB-only online table rebuild. Basically, anything that is supported by ALGORITHM=COPY should 'just work' (however see the limitations section). The bulk copying could still happen in copy_data_between_tables(). A few examples:
- Arbitrary changes of column type will be possible, without duplicating any conversion logic.
- It will be possible to add virtual columns (materialized or not) together with adding indexes, while allowing concurrent writes (
MDEV-13795,MDEV-14332). - The ENGINE or the partitioning of a table can be changed, just like any other attribute.
[Not implemented here] We should remove the online table rebuild code from InnoDB (row_log_table_apply() and friends), and just let InnoDB fall back to this. The only ALTER ONLINE TABLE that could better be implemented inside storage engines would be ADD INDEX. Then, ALGORITHM=INPLACE would no longer be misleading, because it would mean exactly the same as the ALGORITHM=NOCOPY that was introduced in MDEV-13134. Before this, we must implement MDEV-515 (bulk load into an empty InnoDB table) to avoid a performance regression.
Behavior of different engines
The per-engine behavior depends on what operations can happen concurrently while TL_READ is held.
- Innodb can do any DML (except TRUNCATE i presume). It lazily opens the read view once the first record is read during the copy stage. This means that in theory some transaction can slip concurrently between TL_READ-locked table and first record is read. This is why we first read one record out, and then set up the online change buffer.
- Myisam/Aria only allow inserts in parallel with reads: The last table's record offset is remembered for the table handle, so copy stage will read out only the changes, that are already there. Other DMLs will be blocked until table lock is released.
- Online is disabled for temporary tables.
- For other engines, it depends on whether is it possible to acquire a particular table lock in parallel with TL_READ.
Limitations
- Embedded server doesn't support LOCK=NONE, Until HAVE_REPLICATION is enabled there (or until some finer refactoring).
- DROP SYSTEM VERSIONING is not currently supported, but the support can be added on demand
- ALTER TABLE ... ORDER BY is not and cannot be supported
- Tables which are referenced by FOREIGN KEYs with CASCADE operations, see
MDEV-29068 - ALTER IGNORE TABLE
- Adding autoinc to the existing column, when NO_AUTO_VALUE_ON_ZERO is not present, and there were no unchanged UNIQUE NOT NULL keys. A NULL column is always impossible to update to AUTOINC with Online COPY.
- Sequences are not supported
- ADD COLUMN ... AUTO_INCREMENT and ADD COLUMN ... DEFAULT(NEXTVAL(..))
- MODIFY ... NOT NULL DEFAULT(NEXTVAL(..)), if the column initially was NULLable
- Sequences
- Engines S3 and CONNECT
[Old part] Challenges
We should replicate the online rebuild on slaves in parallel, so that the master and slaves will be able to commit at roughly the same time. This would be something similar to MDEV-11675, which would still be needed for native online ADD INDEX, which would avoid copying the table.
In InnoDB, there is some logic for logging the changes when the PRIMARY KEY columns are changed, or a PRIMARY KEY is being added. The 'row event log' online_log will additionally contain the PRIMARY KEY values in the new table, so that the records can easily be found. The online_log will contain INSERT, UPDATE, and DELETE events.
We will need some interface from ROLLBACK inside the storage engine to the 'row event log', so that BEGIN; INSERT; ROLLBACK will also create a DELETE event. Similarly, we will need an interface that allows CASCADE or SET NULL operations from FOREIGN KEY constraints to be relayed to the 'row event log'.
Starting with MariaDB 10.2, there is an optimization that avoids unnecessarily sorting the data by PRIMARY KEY when the sorting does not change. Search for skip_pk_sort. It would be nice if the future MDEV-515 code inside InnoDB could be informed of this, so that it can assume that the data is already sorted by PRIMARY KEY.
If there exist FOREIGN KEY constraints on the being-rebuilt table, then this approach should work just as fine as the current online table rebuild in InnoDB: The constraints would be enforced on the old copy of the table until the very end where we switch the tables, and from that point on, on the new copy of the table.
Initially, we could disable ONLINE...ADD FOREIGN KEY. That could be easier to implement after moving the FOREIGN KEY processing from InnoDB to the SQL layer.
Attachments
Issue Links
- causes
-
MCOL-5603 Online alter can break data consistency
-
- Confirmed
-
-
MDEV-28771 Assertion `table->in_use && tdc->flushed' failed after ALTER
-
- Closed
-
-
MDEV-28816 Assertion `wsrep_thd_is_applying(thd)' failed in int wsrep_ignored_error_code(Log_event*, int)
-
- Closed
-
-
MDEV-28825 Server crash in binlog_online_alter_end_trans
-
- Closed
-
-
MDEV-28930 ALTER TABLE Deadlocks with parallel TL_WRITE
-
- Closed
-
-
MDEV-28943 Online alter fails under LOCK TABLE with ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
-
- Closed
-
-
MDEV-28944 XA assertions failing in binlog_rollback and binlog_commit
-
- Closed
-
-
MDEV-28949 Deadlock between online alter and DML
-
- Closed
-
-
MDEV-28959 Online alter ignores strict table mode
-
- Closed
-
-
MDEV-28967 Assertion `marked_for_write_or_computed()' failed in Field_new_decimal::store_value / online_alter_read_from_binlog
-
- Closed
-
-
MDEV-29013 ER_KEY_NOT_FOUND/lock timeout upon online alter with long unique indexes
-
- Closed
-
-
MDEV-29038 XA assertions failing in binlog_rollback and binlog_commit #2
-
- Closed
-
-
MDEV-29056 Replica SQL thread stops with 1846 error on ALTER ONLINE after LOCK WRITE
-
- Closed
-
-
MDEV-29067 Online alter ignores check constraint violation
-
- Closed
-
-
MDEV-29068 Cascade foreign key updates do not apply in online alter
-
- Closed
-
-
MDEV-29069 ER_KEY_NOT_FOUND upon concurrent online auto-increment addition and DELETE
-
- Closed
-
-
MDEV-29506 Assertion `!table->pos_in_locked_tables' failed in tc_release_table
-
- Closed
-
-
MDEV-30891 Assertion `!table->versioned(VERS_TRX_ID)' failed in Write_rows_log_event::binlog_row_logging_function
-
- Closed
-
-
MDEV-30902 Server crash in LEX::first_lists_tables_same
-
- Closed
-
-
MDEV-30924 Server crashes in MYSQL_LOG::is_open upon ALTER vs FUNCTION
-
- Closed
-
-
MDEV-30925 Assertion `share->now_transactional' failed in translog_write_record / online_alter_read_from_binlog
-
- Closed
-
-
MDEV-30945 RPL tests are failing with MSAN use-of-uninitialized-value in bitmap_intersect
-
- Closed
-
-
MDEV-30949 Direct leak in my_register_filename / binlog_online_alter_end_trans
-
- Closed
-
-
MDEV-30984 Online ALTER table is denied with non-informative error messages
-
- Closed
-
-
MDEV-30987 main.alter_table_online times out with view-protocol
-
- Closed
-
-
MDEV-31033 ER_KEY_NOT_FOUND upon online COPY ALTER on a partitioned table
-
- Closed
-
-
MDEV-31040 Server crashes in MYSQL_LOG::is_open upon ALTER on partitioned table
-
- Closed
-
-
MDEV-31043 ER_KEY_NOT_FOUND upon concurrent ALTER and transaction
-
- Closed
-
-
MDEV-31058 ER_KEY_NOT_FOUND upon concurrent CHANGE column to autoinc and DML
-
- Closed
-
-
MDEV-31059 "Slave SQL" errors upon concurrent DML and erroneous ALTER
-
- Closed
-
-
MDEV-31128 Server crashes in Rows_log_event::find_row upon concurrent DML and ALTER
-
- Closed
-
-
MDEV-31136 Online ALTER is allowed on master but fails on slave with ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
-
- Closed
-
-
MDEV-31172 Server crash or ASAN errors in online_alter_check_autoinc
-
- Closed
-
-
MDEV-31563 Server crashes in MDL_ticket::downgrade_lock
-
- Closed
-
-
MDEV-31601 Some ALTER TABLE .. fail when they worked before, and with a wrong error message
-
- Closed
-
-
MDEV-31624 Online ALTER fails due to intermediate DML which is later overridden
-
- Open
-
-
MDEV-31631 Online ALTER adding auto-increment column to a table with history behaves differently from non-online
-
- Closed
-
-
MDEV-31646 Online alter applies binlog cache limit to cache writes
-
- Closed
-
-
MDEV-31677 Assertion `bitmap_is_set(cols, i)' failed upon online ALTER with binlog_row_image=NOBLOB
-
- Closed
-
-
MDEV-31755 Replica's DML event deadlocks with online alter table
-
- Closed
-
-
MDEV-31775 Server crash in Rows_log_event::update_sequence upon online alter on sequence
-
- Closed
-
-
MDEV-31776 Online ALTER reports the number of affected rows incorrectly
-
- Closed
-
-
MDEV-31777 ER_GET_ERRNO upon online alter with concurrent DML on CONNECT table
-
- Closed
-
-
MDEV-31781 ALTER TABLE ENGINE=s3 fails
-
- Closed
-
-
MDEV-31804 Assertion `thd->m_transaction_psi == __null' fails upon replicating online ALTER
-
- Closed
-
-
MDEV-31812 Add switch to old_mode to disable non-locking ALTER
-
- Closed
-
-
MDEV-31838 Assertion fails in binlog_log_row_online_alter upon parallel replication with two-phase alter and MINIMAL binlog_row_image
-
- Closed
-
-
MDEV-31906 Processlist shows stage and max stage 0 during online alter
-
- Open
-
-
MDEV-32126 Assertion `!writer.checksum_len || writer.remains == 0' fails upon concurrent online ALTER and transactions with failing statements and binary log enabled
-
- Closed
-
-
MDEV-32444 Data from orphaned XA transaction is lost after online alter
-
- Closed
-
-
MDEV-32510 ASAN use-after-poison in online alter with rocksdb under SERIALIZABLE isolation level
-
- Open
-
-
MDEV-33094 row-by-row logging needs to be disabled for the online ALTER log application stage
-
- Open
-
-
MDEV-33330 Server crash or assertion failure in binlog_get_pending_rows_event
-
- Closed
-
- includes
-
MDEV-27986 Galera testing of ALTER ONLINE TABLE
-
- Closed
-
- is blocked by
-
MDEV-30985 Replica stops with error on ALTER ONLINE with Geometry Types
-
- Closed
-
- relates to
-
MDEV-9260 Improve progress report on on-line alter table
-
- Open
-
-
MDEV-12512 Accurately report progress for ALTER TABLE...ALGORITHM=INPLACE
-
- Open
-
-
MDEV-15250 UPSERT during ALTER-TABLE results in 'Duplicate entry' error for alter
-
- Closed
-
-
MDEV-16354 Allow ALGORITHM=INPLACE for ADD COLUMN…PERSISTENT AS
-
- Open
-
-
MDEV-18845 Introduce alter_lock to allow refusing non-online ALTER TABLE
-
- Open
-
-
MDEV-28942 Online alter does not support ORDER BY
-
- Closed
-
-
MDEV-28966 Assertion `row_data' failed in unpack_row / online_alter_read_from_binlog
-
- Closed
-
-
MDEV-29007 Assertion `marked_for_write_or_computed()' failed upon online ADD COLUMN .. FIRST
-
- Closed
-
-
MDEV-30985 Replica stops with error on ALTER ONLINE with Geometry Types
-
- Closed
-
-
MDEV-34768 ASAN errors in Multiupdate_prelocking_strategy::handle_end upon concurrent online ALTER and DML prepare
-
- Open
-
-
MDEV-515 innodb bulk insert
-
- Closed
-
-
MDEV-10453 Allow ONLINE or ALGORITHM=INPLACE with OPTIMIZE TABLE
-
- Open
-
-
MDEV-11675 Lag Free Alter On Slave
-
- Closed
-
-
MDEV-13795 ALTER TABLE…DROP PRIMARY KEY, ADD PRIMARY KEY fails when VIRTUAL columns exist
-
- Closed
-
-
MDEV-14332 Corruption during online table-rebuilding ALTER when VIRTUAL columns exist, causes assertion failure
-
- Closed
-
-
MDEV-15471 Assertion `new_clustered == ctx->need_rebuild()' failed in ha_innobase::commit_inplace_alter_table
-
- Stalled
-
-
MDEV-15641 InnoDB crash while committing table-rebuilding ALTER TABLE
-
- Closed
-
-
MDEV-18127 Handling DuplicateKey error while doing Online DDL
-
- Open
-
-
MDEV-28774 Assertion `table->in_use && tdc->flushed' failed in bool TABLE_SHARE::visit_subgraph(Wait_for_flush*, MDL_wait_for_graph_visitor*)
-
- Closed
-
- split to
-
MDEV-31942 Online alter: support cascade foreign keys
-
- Open
-
- links to