Online Schema Change for Tables with Triggers: pt-osc and gh-ost Workarounds

An existing trigger changes the online-schema-change decision. MySQL 8.4 native DDL can often alter the table without replacing it through an external shadow-table tool. pt-online-schema-change normally creates its own triggers, but current Percona Toolkit provides a constrained --preserve-triggers path for supported MySQL releases. gh-ost is triggerless internally, yet its current official limitations say tables with triggers are not supported; foreign keys are not supported either. The safe plan begins by proving native DDL eligibility and never treats an unsupported tool combination as a workaround.

Use a capability matrix, not a favorite tool

Start with native ALTER TABLE. MySQL's ALGORITHM=INSTANT changes only data-dictionary metadata for supported operations. ALGORITHM=INPLACE avoids the server's table-copy algorithm for many operations, though it can rebuild a table and consume substantial resources. LOCK=NONE requests concurrent reads and writes; if the operation cannot honor it, MySQL fails instead of silently accepting a more restrictive lock. Support varies by exact operation, row format, storage engine, and MySQL release.

Inventory the complete dependency surface

Capture more than trigger names. A trigger definition includes event, timing, order, body, definer, SQL mode, client character set, connection collation, and database collation. Trigger names are unique within a schema. MySQL allows several triggers with the same timing and event and records their PRECEDES or FOLLOWS order. A migration that recreates equivalent SQL under a missing definer or different SQL mode can behave differently.

SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION,
       ACTION_TIMING, ACTION_ORDER, ACTION_STATEMENT, DEFINER,
       SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION
FROM information_schema.TRIGGERS
WHERE EVENT_OBJECT_SCHEMA = 'app'
  AND EVENT_OBJECT_TABLE = 'orders'
ORDER BY ACTION_TIMING, EVENT_MANIPULATION, ACTION_ORDER;

SHOW CREATE TRIGGER app.orders_audit_after_update;

Store the exact SHOW CREATE TRIGGER output in the reviewed change record and verify every definer account exists on the target. Identify trigger side effects: audit rows, summary updates, queue inserts, calls to stored routines, or changes in other tables. Test whether replaying copied rows would create duplicate side effects; never enable application triggers on a shadow table during bulk copy unless the tool's documented algorithm owns that timing.

Inventory foreign keys both on the table and from child tables, including self-references. Record table size, primary and unique keys, partitions, generated columns, full-text or spatial indexes, replicas and filters, binary-log format and row image, disk headroom, transaction duration, write rate, backup and point-in-time recovery state, and managed-service restrictions. Verify the migration account's privileges rather than granting a permanent broad administrator.

Prove native DDL eligibility first

Write the exact ALTER and force the preferred algorithm. For a supported instant column addition, an explicit algorithm prevents an unexpected fallback:

ALTER TABLE app.orders
  ADD COLUMN source varchar(32) NULL,
  ALGORITHM=INSTANT;

For an index operation expected to support online in-place execution, request both properties:

ALTER TABLE app.orders
  ADD INDEX orders_status_created_idx (status, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

Run the statement against the same MySQL 8.4 patch release, schema, row format, and production-like data in staging. An accepted statement on an empty development table does not establish production duration, disk use, or final lock time. Some online operations maintain a temporary modification log while DML continues; the operation can fail if that log exceeds innodb_online_alter_log_max_size. Concurrent writes that satisfy the old definition but violate the new one can also make the ALTER fail late.

Existing triggers do not remove metadata locking. Native online DDL may wait for transactions holding metadata locks at the beginning and needs an exclusive metadata lock to finalize the definition. Find long transactions, idle-in-transaction application behavior, explicit table locks, backup work, and queued DDL before the change. A waiting ALTER can itself become the head of a queue and block later traffic.

SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
       LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'app'
  AND OBJECT_NAME = 'orders';

Use a short, reviewed session lock_wait_timeout so the DDL gives up rather than waits unbounded, and preserve the error for diagnosis. Do not set a risky global timeout for every session. Monitor DDL-stage progress only through tables and columns verified on the deployed version; avoid copied queries containing nonexistent date_started or progress fields.

Use pt-online-schema-change only through its documented trigger path

pt-online-schema-change creates a new table, applies the ALTER, copies rows in chunks, and uses triggers on the original table to propagate concurrent inserts, updates, and deletes. It normally refuses a table that already has triggers. On MySQL 5.7.2 and later, which permits multiple triggers for the same event and timing, --preserve-triggers tells the tool to test and preserve existing triggers.

The option is not magic. Percona documents that the existing triggers are copied to the new table for compatibility testing, removed during row copy, and reapplied for cutover. A trigger that references a column being dropped or changed incompatibly cannot be preserved. Existing trigger names and ordering must remain valid. --preserve-triggers cannot be combined with --no-drop-triggers, --no-drop-old-table, or --no-swap-tables in the normal documented workflow because trigger names must be removed and recreated. That means keeping the old table is not a simple rollback switch for this case.

Build the command from the current toolkit version's own --help and documentation. Use placeholders for workload-specific thresholds instead of copying universal numbers:

pt-online-schema-change \
  --alter 'ADD COLUMN source VARCHAR(32) NULL' \
  --preserve-triggers \
  --alter-foreign-keys-method=auto \
  --max-load 'Threads_running=<reviewed-pause-threshold>' \
  --critical-load 'Threads_running=<reviewed-abort-threshold>' \
  --max-lag '<reviewed-seconds>' \
  --chunk-time '<reviewed-seconds>' \
  --dry-run D=app,t=orders

--dry-run creates and alters the new table but does not create triggers, copy data, or swap tables. It is necessary, but it cannot prove live trigger interaction or cutover. Run an --execute rehearsal on a disposable production-like fixture with all original triggers, definers, foreign keys, replicas, and concurrent write patterns. Review --print output and exact tool version. Supply credentials through a protected option file or secret mechanism, require TLS where remote, and keep them out of process listings and logs.

Set pause and critical-load values from measured capacity. Configure replica discovery and lag checks for the actual topology; a filtered or hidden replica may be missed. Verify the table has a usable primary or unique key and that the ALTER preserves the migration key. Changing or dropping that key requires special scrutiny because copy and delete triggers depend on it.

Treat foreign keys as a separate risk decision

Foreign keys that point to the altered table do not follow an atomic rename to the replacement in the simple way an application expects. pt-osc therefore requires an explicit --alter-foreign-keys-method. The preferred rebuild_constraints path drops and recreates referencing constraints so they target the new table, but child-table ALTER work can be large or blocking. The drop_swap path has a period where the original name does not exist and has a more dangerous failure boundary. auto chooses according to the tool's estimate; it does not transfer the decision's risk to the tool.

Enumerate every child, estimate its ALTER behavior, test constraint names after migration, and run referential checks. Percona documents that constraint names can gain leading underscores because of MySQL name collisions. Self-referencing foreign keys are not fully supported and should send the plan back to native DDL, schema redesign, or a controlled maintenance window. Never use a dangling-reference query as a substitute for a supported constraint migration.

Do not use gh-ost on the unsupported case

gh-ost streams row-based binary-log events rather than adding copy triggers. This makes it pausible and lets operators postpone cutover on supported tables. It does not make a table's existing triggers supported. The project's current requirements and limitations explicitly list both triggers and foreign-key constraints as unsupported. The two original and ghost tables also need a shared primary or qualifying unique key.

Do not drop business triggers or foreign keys simply to make gh-ost start unless a separately reviewed application migration truly removes those invariants. Do not change binlog_format with --switch-to-rbr ad hoc; replication behavior and rollback need topology-level review. gh-ost connects to and writes on the primary during a production migration even when it reads changes through a replica or binary-log stream, so saying it never touches the primary is incorrect. For supported tables, use its noop, replica test, throttle, postpone, socket, panic, and cutover controls from the current project documentation. For the trigger/FK scenario in this guide, choose native DDL or a validated pt-osc path instead.

Validate trigger behavior, not just row counts

Create canary operations for every trigger timing and event: insert, update of relevant and irrelevant columns, delete, and any ordering interaction with another trigger. Verify the primary table, shadow or replacement table, audit and summary tables, stored-routine output, binary log, and replica. Include multi-row statements, rollback, duplicate-key failure, null values, concurrent updates, and an application retry. A row-count match can coexist with duplicate audit entries or missed side effects.

1. Before execution: capture schema, triggers, definers, order, foreign keys, grants, checksums or reconciliation queries, query plans, backup evidence, replica state, and abort conditions.
2. During copy: watch user latency and errors, tool status, chunk rate, load checks, replica lag, disk, redo and binary-log growth, trigger side-effect canaries, and metadata-lock queues.
3. Before cutover: stop incompatible DDL, clear long transactions, confirm all triggers can be recreated, verify foreign-key method completion estimates, and require an authorized operator.
4. After cutover: compare SHOW CREATE TABLE and SHOW CREATE TRIGGER, trigger order and definers, constraints and indexes, row counts and sampled hashes, application reads and writes, replicas, and downstream audit effects.

Define pause, abort, and rollback truthfully

Before cutover, a shadow-table tool can normally be paused or aborted and its artifacts inspected or removed through the tool's documented procedure. Do not improvise deletion while a process may still own them. At cutover, metadata locking and rename steps create a new recovery boundary. With pt-osc --preserve-triggers, the incompatible keep-old-table options mean the original is normally dropped after a successful swap. Rollback may therefore be a forward schema correction, a separately tested reverse migration, or point-in-time recovery—not an instant rename.

Record the last reversible checkpoint, the operator who can authorize cutover, how writes are fenced if recovery is needed, and the maximum time before abort. Keep verified backup and binary-log retention beyond the change window. If trigger correctness cannot be demonstrated, select a maintenance window and native copy ALTER rather than claiming a zero-downtime workaround. The broader MySQL online schema change guide covers pt-osc and gh-ost for tables without this specialist constraint.

Official primary documentation

• MySQL 8.4 online DDL operation matrix
• Online DDL concurrency and performance behavior
• MySQL online DDL failure conditions
• MySQL metadata locking
• Trigger definition, order, and privileges
• pt-online-schema-change behavior and options
• Official gh-ost project and operating model
• gh-ost requirements and trigger/FK limitations