Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16329

Engine-independent online ALTER TABLE

    XMLWordPrintable

Details

    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:

      1. Exclusively acquire the table Metadata Lock (MDL).
      2. Acquire the table lock for read (TL_READ)
      3. Read the first record. In table is empty, online is skipped (goto 11).
      4. Set up (a separate, per-table one) row-based replication for tracking changes from concurrent DMLs ("online changes").
      5. Downgrade the MDL lock.
      6. Copy the table contents (using a non-locking read if supported by the storage engine).
      7. Apply the online changes from the replicated contents.
      8. Unlock the table lock
      9. Exclusively lock the table MDL (upgrade to MDL_SHARED_WRITE).
      10. Apply any remaining online changes.
      11. 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:

      1. Arbitrary changes of column type will be possible, without duplicating any conversion logic.
      2. It will be possible to add virtual columns (materialized or not) together with adding indexes, while allowing concurrent writes (MDEV-13795, MDEV-14332).
      3. 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

          Bug - A problem which impairs or prevents the functions of the product. MCOL-5603 Online alter can break data consistency

          • Critical - Crashes, loss of data, severe memory leak.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28771 Assertion `table->in_use && tdc->flushed' failed after ALTER

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28816 Assertion `wsrep_thd_is_applying(thd)' failed in int wsrep_ignored_error_code(Log_event*, int)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28825 Server crash in binlog_online_alter_end_trans

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28930 ALTER TABLE Deadlocks with parallel TL_WRITE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28943 Online alter fails under LOCK TABLE with ER_ALTER_OPERATION_NOT_SUPPORTED_REASON

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28944 XA assertions failing in binlog_rollback and binlog_commit

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28949 Deadlock between online alter and DML

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28959 Online alter ignores strict table mode

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28967 Assertion `marked_for_write_or_computed()' failed in Field_new_decimal::store_value / online_alter_read_from_binlog

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29013 ER_KEY_NOT_FOUND/lock timeout upon online alter with long unique indexes

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29038 XA assertions failing in binlog_rollback and binlog_commit #2

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29056 Replica SQL thread stops with 1846 error on ALTER ONLINE after LOCK WRITE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29067 Online alter ignores check constraint violation

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29068 Cascade foreign key updates do not apply in online alter

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29069 ER_KEY_NOT_FOUND upon concurrent online auto-increment addition and DELETE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29506 Assertion `!table->pos_in_locked_tables' failed in tc_release_table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30891 Assertion `!table->versioned(VERS_TRX_ID)' failed in Write_rows_log_event::binlog_row_logging_function

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30902 Server crash in LEX::first_lists_tables_same

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30924 Server crashes in MYSQL_LOG::is_open upon ALTER vs FUNCTION

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30925 Assertion `share->now_transactional' failed in translog_write_record / online_alter_read_from_binlog

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30945 RPL tests are failing with MSAN use-of-uninitialized-value in bitmap_intersect

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30949 Direct leak in my_register_filename / binlog_online_alter_end_trans

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30984 Online ALTER table is denied with non-informative error messages

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30987 main.alter_table_online times out with view-protocol

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31033 ER_KEY_NOT_FOUND upon online COPY ALTER on a partitioned table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31040 Server crashes in MYSQL_LOG::is_open upon ALTER on partitioned table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31043 ER_KEY_NOT_FOUND upon concurrent ALTER and transaction

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31058 ER_KEY_NOT_FOUND upon concurrent CHANGE column to autoinc and DML

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31059 "Slave SQL" errors upon concurrent DML and erroneous ALTER

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31128 Server crashes in Rows_log_event::find_row upon concurrent DML and ALTER

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31136 Online ALTER is allowed on master but fails on slave with ER_ALTER_OPERATION_NOT_SUPPORTED_REASON

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31172 Server crash or ASAN errors in online_alter_check_autoinc

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31563 Server crashes in MDL_ticket::downgrade_lock

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31601 Some ALTER TABLE .. fail when they worked before, and with a wrong error message

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31624 Online ALTER fails due to intermediate DML which is later overridden

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31631 Online ALTER adding auto-increment column to a table with history behaves differently from non-online

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31646 Online alter applies binlog cache limit to cache writes

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31677 Assertion `bitmap_is_set(cols, i)' failed upon online ALTER with binlog_row_image=NOBLOB

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31755 Replica's DML event deadlocks with online alter table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31775 Server crash in Rows_log_event::update_sequence upon online alter on sequence

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31776 Online ALTER reports the number of affected rows incorrectly

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31777 ER_GET_ERRNO upon online alter with concurrent DML on CONNECT table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31781 ALTER TABLE ENGINE=s3 fails

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31804 Assertion `thd->m_transaction_psi == __null' fails upon replicating online ALTER

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-31812 Add switch to old_mode to disable non-locking ALTER

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31838 Assertion fails in binlog_log_row_online_alter upon parallel replication with two-phase alter and MINIMAL binlog_row_image

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31906 Processlist shows stage and max stage 0 during online alter

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32126 Assertion `!writer.checksum_len || writer.remains == 0' fails upon concurrent online ALTER and transactions with failing statements and binary log enabled

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32444 Data from orphaned XA transaction is lost after online alter

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32510 ASAN use-after-poison in online alter with rocksdb under SERIALIZABLE isolation level

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33094 row-by-row logging needs to be disabled for the online ALTER log application stage

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33330 Server crash or assertion failure in binlog_get_pending_rows_event

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          includes

          Task - A task that needs to be done. MDEV-27986 Galera testing of ALTER ONLINE TABLE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30985 Replica stops with error on ALTER ONLINE with Geometry Types

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-9260 Improve progress report on on-line alter table

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-12512 Accurately report progress for ALTER TABLE...ALGORITHM=INPLACE

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15250 UPSERT during ALTER-TABLE results in 'Duplicate entry' error for alter

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-16354 Allow ALGORITHM=INPLACE for ADD COLUMN…PERSISTENT AS

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-18845 Introduce alter_lock to allow refusing non-online ALTER TABLE

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28942 Online alter does not support ORDER BY

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28966 Assertion `row_data' failed in unpack_row / online_alter_read_from_binlog

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29007 Assertion `marked_for_write_or_computed()' failed upon online ADD COLUMN .. FIRST

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30985 Replica stops with error on ALTER ONLINE with Geometry Types

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34768 ASAN errors in Multiupdate_prelocking_strategy::handle_end upon concurrent online ALTER and DML prepare

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Task - A task that needs to be done. MDEV-515 innodb bulk insert

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-10453 Allow ONLINE or ALGORITHM=INPLACE with OPTIMIZE TABLE

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-11675 Lag Free Alter On Slave

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13795 ALTER TABLE…DROP PRIMARY KEY, ADD PRIMARY KEY fails when VIRTUAL columns exist

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14332 Corruption during online table-rebuilding ALTER when VIRTUAL columns exist, causes assertion failure

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15471 Assertion `new_clustered == ctx->need_rebuild()' failed in ha_innobase::commit_inplace_alter_table

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15641 InnoDB crash while committing table-rebuilding ALTER TABLE

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-18127 Handling DuplicateKey error while doing Online DDL

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28774 Assertion `table->in_use && tdc->flushed' failed in bool TABLE_SHARE::visit_subgraph(Wait_for_flush*, MDL_wait_for_graph_visitor*)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          split to

          Task - A task that needs to be done. MDEV-31942 Online alter: support cascade foreign keys

          • Major - Major loss of function.
          • Open
          links to

          Web Link Bug #77097 InnoDB Online DDL should support change data type

          Web Link Bug #98600 Optimize table fails with duplicate entry on UNIQUE KEY

          mentioned in

          Page Loading...

          Page Loading...

          Activity

            People

              nikitamalyavin Nikita Malyavin
              marko Marko Mäkelä
              Votes:
              10 Vote for this issue
              Watchers:
              28 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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