Safer PHP and MySQL migrations for live applications

Database migrations become risky when old and new PHP processes can run at the same time. A migration is safer when both application versions remain compatible throughout the release window.

Expand before you contract

Add nullable columns or new tables first. Deploy code that can read the old shape and optionally write both shapes. Backfill existing records in bounded batches, measure progress, and only make the new field mandatory after every writer and reader has moved.

Keep locks and transactions bounded

Review the database engine's online DDL behavior before altering a large table. A single huge update can create long locks, excessive replication lag, or an undo log that is difficult to recover. Batch by a stable indexed key and make every batch restartable.

Inventory every reader and writer

Before editing the schema, list the code paths that read or write the affected data: web requests, admin tools, workers, cron jobs, imports, exports, reporting queries, replicas and direct integrations. A migration that works for the main PHP controller can still break an older queue worker or a report running from a replica.

Capture the table size, row growth, largest indexes, foreign keys, engine version and replication topology. Run the proposed statement against a production-shaped copy and inspect the execution plan. A quick change on a development table with one thousand rows says little about an active table with millions of rows.

A concrete expand-and-contract release

1. Add the new nullable column or parallel table without changing existing reads.
2. Deploy code that understands both shapes and writes the old and new representation where necessary.
3. Backfill old records in small restartable batches using a stable indexed cursor.
4. Measure mismatches until the new representation is complete and current.
5. Switch reads behind a feature flag or controlled deployment.
6. Stop writing the old representation only after every running version has moved.
7. Remove obsolete data in a separate later release after the rollback window closes.

Dual writing can itself fail if one write succeeds and the other does not. Prefer one transaction when both representations are in the same database. If the change crosses systems, use a durable outbox or reconciliation job and define which source is authoritative.

Write a restartable backfill

Offset pagination becomes slower and can skip work while rows change. Iterate by a stable indexed key instead. Save the last completed key, cap the batch size, commit between batches, and make the update conditional so rerunning it does not damage already migrated rows.

UPDATE customer_profiles
SET normalized_phone = :normalized_phone
WHERE id = :id
  AND normalized_phone IS NULL

The backfill process should report rows scanned, rows changed, failures, throughput and the latest key. Add a pause switch. Throttle when application latency or replica delay crosses a defined threshold. Avoid printing personal values into a general migration log.

Understand DDL behavior on the installed engine

MySQL and MariaDB do not provide identical online DDL behavior for every version, engine and alteration. Adding an index, changing a column type and rebuilding a primary key have very different costs. Terms such as “online” can still permit brief metadata locks that wait behind long transactions and then block new work.

Check the exact documentation for the deployed version, request an explicit algorithm and lock level where supported, and verify what the server actually chose. Schedule potentially rebuilding changes with sufficient disk headroom. On a replicated setup, observe apply lag and binary-log growth throughout the operation.

Constraints are the final step, not the first

A new NOT NULL, unique or foreign-key constraint should reflect data that has already been validated. Find duplicates and invalid references before enabling enforcement. If a default value is used to make a migration easy, confirm that the default is meaningful to the domain rather than a placeholder that silently corrupts reporting.

Treat rollback as an application concern

Rolling back PHP code is straightforward only while the previous version understands the current schema. Feature flags, tolerant readers, and delayed destructive changes create that window. Backups remain necessary, but they are not a substitute for a release design that can move forward safely.

Prefer roll-forward for transformed data

Once new production writes use a changed representation, reversing the DDL may lose information or require another risky transformation. Define a roll-forward repair for each stage, and reserve application rollback for the period where the compatibility contract is still intact. Test restoring a backup to a separate environment, including the time required and the gap between the snapshot and current transactions.

Verify the operating result

Track query latency, error rates, row counts, queue depth, and replication health during the rollout. Compare the backfilled data against source invariants before removing old columns or constraints.

Migration runbook

• Name an owner, reviewer and operator with authority to pause the rollout.
• Record pre-change row counts, checksums or domain-level invariants.
• Set thresholds for lock waits, request latency, error rate, disk use and replica lag.
• Deploy application compatibility before structural enforcement.
• Keep destructive cleanup out of the initial release.
• Retain a timestamped audit of each batch and schema version.

The safest migration is rarely the shortest SQL file. It is the sequence that lets old and new PHP processes coexist, makes progress measurable, and provides a controlled decision at every irreversible boundary.

After cleanup, update the schema documentation and remove temporary dual-read, dual-write and feature-flag code. Compatibility scaffolding that remains indefinitely becomes another data path to debug. Keep the migration record, validation totals and operational notes so the next change starts from the real production history rather than an outdated diagram.