Automating DDL Pipelines with Python: A Control Loop for Sharded Online DDL

A schema change on a sharded keyspace is not a command you run once and walk away from — it is a long-lived, restartable process that submits work to every shard, watches each of them advance independently, holds them all at a barrier, and only then releases a single coordinated cutover. Doing that by hand across dozens of primaries is error-prone the first time and unrepeatable the tenth. This page builds the automation that replaces the operator: a Python control loop that submits a migration, persists its identity durably, polls per-shard state, enforces the global cutover barrier, and survives its own crash without corrupting the fleet. It is written for platform engineers and MySQL SREs who already run Vitess Online DDL manually and now need it to be a reliable, unattended pipeline. The coordination model this loop implements is defined in Online DDL Orchestration & Migration Coordination; this page is the executable form of it.

Prerequisites #

Before the control loop is safe to run against a production keyspace, confirm the following:

• Vitess 19 or newer for the OnlineDDL command surface used here (show, complete, --json output) and for stable --postpone-completion semantics. The vitess --ddl-strategy is the managed VReplication-based engine the loop drives.
• A control-plane path from wherever the controller runs: vtctldclient on PATH and pointed at the topology server, or network reach to a VTGate on the keyspace’s MySQL port. The examples use both — vtctldclient for submission and completion, VTGate DB-API for state reads.
• Assumed knowledge: how a single logical ALTER fans out to every shard through the VTGate routing layer, how the VSchema maps logical tables to physical shards, and what the per-shard migration states mean — the loop branches on them, and their meanings are catalogued in coordinating multi-shard schema migrations.
• A durable store the controller owns — a small table, a row in an operations database, or a KV entry — to persist the migration UUID and its target. Standard output is not a store; a controller that only prints the UUID and then crashes has lost the migration.
• Python 3.9+ with a MySQL driver (PyMySQL in these examples) for the VTGate reads; subprocess from the standard library drives vtctldclient.

The Control Loop, End to End #

The automation is a small state machine wrapped around Vitess’s own migration state machine. It has exactly six responsibilities, and every one of them must be idempotent because the controller can die and be restarted at any point: submit the change once, persist its UUID before doing anything else, poll every shard until they agree, hold at the barrier until all shards are caught up, fire one fleet-wide complete, then verify the result. The single edge that makes the whole thing production-grade is the retry edge — when the controller restarts, it must re-enter the loop without re-submitting the migration it already launched.

The control-plane surface the loop drives is the same one described for topology inspection in the architecture reference — vtctldclient and the vtadmin API for mutations, the VTGate MySQL wire for reads — never the two confused. Submitting through VTGate’s SQL port and reading state through it are convenient; issuing complete and cleanup goes through vtctldclient so the action is scriptable, auditable, and independent of any one VTGate instance’s health.

Core Mechanism: Identity, State, and the Barrier #

Three invariants make the loop correct, and every failure mode later on this page is a violation of one of them.

Identity is the migration UUID, and it must be persisted before it is used. When you submit with the vitess strategy, Vitess assigns one migration_uuid and fans the job to every shard; that UUID is the only handle to the migration. If the controller submits and then crashes before writing the UUID down, the migration is running on the fleet but the controller no longer knows its name — a lost UUID. The fix is ordering: persist first, act second. Because a controller may also crash between submit and persist, the loop additionally needs a way to recover the UUID it already launched, which is what a deterministic --migration-context provides — a caller-supplied label that lets a restarted controller look up its own in-flight migration instead of starting a second one.

State is per-shard, and the fleet’s state is the aggregate. There is no single “the migration is done” bit. Each shard advances through queued → ready → running → complete on its own clock, and the migration is only as far along as its least-advanced shard. The loop therefore never reads one shard; it reads all of them and reduces them to a single verdict — any failed/cancelled aborts, all complete releases, anything else means keep polling. The full status vocabulary and the valid transitions between states are laid out in decoding Online DDL migration states.

The barrier separates “caught up” from “cut over.” Submitting with --postpone-completion makes every shard copy its rows and then hold, caught up but not swapped. Cutover is a second, explicit action. This decoupling is the entire point of the barrier: it guarantees that no shard serves the new schema until every shard is ready to, so the keyspace never spends a window serving two table shapes at once. The mechanics of holding and releasing that barrier fleet-wide are the subject of enforcing a global cutover barrier.

Step-by-Step Implementation #

1. Submit the migration with the barrier held #

Submission goes through vtctldclient ApplySchema so it is scriptable and returns the UUID on standard output. Pass --ddl-strategy with both --postpone-completion (hold the barrier) and --singleton (reject a second concurrent migration on the same table), and a deterministic --migration-context derived from the change itself so a retry is recognizable:

import subprocess
import hashlib

def migration_context(keyspace: str, ddl: str) -> str:
    """Deterministic context: the same change yields the same label, so a
    restarted controller can find the migration it already submitted."""
    digest = hashlib.sha1(f"{keyspace}::{ddl}".encode()).hexdigest()[:12]
    return f"pipeline-{keyspace}-{digest}"

def submit(keyspace: str, ddl: str) -> str:
    ctx = migration_context(keyspace, ddl)
    proc = subprocess.run(
        ["vtctldclient", "ApplySchema",
         "--ddl-strategy", "vitess --postpone-completion --singleton",
         "--migration-context", ctx,
         "--sql", ddl,
         keyspace],
        capture_output=True, text=True, check=True, timeout=120,
    )
    # ApplySchema prints the migration UUID; it is the only handle to the change.
    uuid = proc.stdout.strip().splitlines()[-1].strip()
    if not uuid:
        raise RuntimeError("ApplySchema returned no migration UUID")
    return uuid

2. Persist the UUID before doing anything else #

The write to the durable store is the first thing that happens after submit returns — before the first poll, before any logging that could be the last line before a crash. Use the migration context as the idempotency key so a restart updates the same row rather than inserting a duplicate:

import pymysql

def persist_uuid(store: pymysql.Connection, keyspace: str, ddl: str, uuid: str) -> None:
    ctx = migration_context(keyspace, ddl)
    with store.cursor() as cur:
        cur.execute(
            """INSERT INTO ddl_pipeline (context, keyspace_name, ddl, uuid, phase)
               VALUES (%s, %s, %s, %s, 'submitted')
               ON DUPLICATE KEY UPDATE uuid = VALUES(uuid), phase = 'submitted'""",
            (ctx, keyspace, ddl, uuid),
        )
    store.commit()

def load_uuid(store: pymysql.Connection, keyspace: str, ddl: str) -> str | None:
    ctx = migration_context(keyspace, ddl)
    with store.cursor() as cur:
        cur.execute("SELECT uuid FROM ddl_pipeline WHERE context = %s", (ctx,))
        row = cur.fetchone()
    return row[0] if row else None

The ddl_pipeline table has context as a unique key. On restart the controller calls load_uuid first; if it returns a UUID, the migration is already in flight and the controller skips submission entirely and rejoins at the poll step. That single lookup is the retry edge from the diagram.

3. Poll per-shard state until the fleet agrees #

Read state through vtctldclient OnlineDDL show <keyspace> <uuid> --json, parse it, and reduce every shard’s status to one verdict. The robust, timeout-and-backoff version of this poller — handling non-JSON output, partial shard reporting, and transient errors — is built out in polling migration status with vtctldclient; the essential shape is:

import json
import time

TERMINAL_FAIL = {"failed", "cancelled"}
CAUGHT_UP = {"complete"}   # under --postpone-completion, "complete" == caught up, holding

def shard_statuses(keyspace: str, uuid: str) -> dict[str, str]:
    proc = subprocess.run(
        ["vtctldclient", "OnlineDDL", "show", keyspace, uuid, "--json"],
        capture_output=True, text=True, check=True, timeout=30,
    )
    rows = json.loads(proc.stdout or "[]")
    return {r["shard"]: r["migration_status"] for r in rows}

def wait_for_barrier(keyspace: str, uuid: str, poll: int = 15,
                     deadline_s: int = 6 * 3600) -> dict[str, str]:
    """Block until every shard is caught up, or raise on failure/timeout."""
    started = time.monotonic()
    while True:
        states = shard_statuses(keyspace, uuid)
        failed = {s: st for s, st in states.items() if st in TERMINAL_FAIL}
        if failed:
            raise RuntimeError(f"migration {uuid} failed on shards: {failed}")
        if states and all(st in CAUGHT_UP for st in states.values()):
            return states                      # barrier reached — safe to cut over
        if time.monotonic() - started > deadline_s:
            raise TimeoutError(f"migration {uuid} did not reach barrier in time: {states}")
        time.sleep(poll)

4. Enforce the barrier, then fire one fleet-wide cutover #

wait_for_barrier returning is the barrier being satisfied: every shard is caught up and postponed. Only then does the controller issue a single complete, which releases the cutover across the whole fleet. Guard the completion behind a persisted phase check so a restart that happens after completion does not fire it twice:

def complete(store: pymysql.Connection, keyspace: str, ddl: str, uuid: str) -> None:
    ctx = migration_context(keyspace, ddl)
    # If we already recorded completion, do not release the cutover again.
    with store.cursor() as cur:
        cur.execute("SELECT phase FROM ddl_pipeline WHERE context = %s", (ctx,))
        phase = cur.fetchone()[0]
    if phase in ("completing", "completed", "verified"):
        return
    with store.cursor() as cur:
        cur.execute("UPDATE ddl_pipeline SET phase = 'completing' WHERE context = %s", (ctx,))
    store.commit()
    subprocess.run(
        ["vtctldclient", "OnlineDDL", "complete", keyspace, uuid],
        check=True, timeout=120,
    )
    with store.cursor() as cur:
        cur.execute("UPDATE ddl_pipeline SET phase = 'completed' WHERE context = %s", (ctx,))
    store.commit()

OnlineDDL complete on a migration that has already cut over is itself a no-op in Vitess, so the phase guard is defence in depth, not the only line — but recording the phase transition keeps the controller’s own view consistent and gives operators an audit trail.

5. Assemble the loop #

The top-level driver wires the steps together so that every entry point is the same idempotent path, whether it is a first run or a restart:

def run_pipeline(store, keyspace: str, ddl: str) -> None:
    uuid = load_uuid(store, keyspace, ddl)
    if uuid is None:                    # first run for this change
        uuid = submit(keyspace, ddl)
        persist_uuid(store, keyspace, ddl, uuid)
    # From here every path is safe to re-run: poll, barrier, complete, verify.
    wait_for_barrier(keyspace, uuid)
    complete(store, keyspace, ddl, uuid)
    verify(keyspace, uuid)              # section 7

Configuration Reference #

The knobs below govern the loop’s timing and safety. Defaults favour caution; the recommended column is tuned for an unattended pipeline over a large sharded fleet.

The two misconfigurations that hurt most: submitting without --postpone-completion forfeits the barrier, so each shard cuts over the instant its own copy finishes and the loop has no window in which to coordinate; and running the controller without a durable store turns every restart into a fresh submission, which the next section covers as the double-submit failure.

Failure Modes #

Double-submit. Symptom: two migrations on the same table, or a --singleton rejection error on what should be a clean run. Root cause: the controller restarted and re-ran submit because it never checked the durable store — or checked a store that was not written before the crash. Mitigation: always load_uuid before submitting; keep --singleton on so that even a logic bug is caught by Vitess rejecting the second concurrent migration rather than launching it. The deeper treatment of making submission itself idempotent is in building idempotent DDL retry logic.

Lost UUID. Symptom: a migration is visibly running (SHOW VITESS_MIGRATIONS returns rows, _vt artifact tables are growing) but no controller is tracking it. Root cause: the controller crashed between submit returning and persist_uuid committing. Mitigation: the deterministic --migration-context is the recovery handle — a restarted controller queries vtctldclient OnlineDDL show <keyspace> --json and matches on context to reclaim its migration. Never rely on standard output as the only record of the UUID.

Partial cutover. Symptom: some shards on the new schema, others on the old; queries spanning them observe two table shapes. Root cause: cutover fired without a true barrier — either --postpone-completion was omitted, or the controller called complete before wait_for_barrier confirmed all shards were caught up. This is most dangerous for cross-shard transactions, which can see both shapes within one transaction. Mitigation: never complete until the aggregate verdict is unanimous; treat the fan-out as all-or-nothing and re-swap already-cut shards back to their retained originals if any shard’s swap fails.

Crash mid-poll. Symptom: the pipeline appears stuck; no progress is being recorded even though shards are advancing. Root cause: the controller process died during the poll loop and nothing restarted it. Mitigation: make the controller a restartable, stateless-except-for-the-store process (a Kubernetes Job with restartPolicy, a systemd unit, a workflow task) whose entire recovery is run_pipeline re-reading the store. Because polling reads state and never mutates it, resuming a poll is always safe — the cost of a crash mid-poll is only the seconds until the supervisor restarts the loop.

Control-plane call timeout. Symptom: a subprocess call to vtctldclient hangs indefinitely. Root cause: topology-server slowness or an unreachable vtctld. Mitigation: every subprocess.run carries an explicit timeout; on TimeoutExpired, back off and retry the read (idempotent) but never blindly retry a submit without first checking the store.

Verification #

Do not trust the pipeline’s own “completed” phase — confirm the outcome against Vitess directly. Every shard should report a terminal complete with no leftover artifacts:

def verify(keyspace: str, uuid: str) -> None:
    states = shard_statuses(keyspace, uuid)
    bad = {s: st for s, st in states.items() if st != "complete"}
    if bad:
        raise RuntimeError(f"post-cutover: shards not complete: {bad}")
    # Clean up retained artifact tables once the change is confirmed durable.
    subprocess.run(["vtctldclient", "OnlineDDL", "cleanup", keyspace, uuid],
                   check=True, timeout=60)

Then confirm the schema is actually visible through the router, not merely present on disk, by reading the new column back through VTGate:

-- Through VTGate: the migrated column must resolve on the shards you expect
SELECT fulfilment_center_id FROM orders WHERE order_id = 1082337;

Finally, watch post-cutover signals for a few minutes: SHOW VITESS_MIGRATIONS should show every shard complete, the _vt artifact table count should fall back to baseline after cleanup, and query-plan p99 latency should settle as optimizers recompile against the new definition. Only once p99 returns to baseline is the migration truly done — declaring victory at complete and walking away is how a clean migration becomes a latency incident an hour later.

Related #

• Polling Migration Status with vtctldclient — a robust poll_until_terminal() that survives non-JSON output, partial reporting, and transient errors.
• Building Idempotent DDL Retry Logic — deterministic migration keys, in-flight detection, and exponential backoff for a crash-safe controller.
• Coordinating Multi-Shard Schema Migrations — the serialization and phasing model the loop automates.
• Tracking Migration Progress and State Machines — the per-shard state the poller reduces to a single verdict.
• Throttling and Cutover Control — how the copy is paced and when the barrier can safely be released.

← Back to Online DDL Orchestration & Migration Coordination · Related area: Vitess Sharding Architecture & Topology Design