Online Schema Change Strategies, Native DDL Capabilities, and External Tool Concepts

Learning outcomes

ServiceHub needs schema changes on tables that cannot be blocked casually. MySQL can perform many InnoDB DDL operations instantly or in place, but “online” does not mean “no locks,” and support varies by operation and release. The safest strategy is to evaluate native capabilities first, then add external online-schema-change machinery only when a measured requirement justifies the extra complexity.

Native DDL first: ask what this exact operation supports

INSTANT modifies metadata only for supported operations. INPLACE avoids the server-layer table-copy algorithm but can still rebuild storage depending on the operation. COPY constructs a new representation and copies rows. These are MySQL-specific operational choices, not portable SQL concepts.

USE servicehub_change_lab;SHOW CREATE TABLE work_orders\GSHOW VARIABLES LIKE 'old_alter_table';ALTER TABLE work_orders  ADD COLUMN dispatch_note VARCHAR(200) NULL,  ALGORITHM=INSTANT;ALTER TABLE work_orders  ADD INDEX ix_status_opened(status,opened_at),  ALGORITHM=INPLACE, LOCK=NONE;SHOW INDEX FROM work_orders;

Explicit clauses turn a hidden fallback into a visible deployment decision. If an operation cannot meet LOCK=NONE or the requested algorithm, failure during preflight is safer than silently accepting a more disruptive path.

Online DDL can still wait for metadata locks

A metadata lock (MDL) protects a table definition while sessions use it. Even an instant or in-place operation can need an exclusive metadata lock during key phases. A long transaction that touched the table can therefore block a seemingly fast DDL, and the pending DDL can cause later work to queue behind it.

USE servicehub_change_lab;START TRANSACTION;SELECT * FROM work_orders WHERE work_order_id=1001;-- Leave the transaction open temporarily.

USE servicehub_change_lab;ALTER TABLE work_orders  ADD COLUMN routing_tag VARCHAR(32) NULL,  ALGORITHM=INSTANT;-- Observe whether final metadata-lock acquisition waits.

SELECT OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_DURATION,       LOCK_STATUS,OWNER_THREAD_IDFROM performance_schema.metadata_locksWHERE OBJECT_SCHEMA='servicehub_change_lab'  AND OBJECT_NAME='work_orders';SHOW FULL PROCESSLIST;-- COMMIT Session A, then verify Session B finishes.

The lesson is not that instant DDL is dangerous; it is that transaction hygiene and metadata-lock visibility belong in the cutover plan. A deployment gate should detect long-running transactions before the DDL begins.

Shadow-table tools trade blocking time for operational machinery

External online-schema-change tools commonly create a shadow table with the target definition, copy rows in chunks, capture concurrent changes, verify the copy, then perform a short rename/cutover. This can be valuable for a copy-required change on a very large table, but it introduces disk, replication, trigger/change-capture, and cutover concerns.

Native online DDL should not be rejected simply because an external tool exists. Conversely, “native” does not make a table-copy operation acceptable on a busy multi-terabyte table. Choose from measured constraints.

Compatibility failure: request an algorithm that cannot satisfy the change

-- Representative staging copy only.ALTER TABLE work_orders  MODIFY summary TEXT NOT NULL,  ALGORITHM=INSTANT;-- If unsupported as INSTANT, expect MySQL to reject it.-- Do not remove ALGORITHM just to make the command run.-- First evaluate INPLACE/COPY behavior, locking, disk, duration,-- binary-log/replica impact, and a staged expand/contract alternative.

The correction is not always “use COPY.” It may be a staged new column, dual-write, backfill, validation, and later contract. The schema design and release process can avoid a disruptive rewrite entirely.

Representative preflight and cleanup

Benchmark a realistic staging copy using representative row count, width, indexes, write concurrency, and available disk. Record duration as a local observation only. Also record MDL waits, application p95/p99, free space, redo/binlog growth, and replica lag when applicable.

SHOW CREATE TABLE work_orders\GSELECT COUNT(*) AS rows_after_ddl FROM work_orders;SELECT COUNT(*) AS missing_public_referenceFROM work_orders WHERE public_reference IS NULL;ALTER TABLE work_orders DROP INDEX ix_status_opened;ALTER TABLE work_orders  DROP COLUMN dispatch_note,  DROP COLUMN routing_tag;

DDL has phases, and each phase consumes a different resource

Schema change planning improves when you separate metadata work, data/index build work, and final definition publication. An instant operation mostly changes metadata, so its dominant risk can be waiting for the exclusive metadata lock needed to publish the new definition. An in-place index build can permit concurrent DML while consuming CPU, storage bandwidth, temporary space, buffer-pool capacity, and redo. A copy algorithm adds a full table-copy lifecycle and typically has much larger disk and I/O requirements. The word “online” only describes permitted concurrency; it does not guarantee low resource usage or short completion time.

For a representative test, capture free disk before and after, the operation duration, application latency percentiles, metadata-lock waits, and—if replication exists—receiver/applier lag. If the test table is a tiny fraction of production size, do not extrapolate linearly. Index build and table-copy behavior can be affected by cache state, data distribution, concurrent writes, device throughput, and the number/width of indexes.

External online-schema-change tools add correctness obligations

A shadow-table tool must keep the shadow logically synchronized with the source while rows are copied. Trigger-based approaches add write-path work and can conflict with existing trigger policies. Chunking requires a stable traversal key and must avoid starving application traffic. Cutover usually depends on a short metadata-lock window, so a long transaction can still prevent the final rename. Replicas may receive both the copy workload and change-capture activity, creating lag even though the source application remains responsive.

Before approving such a tool, write a decision record that names: the native DDL limitation being avoided, expected additional disk headroom, how concurrent changes are captured, how row counts/checksums are compared, how triggers are handled, how replica lag is bounded, what happens if cutover times out, and when the old table can be removed. “The tool is online” is not sufficient justification.

Cutover failure drill: timeout is safer than indefinite waiting

In a zero/low-downtime change, final cutover should have an explicit time budget. If the required metadata lock cannot be acquired within the approved window, abort or postpone rather than letting deployment queue application work indefinitely. MySQL exposes waiting metadata locks through Performance Schema and process state, which gives the operator a concrete reason to stop the cutover and identify the blocking transaction.

SELECT OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_STATUS,OWNER_THREAD_IDFROM performance_schema.metadata_locksWHERE OBJECT_SCHEMA='servicehub_change_lab';SELECT THREAD_ID,PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_TIME,PROCESSLIST_STATEFROM performance_schema.threadsWHERE PROCESSLIST_ID IS NOT NULLORDER BY PROCESSLIST_TIME DESC;

Do not kill the oldest session automatically. Determine ownership and transaction purpose first. A legitimate long-running business transaction can be more important than the schema change. Production judgment means being able to postpone the migration.

Preflight acceptance criteria

Before production, prove the requested ALGORITHM/LOCK contract on the exact server family, test with realistic concurrency, verify required free space, and define abort thresholds for metadata-lock wait, application error rate, replica lag, and disk consumption. For shadow-table strategies, add checksum/row-count acceptance and a rollback/cutover rehearsal. Keep all timing numbers labeled as environment-specific observations.

Verify metadata and stored rows, not only the ALTER result

After any DDL experiment, confirm that the data dictionary and rows match the intended state. SHOW CREATE TABLE is the clearest reconstruction of the table definition; INFORMATION_SCHEMA.COLUMNS provides queryable metadata for automated gates; and a deterministic row query proves the change did not unexpectedly transform existing business values.

SELECT COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,ORDINAL_POSITIONFROM information_schema.COLUMNSWHERE TABLE_SCHEMA='servicehub_change_lab'  AND TABLE_NAME='work_orders'ORDER BY ORDINAL_POSITION;SELECT work_order_id,status,summary,public_referenceFROM servicehub_change_lab.work_ordersORDER BY work_order_id;SHOW WARNINGS;

Expected business values are the same work orders established in Lesson 1. Metadata evidence proves what definition MySQL accepted; row evidence proves only the checked rows, so production canaries should also include representative invariants and counts.

Authoritative references

• MySQL 8.4 — Upgrading MySQL
• MySQL 8.4 — Upgrade Paths
• MySQL 8.4 — Upgrade Best Practices
• MySQL Shell 8.4 — Upgrade Checker Utility
• MySQL 8.4 — InnoDB and Online DDL
• MySQL 8.4 — Upgrading Group Replication
• MySQL 8.4 — Downgrading MySQL
• MySQL 8.4 — Native Authentication Plugin
• MySQL Community Server 8.4 Downloads
• MySQL Shell 8.4 Downloads