Database Migration Safety When the Store Cannot Go Offline

A release note says "adds a new column for the shipping carrier". The column is small, the change ships on a Tuesday, and the statement runs against forty thousand order rows in less time than the request allows. On a table ten times that size it does not. PHP kills the request at the time limit, and the plugin has already written the version marker that says the migration is finished.

What the store now has is the new column, no data in it, and a plugin that will never try again. The failure is not exotic. It is the default outcome of treating a schema change like a normal request, and it appears on exactly the stores where the change mattered.

None of this would be a problem if you could take the database offline, run a script, and check the result before putting it back. On a store you operate, that is what you do. Inside a plugin you have no such option, because there is no moment when the site is not serving traffic.

A plugin migration is not an application migration

The difference is worth spelling out, because habits from application work are what cause the damage. It runs inside a web request, so it shares that request's time limit and memory budget. It runs on a database you do not administer, which may be a shared instance with a connection limit you have never seen. It can run on thousands of sites at once, all of them triggering the same code path the moment the release lands. And there is no rollback: your code can be reverted, the schema cannot.

That last point is the one people miss. Reverting a plugin file takes two minutes. The next request loads the old code against the new schema, and the old code has never seen that column. Every privilege you thought a version-controlled deploy gave you evaporates at the database boundary.

Never run a schema change on every request

The first rule is also the one most often broken by accident. A plugin that calls dbDelta() or CREATE TABLE IF NOT EXISTS on plugins_loaded runs that call on every request that boots WordPress. On a quiet site with a small schema, you may not notice for months. On a busy site, you have added a schema comparison to every page view.

Gate it. Store a schema version in an option, compare it to the version the code expects, and run the migration when they differ. The trigger should be an explicit action rather than an ordinary page load: register_activation_hook() for a fresh install, a WP-CLI command for a network of sites, or an admin-page action for a host without WP-CLI.

// Runs once per release, not once per request.
add_action( 'admin_init', 'myplugin_maybe_migrate' );

function myplugin_maybe_migrate() {
    $target = '2026_01_carrier_column';

    if ( get_option( 'myplugin_schema_version' ) === $target ) {
        return;
    }

    // One worker does the work; the rest wait and then find it done.
    if ( ! myplugin_acquire_migration_lock() ) {
        return;
    }

    myplugin_run_migration( $target );

    // Written only after the work returns, never before it starts.
    update_option( 'myplugin_schema_version', $target, false );
    myplugin_release_migration_lock();
}

Two details in that snippet carry the weight. The version option is written after the work, not before, because a marker written first is a promise the code has not kept yet. And the lock matters with concurrent traffic: two requests can arrive at admin_init within the same second, both see a stale version, and both start the same ALTER TABLE.

What the version check and the lock prevent

The version marker and the lock prevent three outcomes, and each has a signature.

The migration runs twice. Two requests race past the version check before either writes the marker, and both start the work. On a CREATE TABLE the second run fails with an error about the table already existing, so the trigger is a log line. On a backfill it is worse: two loops writing the same rows and fighting over the same cursor option. A lock with an expiry removes the race, because the second request bails out instead of duplicating work.

The migration never runs. This is the one that hides. The marker was written before the work finished, the request was killed in between, and every later request sees a matching version and returns early. The site looks fine until somebody reads a column that is still empty months later. Write the marker after the work returns, inside the same lock. If the process dies, the marker stays stale and the next request tries again.

The migration appears to succeed and leaves both tables. A script that creates the new table and is killed partway through leaves the old one in place with the new one beside it. Nothing reports an error, because nothing failed. The stale table sits there accumulating. This is why verification matters as much as the migration: query the state you expect and check that it matches. A version option records intent. It is not evidence that the work happened.

Make the migration re-entrant before you make it fast

Assume the request will be killed halfway through. On a large table it probably will be. The question is what the next request finds.

In practice this means every step is conditional. Add a column only if it is not there. Create a table only if it does not exist. Backfill in batches and record your progress, so a run that stops after ten thousand rows resumes at ten thousand rather than starting over. A migration that assumes a clean starting state fails on every store where the previous attempt was interrupted, which is the population you were writing it for.

The lock has an expiry for the same reason. A lock held by a request that died blocks every later attempt. Give it a short lifetime and treat an expired lock as available.

Add before you remove, and let dbDelta() do only what it does

During the transition, the old column and the new one both exist and both work: the code reads and writes the new one, and the old one remains as a fallback for a code path you forgot to update. Removing the old column is a separate release, after the new one has been populated and verified.

The instinct to rename a column is understandable and usually wrong. Renaming is a drop with extra steps from the rollback's point of view. Adding a second column, filling it, and leaving the first in place costs storage and buys a clean way back.

The same applies to a release that stops reading a column. Do not drop it in the same version that stopped using it. If the release turns out badly and you roll the code back, the old code needs the old data, and a rollback without data is not a rollback. Ship the code change, confirm it, then drop the column in a later release when nobody will revert to a build that needs it.

dbDelta() is the function you reach for to create or update a table from a CREATE TABLE statement. It compares that statement to the live table and issues the ALTER TABLE calls needed to reconcile them. That is useful, and it is narrower than most people assume.

It only adds and alters. It does not drop a column that your statement no longer mentions, so a field you remove from the SQL quietly stays in the database. It cannot move data between columns, and it will not change an index in every case you might expect. It also cares about formatting in a way that has no equivalent elsewhere: two spaces after PRIMARY KEY, one field per line, and no backticks around the field names. A statement that is valid SQL but formatted differently is one dbDelta() will fail to parse correctly, and it usually fails by doing less than you asked rather than by reporting an error.

function myplugin_create_table() {
    global $wpdb;

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';

    $table   = $wpdb->prefix . 'carrier_events';
    $collate = $wpdb->get_charset_collate();

    // dbDelta() is strict about this formatting: one field per line,
    // two spaces after PRIMARY KEY, no backticks.
    $sql = "CREATE TABLE $table (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        order_id bigint(20) unsigned NOT NULL,
        carrier varchar(64) NOT NULL DEFAULT '',
        created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
        PRIMARY KEY  (id),
        KEY order_id (order_id)
    ) $collate;";

    dbDelta( $sql );
}

Run that from an activation hook or a WP-CLI command, never from a request that fires on every page. And because dbDelta() will not remove anything, a schema change that removes a column needs a deliberate drop, in its own release, with a decision about the data it holds.

Batch the data, and check the state the migration leaves behind

Schema changes are cheap compared to data moves. A single UPDATE wp_carrier_events SET ... across a table with millions of rows holds locks, grows the undo log, and may outlive the request that started it. Batch it: select a bounded set of rows, update them, commit, and move the marker forward. If the process dies, the next run picks up where the last one stopped.

function myplugin_backfill_carriers() {
    global $wpdb;

    $table    = $wpdb->prefix . 'carrier_events';
    $last_id  = (int) get_option( 'myplugin_backfill_cursor', 0 );
    $batch    = 500;

    while ( true ) {
        $ids = $wpdb->get_col( $wpdb->prepare(
            "SELECT id FROM $table WHERE id > %d ORDER BY id ASC LIMIT %d",
            $last_id,
            $batch
        ) );

        if ( empty( $ids ) ) {
            break;
        }

        foreach ( $ids as $id ) {
            $wpdb->update(
                $table,
                array( 'carrier' => 'unknown' ),
                array( 'id' => (int) $id ),
                array( '%s' ),
                array( '%d' )
            );
        }

        $last_id = (int) end( $ids );
        update_option( 'myplugin_backfill_cursor', $last_id, false );

        // Leave room for the rest of the request.
        if ( myplugin_time_is_short() ) {
            return;
        }
    }

    delete_option( 'myplugin_backfill_cursor' );
}

The cursor is the trick. It turns an operation that must finish into one that can be paused and resumed, which is the only property that matters when a request can be killed at any point.

After the run, verify the state rather than trusting the version option. Count the rows that still hold the old value. Check that the column you added exists with the type you expected. A migration that appears to succeed and leaves both a new and an old table behind is a common outcome of a script that created the new table and then died, and the check for it is a query, not a feeling.

What to do on Monday

1. Search the plugin for any schema call that is not behind a version check or an activation hook. Each one is a tax on every request.
2. Give the migration a lock with an expiry, and write the version marker after the work, not before it.
3. Convert any unbounded UPDATE on a large table into a cursored loop.
4. Rehearse the migration against a restored copy of the largest database you can get, with a timer running, and write down how long it took.

None of this saves a migration that is simply wrong

Everything above assumes the migration does what it claims. If the backfill maps the wrong carrier code, or the new column means something different from the old one, no amount of locking, batching or ordering will catch it. The procedure protects the database from the process. It does not protect the data from the idea.

The only safety net for a migration that is wrong is a restore path somebody has tested: a recent backup, a documented restore command, and a person who knows how long it takes to run. On the store-sized datasets where migrations hurt, that rehearsal is the difference between an afternoon and a week. If you cannot restore last night's database into a staging environment, you do not know whether your migration is safe. You only know that it ran.