CONCEPT Cited by 1 source

InnoDB internal operations table¶

Definition¶

An internal operations table is a PlanetScale-fork-specific MySQL / InnoDB primitive that marks specific tables as foreign-key-exempt: InnoDB skips all FK validation and cascading for those tables, "as if we SET FOREIGN_KEY_CHECKS=0 for specific tables, even while the transaction otherwise applies foreign key logic to other tables." (Source: sources/2026-04-21-planetscale-the-challenges-of-supporting-foreign-key-constraints).

The primitive ships in PlanetScale's MySQL fork (github.com/planetscale/mysql-server), not in upstream MySQL. Vitess marks shadow tables created by Online DDL as internal-operations tables so their presence doesn't interfere with the live parent/child FK graph.

Why this is necessary¶

The default shadow- table online-DDL mechanism is fundamentally incompatible with FK-carrying tables without this primitive. Four concrete problems, per the source post:

1. Shadow tables of FK children affect their parent¶

If a child table c has FOREIGN KEY ... REFERENCES p (...) and we build a shadow c_shadow with the same FK (on the same parent p), InnoDB now treats both tables as FK children of p. A DELETE FROM p can be rejected because of rows in c_shadow — even though c_shadow is a migration artefact and may contain stale or incomplete rows still being backfilled. Application queries see unexpected FK rejections on the parent.

2. Shadow tables during backfill are always inconsistent¶

The shadow table is inherently out of sync with the production table during the copy + catch-up phase. Rows that have been deleted from the production child may still exist in the shadow, because the delete event hasn't been replayed yet. InnoDB doesn't know the shadow is "inherently stale" — it applies FK logic to its rows like any other. Application writes therefore conflict against rows that shouldn't be FK-constraining in the first place.

3. Cutover leaves the post-swap stale table blocking the parent¶

After swap, the old production table becomes <name>_old but retains its FK constraint — still blocking parent-side writes until dropped.

4. `SET FOREIGN_KEY_CHECKS=0` is too coarse¶

We want to disable FK validation for specific tables (the shadow / migration artefacts), not for the whole transaction. FOREIGN_KEY_CHECKS=0 is a session-level switch; applications running production traffic alongside the migration can't disable it.

The fix¶

PlanetScale's MySQL fork introduces a table-level marker. The post describes it:

"Essentially, we tell MySQL: ignore any foreign key checks for these special tables. Do not try and validate row data. Do not attempt to cascade anything. It's as if we SET FOREIGN_KEY_CHECKS=0 for specific tables, even while the transaction otherwise applies foreign key logic to other tables."

Effect: shadow tables become invisible to the live FK graph during the migration. Parent-side deletes + updates proceed normally; the shadow table's potentially-inconsistent rows don't block them. At cutover, the swap happens with no FK side-effects. Post-cutover, the old table keeps the internal-operations marker until cleanup, so it doesn't block the new parent-child traffic either.

Composition with `rename_table_preserve_foreign_key`¶

The internal-operations-table primitive and rename_table_preserve_foreign_key are both necessary for shadow-table Online DDL on FK-carrying tables. They solve complementary problems:

rename_table_preserve_foreign_key — ensures a parent table's rename preserves child FK pointers. Without it, a shadow-table swap breaks every child's FK reference.
Internal operations tables — ensures a child table's shadow doesn't interfere with the live parent during backfill, and doesn't block the parent after swap.

Together they bound the shadow-table surface so InnoDB's FK machinery sees only the production tables, and only at the right times.

Seen in¶

sources/2026-04-21-planetscale-the-challenges-of-supporting-foreign-key-constraints — canonical wiki home. Noach + Gupta introduce the primitive in the context of "Alas, in MySQL, foreign key constraint definitions are created in the scope of a child table. To add a foreign key constraint, or to remove a foreign key constraint, is a full table rebuild operation, locking and blocking; the very thing Online DDL was designed to overcome." The internal-operations-table primitive side-steps this by making the constraint selectively-enforced rather than forcing a full rebuild.