ALTER TABLE and Online DDL

ALTER TABLE adds, drops, renames and modifies columns, indexes, constraints and options, several per statement. ALGORITHM decides how: INSTANT edits only the data dictionary (adding, dropping or renaming a column, or changing a default, since 8.0.29); INPLACE works inside the tablespace while other sessions keep writing (LOCK=NONE); COPY rebuilds the table and blocks writes. Name the one you expect, so the statement fails instead of silently copying a big table:

The same change made instantly and by copying, and one INSTANT cannot makeSQL

CREATE TABLE order_events (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id INT UNSIGNED NOT NULL, event VARCHAR(20) NOT NULL, amount DECIMAL(8,2) NOT NULL);
SET SESSION cte_max_recursion_depth = 2000000;
INSERT INTO order_events (order_id, event, amount)
  WITH RECURSIVE n (i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 2000000)
  SELECT i % 9 + 1, ELT(i % 3 + 1, 'created', 'paid', 'shipped'), i % 500 / 10 FROM n;
ALTER TABLE order_events
  ADD COLUMN channel VARCHAR(10) NOT NULL DEFAULT 'web', ALGORITHM=INSTANT;
SELECT TABLE_ID, TOTAL_ROW_VERSIONS FROM information_schema.INNODB_TABLES
 WHERE NAME = 'shop/order_events';
ALTER TABLE order_events ADD COLUMN source VARCHAR(10) NOT NULL DEFAULT 'web', ALGORITHM=COPY;
SELECT TABLE_ID, TOTAL_ROW_VERSIONS FROM information_schema.INNODB_TABLES
 WHERE NAME = 'shop/order_events';
ALTER TABLE order_events MODIFY amount DECIMAL(10,2) NOT NULL, ALGORITHM=INSTANT;
ALTER TABLE order_events ADD INDEX idx_order (order_id), ALGORITHM=INPLACE, LOCK=NONE;

Output

Query OK, 0 rows affected (0.030 sec)
+----------+--------------------+
| TABLE_ID | TOTAL_ROW_VERSIONS |
+----------+--------------------+
|     1406 |                  1 |
+----------+--------------------+
Query OK, 2000000 rows affected (8.903 sec)
+----------+--------------------+
| TABLE_ID | TOTAL_ROW_VERSIONS |
+----------+--------------------+
|     1421 |                  0 |
+----------+--------------------+
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to
  change column type. Try ALGORITHM=COPY/INPLACE.
Query OK, 0 rows affected (3.407 sec)

The instant add took 0.03 seconds and rewrote 0 rows: InnoDB recorded a new row version, and reading an old row supplies 'web' from the dictionary. By COPY the same change took 8.9 seconds and built a new table (new TABLE_ID, versions reset). Since MySQL 9.1 524 a table allows 255 instant versions (64 before); then error 4092 demands a rebuild such as OPTIMIZE TABLE. The index took 3.4 seconds in place, with LOCK=NONE leaving the table open to writes.

Even an instant change takes a brief exclusive metadata lock, which one long transaction can stall, queueing every later query (Table and Metadata Locks). For rebuilds of busy tables, gh-ost 13,584 (https://github.com/github/gh-ost 13,584 ) and pt-online-schema-change from Percona Toolkit 1,555 (https://github.com/percona/percona-toolkit 1,555 ) copy in small chunks and swap at the end.

MENU

ALTER TABLE and Online DDL