October 21, 20264 min read

Recover After Only Part of a MySQL Migration Commits

Observe a MySQL column addition surviving a failed index build, correct the duplicate data, and let Ptah resume at the recorded next statement.

Simon HartSolutions Engineer

Ptah helps you plan, review, and apply database migrations. Try in your browser

MySQL committed the new column before it refused the unique index.

That is a possible result of multi-statement MySQL DDL. In this example, Ptah added a carrier column, then MySQL refused a unique index because two orders shared the same tracking code. We inspected the partial state, corrected the synthetic data, and used Ptah’s statement-level resume.

The run used Ptah 0.13.0 and MySQL 8.4.11. The complete example includes the failed retry, successful recovery, and comparison with a fresh replay.

Reproduce the partial change

Version 1 has two orders with the same tracking_code. Version 2 contains:

ALTER TABLE orders ADD COLUMN carrier varchar(20) NOT NULL DEFAULT 'parcel';
CREATE UNIQUE INDEX orders_tracking_key ON orders (carrier, tracking_code);

We applied it with nontransactional execution explicitly selected:

ptah migrations up --db-url "$DB_URL" --migrations-dir migrations --verify-sum --tx-mode none

The command exited 2:

error: error running migrations: failed to apply migration 2: failed to execute migration SQL: Error 1062 (23000): Duplicate entry 'parcel-PKG-001' for key 'orders.orders_tracking_key'
SQL: CREATE UNIQUE INDEX orders_tracking_key ON orders (carrier, tracking_code)

MySQL’s implicit-commit rules mean the earlier DDL need not roll back when a later statement fails. A transaction-shaped migration file is not an atomic schema change merely because its statements appear together.

Inspect the table and recorded progress

The inspection queries read the orders, describe the columns, and list the indexes. They showed carrier present with parcel in both rows. Only the primary-key index existed; the new unique index had failed.

ptah migrations status --db-url "$DB_URL" --migrations-dir migrations --json

The status JSON marked version 2 dirty, with applied: 1, total: 2, and a partial applied checksum. Keep those recorded bytes and the live state together when deciding what can be retried. Replacing the SQL file or manually declaring the version complete would remove the basis for that decision.

We tried --allow-dirty before fixing the duplicate. Ptah resumed at the second statement, but MySQL refused the same duplicate again. The flag allowed a retry; it could not make the data satisfy the proposed index.

Correct the data requirement explicitly

In the synthetic fixture, order 2 should have code PKG-002. We reviewed and applied this correction through the MySQL client:

UPDATE orders SET tracking_code='PKG-002' WHERE id=2 AND tracking_code='PKG-001';
SELECT * FROM orders ORDER BY id;

The rows now had distinct carrier/code pairs. This is a decision about our known fixture, not a general instruction to invent a new tracking code or delete a duplicate in production. A real correction needs the business owner’s explanation of which values are valid.

We left the migration file unchanged and retried:

ptah migrations up --db-url "$DB_URL" --migrations-dir migrations --verify-sum --tx-mode none --allow-dirty

The recorded log excerpt shows where Ptah resumed:

time=2026-10-20T17:44:28.719+02:00 level=INFO msg="Skipping pre-migration checks for partially applied migration" correlation_id=1ed6f6baac22ce7c command=migrations.up version=2 resumeFromStatement=2
time=2026-10-20T17:44:28.999+02:00 level=INFO msg="Resuming migration after a partially applied attempt" correlation_id=1ed6f6baac22ce7c command=migrations.up version=2 resumeFromStatement=2

The first statement was not replayed. Ptah resumed the unique-index creation at statement 2 and completed version 2. The migration application documentation explains the execution controls; use the recorded failure and actual catalog to establish whether they fit your recovery.

Compare the recovered database with the intended result

ptah schema compare --db-url "$DB_URL" --schema-file schema.sql --exit-code

Comparing schema from schema.sql with database mysql://root:***@127.0.0.1:53371/partial
=== SCHEMA COMPARISON ===

No schema differences detected.

The comparison exited 0. A new insert with the same carrier/code pair was refused with MySQL error 1062; the two corrected orders remained. The constraint check and final rows retain that result. Another migration run found no pending version. We also applied the unchanged migration directory to a separate empty MySQL database. Both migrations completed there, and its schema comparison passed too.

Fresh replay verifies that the release files still describe a complete path from an empty database. The recovered database additionally contains the two corrected orders; the empty replay does not prove that the data correction was appropriate for any other dataset.

Recover the state you have

The useful evidence here was concrete: the first DDL persisted, the second failed, the history retained partial progress, and the data violated the new index. After correcting that violation, the recorded resume point let Ptah finish the original file.

A connection loss with uncertain server progress, a changed migration file, or a different engine can require another recovery path. Preserve the error and inspect the state before allowing a retry. The checksum failure article covers changed files, while the PostgreSQL lock article covers a statement that could not acquire its lock.

Get help with an unresolved failure

If these checks do not explain your failure, open an issue for a reproducible defect or start a discussion for help choosing a recovery path. Include the Ptah and database versions, the command, a minimal synthetic fixture, and redacted output. Leave credentials and private production data out of the report.

Put Ptah to work

Example files

← All articles