---
title: Safe WordPress Database Migrations with dbDelta and Online DDL
description: Run reliable WordPress custom-table migrations with dbDelta-compatible SQL, schema versioning, online DDL and reversible deployment safeguards.
url: https://moxseo.com/wordpress-database-migrations-dbdelta-online-ddl
date_modified: 2026-10-19
author: Aditya Bhimrajka
language: en_US
---

## Key takeaways

- `dbDelta()` compares a CREATE TABLE statement with the live table and alters it without dropping data, but it is picky about formatting (two spaces after `PRIMARY KEY`, lowercase data types, and so on).
- It can add columns, widen existing ones and create indexes. It **cannot** rename columns, drop removed ones or safely change foreign key constraints.
- A versioned Migration Manager built from one class per step makes schema changes repeatable, even when a site jumps several plugin versions at once (say, v1.2 straight to v3.0).
- On tables above roughly 500,000 rows a plain `ALTER TABLE` can block writes for minutes. Use MySQL 8.0 `ALGORITHM=INSTANT` where it applies, or move the work to Action Scheduler background batches.
- Build the charset clause with `$wpdb->get_charset_collate()`. Otherwise you risk subtle corruption and “Illegal mix of collations” errors in JOINs, especially with multilingual UTF-8 data.
- A shadow table swap (`RENAME TABLE v1 TO old, v2 TO v1`) restructures a table while orders keep flowing.
- PHPUnit schema tests confirm that tables and indexes come out right on both MySQL 8.0 and MariaDB 10.11 runners.

Once a custom WordPress application or commercial plugin reaches real volume, the default data model (posts, postmeta, terms, options) stops scaling. High-volume transactional work such as ledger logging, multi-vendor commission accounting, webhook event streams or IoT sensor telemetry calls for dedicated custom MySQL tables.

Creating the table on first activation is only the beginning. Requirements change: you will need new columns, different data types, composite indexes, retired structures and backfills over millions of rows. A migration that fails can corrupt customer data, lock tables while customers are checking out, or throw fatal PHP errors, and a slow or locked store also hurts the speed and crawlability your SEO depends on.

WordPress ships `dbDelta()` to create tables and diff schemas, but it is known for strict parsing quirks, almost no error reporting and no support for dropping or renaming columns. This guide dissects how `dbDelta()` parses SQL, builds a PSR-4 Schema Migration Framework, handles zero-downtime MySQL 8.0 DDL, runs cursor-based Action Scheduler backfills, performs shadow table atomic swaps, covers foreign key integrity and collation conflicts, and finishes with PHPUnit integration tests and WP-CLI tooling.

## How Strict `dbDelta()` Is About Syntax

`dbDelta()`, defined in `wp-admin/includes/upgrade.php`, has no real SQL grammar parser. It uses regular expressions to compare the existing table schema with the SQL string you hand it, so a stray space or a wrongly capitalized keyword can make it fail silently or issue the same `ALTER TABLE` on every page load.

### `dbDelta()` Formatting Checklist

| Syntax Requirement | Correct Format | Incorrect Format (Causes Failure) |
| --- | --- | --- |
| **Separate Line Per Field** | Each column definition on its own separate line. | Multiple fields declared on a single line separated by commas. |
| **PRIMARY KEY Whitespace** | `PRIMARY KEY  (id)` (Must have **TWO spaces** after KEY). | `PRIMARY KEY (id)` (Single space causes regex failure). |
| **Index Keyword** | `KEY idx_user_id (user_id)` | `INDEX idx_user_id (user_id)` (`INDEX` is often ignored). |
| **SQL Keyword Casing** | `CREATE TABLE wp_custom (` in uppercase. | `create table wp_custom (` in lowercase. |
| **Field Type Casing** | `varchar(255) NOT NULL` (Types in lowercase, attributes uppercase). | `VARCHAR(255) not null` |
| **Charset & Collate** | Append `$wpdb->get_charset_collate();` at the end. | Hardcoding `DEFAULT CHARSET=utf8` (Breaks emojis / 4-byte UTF-8). |

### A Reliable `dbDelta()` DDL Template

This is the exact layout a dependable table definition needs:

```
<?php
declare(strict_types=1);

function wpstack_get_invoices_schema(): string {
    global $wpdb;
    $table_name = $wpdb->prefix . 'wpstack_invoices';
    $charset_collate = $wpdb->get_charset_collate();

    return "CREATE TABLE {$table_name} (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        order_id bigint(20) unsigned NOT NULL,
        customer_id bigint(20) unsigned NOT NULL,
        invoice_number varchar(64) NOT NULL DEFAULT '',
        total_amount decimal(12,4) NOT NULL DEFAULT '0.0000',
        currency varchar(3) NOT NULL DEFAULT 'USD',
        status varchar(32) NOT NULL DEFAULT 'pending',
        created_at datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
        updated_at datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
        PRIMARY KEY  (id),
        KEY idx_order_id (order_id),
        KEY idx_customer_status (customer_id, status),
        KEY idx_created_at (created_at)
    ) {$charset_collate};";
}
```

## What `dbDelta()` Will Not Do

`dbDelta()` is good at creating tables and adding missing columns, but it has real limits you must plan around:

- **No column drops:** Delete a field from the schema string and `dbDelta()` just ignores the omission. It never runs `DROP COLUMN`, which protects data from accidental loss.
- **No column renames:** If you change `user_id` to `customer_id`, `dbDelta()` sees a brand-new column, adds it with default values and leaves the old `user_id` column and its data behind.
- **No primary key changes:** Turning a single-column primary key into a composite key is not supported.
- **No foreign keys:** `dbDelta()` neither parses nor maintains `FOREIGN KEY ... REFERENCES` constraints.
- **Not for heavy migrations on huge tables:** Running `dbDelta()` in an admin request against a very large table risks PHP timeouts and HTTP 504 errors.

## Collation and Character Set Traps in MySQL 8.0

A particularly sneaky failure shows up when a custom table is joined to a WordPress core table (for example `INNER JOIN wp_users ON wp_wpstack_invoices.customer_id = wp_users.ID`). If the two tables use different character sets or collations, MySQL stops with an error:

```
-- Fatal Database Error
Error: 1267 SQLSTATE: HY000 (ER_CANT_AGGREGATE_2COLLATIONS)
Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT) for operation '='
```

It happens when a collation is hardcoded or left to the server default. On WordPress 6.x with MySQL 8.0, core tables normally use `utf8mb4_unicode_520_ci` or `utf8mb4_0900_ai_ci`. Appending `$wpdb->get_charset_collate()` to your DDL makes the new table match them exactly.

## Designing a Versioned PSR-4 Migration Engine

To work around those gaps, mature teams wrap schema changes in a versioned migration framework. It records every applied migration in its own store and runs schema diffs and data transformations in order.

### 1. The Interface (`MigrationInterface.php`)

```
<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginDatabaseMigrations;

interface MigrationInterface {
    /**
     * Unique semantic version or timestamp (e.g. "2026_10_01_001").
     */
    public function getVersion(): string;

    /**
     * Human-readable description of migration purpose.
     */
    public function getDescription(): string;

    /**
     * Execute forward migration logic.
     */
    public function up(): void;

    /**
     * Optional rollback logic for staging test environments.
     */
    public function down(): void;
}
```

### 2. A First Migration That Creates the Table (`Migration_2026_10_01_CreateInvoicesTable.php`)

```
<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginDatabaseMigrations;

use wpdb;

class Migration_2026_10_01_CreateInvoicesTable implements MigrationInterface {
    public function __construct(private readonly wpdb $db) {}

    public function getVersion(): string {
        return '2026_10_01_001';
    }

    public function getDescription(): string {
        return 'Create initial custom invoices table with composite indexes.';
    }

    public function up(): void {
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $tableName = $this->db->prefix . 'wpstack_invoices';
        $charsetCollate = $this->db->get_charset_collate();

        $sql = "CREATE TABLE {$tableName} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            order_id bigint(20) unsigned NOT NULL,
            customer_id bigint(20) unsigned NOT NULL,
            invoice_number varchar(64) NOT NULL DEFAULT '',
            total_amount decimal(12,4) NOT NULL DEFAULT '0.0000',
            status varchar(32) NOT NULL DEFAULT 'pending',
            created_at datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
            PRIMARY KEY  (id),
            KEY idx_order_id (order_id),
            KEY idx_customer_status (customer_id, status)
        ) {$charsetCollate};";

        dbDelta($sql);
    }

    public function down(): void {
        $tableName = $this->db->prefix . 'wpstack_invoices';
        $this->db->query("DROP TABLE IF EXISTS {$tableName}");
    }
}
```

### 3. Renaming Columns and Backfilling Data

When an update has to rename a column or reshape data, combine `dbDelta()` with explicit transactional DDL statements:

```
<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginDatabaseMigrations;

use wpdb;

class Migration_2026_10_15_AddTaxColumnsAndRenameStatus implements MigrationInterface {
    public function __construct(private readonly wpdb $db) {}

    public function getVersion(): string {
        return '2026_10_15_002';
    }

    public function getDescription(): string {
        return 'Add tax_rate column and safely migrate legacy status codes.';
    }

    public function up(): void {
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $tableName = $this->db->prefix . 'wpstack_invoices';
        $charsetCollate = $this->db->get_charset_collate();

        // 1. Use dbDelta to add new columns safely
        $sql = "CREATE TABLE {$tableName} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            order_id bigint(20) unsigned NOT NULL,
            customer_id bigint(20) unsigned NOT NULL,
            invoice_number varchar(64) NOT NULL DEFAULT '',
            total_amount decimal(12,4) NOT NULL DEFAULT '0.0000',
            tax_rate decimal(5,2) NOT NULL DEFAULT '0.00',
            tax_amount decimal(12,4) NOT NULL DEFAULT '0.0000',
            status varchar(32) NOT NULL DEFAULT 'pending',
            created_at datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
            PRIMARY KEY  (id),
            KEY idx_order_id (order_id),
            KEY idx_customer_status (customer_id, status)
        ) {$charsetCollate};";

        dbDelta($sql);

        // 2. Execute transactional data transform
        $this->db->query("START TRANSACTION");
        try {
            // Transform legacy status values
            $this->db->query(
                "UPDATE {$tableName} 
                 SET status = 'paid_in_full' 
                 WHERE status = 'complete'"
            );

            // Compute default tax amounts for existing rows
            $this->db->query(
                "UPDATE {$tableName} 
                 SET tax_amount = total_amount * 0.20, tax_rate = 20.00 
                 WHERE tax_amount = 0.0000 AND status = 'paid_in_full'"
            );

            $this->db->query("COMMIT");
        } catch (Throwable $e) {
            $this->db->query("ROLLBACK");
            throw $e;
        }
    }

    public function down(): void {
        $tableName = $this->db->prefix . 'wpstack_invoices';
        $this->db->query("ALTER TABLE {$tableName} DROP COLUMN tax_rate, DROP COLUMN tax_amount");
    }
}
```

### 4. The Coordinator (`SchemaMigrationManager.php`)

The coordinator decides the run order, reads which migration versions are already applied from the database, and prevents anything running twice:

```
<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginDatabase;

use wpdb;
use WPStackEnterprisePluginDatabaseMigrationsMigrationInterface;

class SchemaMigrationManager {
    private const OPTION_KEY = 'wpstack_applied_schema_migrations';

    /** @var list */
    private array $migrations = [];

    public function __construct(private readonly wpdb $db) {}

    public function registerMigration(MigrationInterface $migration): void {
        $this->migrations[] = $migration;
    }

    /**
     * Execute all pending migrations in chronological order.
     *
     * @return array{executed: list, skipped: list}
     */
    public function migrate(): array {
        $applied = $this->getAppliedMigrations();
        $executed = [];
        $skipped = [];

        // Sort migrations by version string
        usort($this->migrations, static fn(MigrationInterface $a, MigrationInterface $b) => strcmp($a->getVersion(), $b->getVersion()));

        foreach ($this->migrations as $migration) {
            $version = $migration->getVersion();

            if (in_array($version, $applied, true)) {
                $skipped[] = $version;
                continue;
            }

            // Execute migration step
            $migration->up();

            // Record version as applied
            $applied[] = $version;
            update_option(self::OPTION_KEY, $applied, false);
            $executed[] = $version;
        }

        return [
            'executed' => $executed,
            'skipped'  => $skipped,
        ];
    }

    /**
     * Retrieve list of previously applied migration version tags.
     *
     * @return list
     */
    public function getAppliedMigrations(): array {
        $applied = get_option(self::OPTION_KEY, []);
        return is_array($applied) ? $applied : [];
    }
}
```

## Backfilling Data in the Background with Action Scheduler

Suppose a migration adds a computed column to a table with 2,000,000+ rows. A single `UPDATE wp_custom SET column = calc()` locks the whole table for 45+ seconds, halts writes and causes replication lag.

Instead, keep the schema change separate from the heavy data transformation and process the data through an Action Scheduler queue that advances a cursor:

```
<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginDatabaseQueue;

use wpdb;

class SchemaBackfillWorker {
    public const HOOK_NAME = 'wpstack_async_schema_backfill';
    public const GROUP_NAME = 'wpstack_migrations';
    private const BATCH_SIZE = 500;

    public function __construct(private readonly wpdb $db) {}

    public function register(): void {
        add_action(self::HOOK_NAME, [$this, 'processBatch'], 10, 1);
    }

    public function dispatchInitialJob(): void {
        if (!as_has_scheduled_action(self::HOOK_NAME, ['last_id' => 0], self::GROUP_NAME)) {
            as_schedule_single_action(time(), self::HOOK_NAME, ['last_id' => 0], self::GROUP_NAME);
        }
    }

    public function processBatch(int $lastId): void {
        $tableName = $this->db->prefix . 'wpstack_invoices';

        // Fetch primary keys of next batch using cursor pagination (avoids OFFSET performance cliff)
        $ids = $this->db->get_col(
            $this->db->prepare(
                "SELECT id FROM {$tableName} 
                 WHERE id > %d AND tax_amount = 0.0000 
                 ORDER BY id ASC 
                 LIMIT %d",
                $lastId,
                self::BATCH_SIZE
            )
        );

        if (empty($ids)) {
            // Backfill finished! Mark migration flag as complete
            update_option('wpstack_tax_backfill_completed', true, false);
            return;
        }

        $idPlaceholders = implode(',', array_map('intval', $ids));
        $maxProcessedId = max(array_map('intval', $ids));

        // Execute batch update on small subset of locked rows
        $this->db->query(
            "UPDATE {$tableName} 
             SET tax_amount = total_amount * 0.20, tax_rate = 20.00 
             WHERE id IN ({$idPlaceholders})"
        );

        // Schedule next batch immediately
        as_schedule_single_action(time(), self::HOOK_NAME, ['last_id' => $maxProcessedId], self::GROUP_NAME);
    }
}
```

## Shadow Table Swaps Without Downtime

Changing primary keys, switching storage engines or shedding gigabytes of bloat is too risky to do in place on a busy store. The **shadow table atomic swap** pattern avoids that:

1. **Create the shadow table:** Provision `wp_wpstack_invoices_v2` with the final schema.
2. **Write to both tables:** In the plugin’s repository layer, save each new record to `v1` and `v2` at the same time.
3. **Backfill history:** Copy existing rows from `v1` to `v2` in chunks with Action Scheduler.
4. **Swap the tables:** Rename both in one atomic MySQL statement, which completes instantly:
5. **Retire the old table:** After seven days of clean telemetry, drop `wp_wpstack_invoices_legacy_backup`.

With this pattern, a structural change carries no downtime and no risk of losing data, which suits high-availability setups and custom plugin work alike. MoxSEO’s web development team can help plan and review such migrations; see our [web development services](/services/web-development/).

## Foreign Keys and Cascading Deletes in Application Code

WordPress core stays away from database-level foreign keys (such as `FOREIGN KEY (user_id) REFERENCES wp_users(ID) ON DELETE CASCADE`) so it keeps working with MyISAM and older storage engines. In plugins, leaning only on MySQL foreign keys can also deadlock against core’s deletion hooks.

A hybrid approach works better: solid database indexes, plus application-level listeners on WordPress deletion actions that cascade the cleanup:

```
<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginDatabase;

use wpdb;

class DatabaseCascadeIntegrityManager {
    public function __construct(private readonly wpdb $db) {}

    public function register(): void {
        // Listen for WordPress user deletions
        add_action('deleted_user', [$this, 'handleUserDeleted'], 10, 2);
        // Listen for WooCommerce order deletions
        add_action('woocommerce_delete_order', [$this, 'handleOrderDeleted'], 10, 1);
    }

    public function handleUserDeleted(int $userId, ?int $reassignId): void {
        $tableName = $this->db->prefix . 'wpstack_invoices';

        if ($reassignId !== null && $reassignId > 0) {
            // Reassign customer invoices to target user
            $this->db->update($tableName, ['customer_id' => $reassignId], ['customer_id' => $userId]);
        } else {
            // Soft-delete or mark invoices as orphaned for accounting compliance
            $this->db->update($tableName, ['status' => 'anonymized_gdpr'], ['customer_id' => $userId]);
        }
    }

    public function handleOrderDeleted(int $orderId): void {
        $tableName = $this->db->prefix . 'wpstack_invoices';
        // Delete child invoices when parent order is permanently removed
        $this->db->delete($tableName, ['order_id' => $orderId]);
    }
}
```

## Running Migrations Across a Multisite Network

On a Multisite network with thousands of sub-sites, running migrations in a single request at network activation will eventually exhaust PHP workers. Process sub-sites in cursor-based batches instead:

```
<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginDatabaseMultisite;

use WP_Site;
use WPStackEnterprisePluginDatabaseSchemaMigrationManager;

class MultisiteMigrationOrchestrator {
    public const ASYNC_HOOK = 'wpstack_multisite_batch_migrate';
    private const SITE_CHUNK_SIZE = 50;

    public function register(): void {
        add_action('wp_initialize_site', [$this, 'migrateNewSite'], 10, 1);
        add_action(self::ASYNC_HOOK, [$this, 'processSiteBatch'], 10, 1);
    }

    public function migrateNewSite(WP_Site $site): void {
        switch_to_blog((int) $site->blog_id);
        try {
            global $wpdb;
            $manager = new SchemaMigrationManager($wpdb);
            $manager->migrate();
        } finally {
            restore_current_blog();
        }
    }

    public function processSiteBatch(int $lastSiteId): void {
        $sites = get_sites([
            'number'   => self::SITE_CHUNK_SIZE,
            'site__gt' => $lastSiteId,
            'public'   => 1,
            'spam'     => 0,
            'deleted'  => 0,
        ]);

        if (empty($sites)) {
            update_site_option('wpstack_network_migration_complete', true);
            return;
        }

        $highestId = $lastSiteId;

        foreach ($sites as $site) {
            $siteId = (int) $site->blog_id;
            $highestId = max($highestId, $siteId);

            switch_to_blog($siteId);
            try {
                global $wpdb;
                $manager = new SchemaMigrationManager($wpdb);
                $manager->migrate();
            } finally {
                restore_current_blog();
            }
        }

        // Enqueue next subsite chunk
        as_schedule_single_action(time() + 2, self::ASYNC_HOOK, ['last_site_id' => $highestId], 'wpstack_migrations');
    }
}
```

## Online Migrations for High-Traffic Tables

Once a custom table passes 500,000 rows, an ordinary `ALTER TABLE` run through `dbDelta()` can lock it for minutes, block WooCommerce checkouts and bring 504 Gateway Timeouts.

| DDL Operation | MySQL 8.0 Algorithm | Locking Behavior | Production Impact |
| --- | --- | --- | --- |
| **Add Column at Table End** | `ALGORITHM=INSTANT` | `LOCK=NONE` | **Zero downtime** (Completes in < 10ms regardless of table size). |
| **Add Secondary Index** | `ALGORITHM=INPLACE` | `LOCK=NONE` | Reads and concurrent writes remain fully active during build. |
| **Modify Column Data Type** | `ALGORITHM=COPY` | `LOCK=SHARED` | **High Risk:** Locks table writes. Must use Action Scheduler batching. |
| **Add Foreign Key Constraint** | `ALGORITHM=INPLACE` | `LOCK=NONE` | Requires validation scan; minimal concurrency impact. |

## Incident Walkthrough: A Table Lockout on Black Friday

Take an online luxury goods store running custom order-fulfillment plugins whose checkout failed completely on Black Friday. At 00:05 UTC an automated plugin update ran `dbDelta()` inside a `plugins_loaded` hook to add an index to an 8-million-row table.

### 1. What Happened and Why

- **Metadata lock pile-up:** The `ALTER TABLE` asked for an exclusive metadata lock, but checkout transactions still held read locks. MySQL queued the DDL and every new order write queued behind it.
- **Worker exhaustion:** Within 40 seconds PHP-FPM hit its 256-worker limit and the whole store returned 504 Gateway Timeouts.

### 2. How It Was Fixed

1. **Killed the blocked DDL:** The on-call team ran `SHOW PROCESSLIST` and killed the waiting `ALTER TABLE` thread, which restored checkout immediately.
2. **Moved to online DDL:** They added the composite index with `ALTER TABLE wp_orders ADD KEY idx_status (status), ALGORITHM=INPLACE, LOCK=NONE;` in a planned off-peak window.
3. **Changed the policy:** Synchronous `dbDelta()` calls in general page-load hooks were banned, and the versioned `SchemaMigrationManager` now runs only from WP-CLI deploy scripts.

## Managing Schema Changes with WP-CLI

These WP-CLI commands expose migrations to DevOps automation pipelines:

```
<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginCli;

use WP_CLI;
use WPStackEnterprisePluginDatabaseSchemaMigrationManager;
use WPStackEnterprisePluginDatabaseMigrationsMigration_2026_10_01_CreateInvoicesTable;
use WPStackEnterprisePluginDatabaseMigrationsMigration_2026_10_15_AddTaxColumnsAndRenameStatus;

class SchemaCliCommand {
    /**
     * Run all pending custom database schema migrations.
     *
     * ## EXAMPLES
     *
     *     wp wpstack-schema migrate
     */
    public function migrate(array $args, array $assocArgs): void {
        global $wpdb;
        $manager = new SchemaMigrationManager($wpdb);
        $manager->registerMigration(new Migration_2026_10_01_CreateInvoicesTable($wpdb));
        $manager->registerMigration(new Migration_2026_10_15_AddTaxColumnsAndRenameStatus($wpdb));

        WP_CLI::log('Starting database schema migration check...');
        $result = $manager->migrate();

        if (empty($result['executed'])) {
            WP_CLI::success('Database schema is already up to date. No pending migrations.');
            return;
        }

        foreach ($result['executed'] as $version) {
            WP_CLI::log("Successfully applied migration: [{$version}]");
        }

        WP_CLI::success('All pending schema migrations completed successfully.');
    }

    /**
     * Display status of applied and pending migrations.
     *
     * ## EXAMPLES
     *
     *     wp wpstack-schema status
     */
    public function status(array $args, array $assocArgs): void {
        global $wpdb;
        $manager = new SchemaMigrationManager($wpdb);
        $applied = $manager->getAppliedMigrations();

        WP_CLI::line('Applied Schema Migrations:');
        if (empty($applied)) {
            WP_CLI::line('  (None)');
            return;
        }

        foreach ($applied as $version) {
            WP_CLI::line("  - [X] {$version}");
        }
    }
}

if (defined('WP_CLI') && WP_CLI) {
    WP_CLI::add_command('wpstack-schema', SchemaCliCommand::class);
}
```

## Testing Migrations with PHPUnit

PHPUnit exercises the full migration lifecycle and checks that tables are created, column changes apply cleanly and indexes exist:

```
<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginTestsIntegration;

use WP_UnitTestCase;
use WPStackEnterprisePluginDatabaseSchemaMigrationManager;
use WPStackEnterprisePluginDatabaseMigrationsMigration_2026_10_01_CreateInvoicesTable;
use WPStackEnterprisePluginDatabaseMigrationsMigration_2026_10_15_AddTaxColumnsAndRenameStatus;

class Test_SchemaMigrationsIntegration extends WP_UnitTestCase {
    private SchemaMigrationManager $manager;
    private string $tableName;

    public function setUp(): void {
        parent::setUp();
        global $wpdb;

        $this->tableName = $wpdb->prefix . 'wpstack_invoices';
        $wpdb->query("DROP TABLE IF EXISTS {$this->tableName}");
        delete_option('wpstack_applied_schema_migrations');

        $this->manager = new SchemaMigrationManager($wpdb);
        $this->manager->registerMigration(new Migration_2026_10_01_CreateInvoicesTable($wpdb));
        $this->manager->registerMigration(new Migration_2026_10_15_AddTaxColumnsAndRenameStatus($wpdb));
    }

    public function tearDown(): void {
        global $wpdb;
        $wpdb->query("DROP TABLE IF EXISTS {$this->tableName}");
        parent::tearDown();
    }

    public function test_migrations_create_table_and_apply_incremental_columns(): void {
        global $wpdb;

        // 1. Execute migrations
        $result = $this->manager->migrate();
        $this->assertCount(2, $result['executed']);

        // 2. Verify table exists
        $tableExists = $wpdb->get_var("SHOW TABLES LIKE '{$this->tableName}'");
        $this->assertSame($this->tableName, $tableExists);

        // 3. Verify columns exist
        $columns = $wpdb->get_col("DESCRIBE {$this->tableName}", 0);
        $this->assertContains('id', $columns);
        $this->assertContains('order_id', $columns);
        $this->assertContains('tax_rate', $columns);
        $this->assertContains('tax_amount', $columns);

        // 4. Verify re-running migration performs zero duplicate executions
        $secondRun = $this->manager->migrate();
        $this->assertEmpty($secondRun['executed']);
        $this->assertCount(2, $secondRun['skipped']);
    }
}
```

## Troubleshooting Migrations in Production

For complex custom-table migrations on multisite and larger installs, this table lists the usual symptoms, causes and fixes:

| Issue Symptom | Root Cause | Resolution Procedure |
| --- | --- | --- |
| `dbDelta() fails to add index or executes duplicate ALTERs` | Only one space after `PRIMARY KEY`, or `INDEX` used where `KEY` is required. | Write `PRIMARY KEY  (id)` with exactly two spaces between `KEY` and `(`, and declare every index as `KEY name (col)`. |
| `Foreign key constraint creation fails` | The child and parent columns have different data types (for example `INT(11)` versus `BIGINT(20) UNSIGNED`). | Make sure the referenced ID columns have the same data types and collations as the parent WordPress tables. |
| `Character corruption / emojis broken in custom table` | The table was created with the default `latin1` instead of the WordPress table collation. | Put `$wpdb->get_charset_collate()` at the end of every `CREATE TABLE` string. |
| `Table lock timeout on ALTER TABLE` | A long-running read on the custom table is holding up the DDL statement. | Use MySQL 8.0 `ALGORITHM=INSTANT`, or schedule the change for an off-peak maintenance window. |
| `Migration runs on every page load` | The applied version is not being saved in `wp_options`, or the version comparison has a bug. | Confirm that `update_option('wpstack_applied_schema_migrations', $applied, false)` actually stores the version tags. |
| `Multisite sub-sites missing custom table schemas` | The migration ran only on the main site (blog ID 1) during network activation. | Hook the migration manager into `wp_initialize_site` so each new sub-site gets its schema. |

## Common Questions

### What does `dbDelta()` do in WordPress?

`dbDelta()` is a core helper that compares a CREATE TABLE statement with the existing table and runs whatever ALTER TABLE statements are needed to update the schema, without dropping existing data.

### Why does `dbDelta()` need two spaces after `PRIMARY KEY`?

Its parser is a set of regular expressions, and the pattern expects `PRIMARY KEY  (` with two spaces. With one space the match fails and the primary key is skipped.

### Will `dbDelta()` remove columns I no longer use?

No. To avoid accidental data loss it never issues `DROP COLUMN`. Remove obsolete columns yourself with an explicit `ALTER TABLE table_name DROP COLUMN column_name`.

### What is the safe way to rename a column?

Do it in phases: add the new column with `dbDelta()`, copy the data across with a background Action Scheduler task, switch your code to read the new column, and drop the old column in a later release.

### What does MySQL 8.0 `ALGORITHM=INSTANT` do for WordPress plugins?

It lets MySQL add a column by changing only the table metadata, which takes milliseconds and avoids copying table data or locking reads and writes.

### Why use `$wpdb->get_charset_collate()` for custom tables?

It gives the table the same character set and collation as the rest of the WordPress database (for example `utf8mb4_unicode_520_ci`), so emoji store correctly and JOINs against core tables do not hit collation conflicts.

### How can I test schema migrations in CI?

Use PHPUnit with `WP_UnitTestCase`: run the migration classes in order against a clean MySQL test database, then use `SHOW TABLES` and `DESCRIBE table_name` to assert the expected columns and indexes exist.

### How should migrations run on a Multisite network?

For per-site prefixed tables, migrate the current site on plugin activation, hook `wp_initialize_site` for sub-sites created later, and use an asynchronous Action Scheduler batch worker for the sub-sites that already exist.