Avoiding Implicit Commits in MySQL DDL Migrations

You wrapped three ALTER TABLE statements in BEGIN ... COMMIT, the second one failed, and you expected the whole thing to roll back. Instead production now has the first ALTER applied, the second half-done, and the third never started — and your ROLLBACK did nothing. This is not a bug in your migration tool. It is MySQL doing exactly what it is documented to do: every DDL statement triggers an implicit commit that ends the surrounding transaction before the statement even runs. The transaction you thought protected your migration was silently committed at the first ALTER, so there was nothing left to roll back. The alert that lands engineers here is a migration that failed partway and left the schema in a state the tool’s history table does not recognize. This page explains why the rollback never happened, how to structure migrations so a failure is recoverable, and how to clean up a half-applied one.

Symptom / Error Signatures

An implicit-commit failure looks like a transaction that did not behave like one:

• A multi-statement migration fails on a middle statement, yet earlier statements are still applied after you issue ROLLBACK — the rollback reports success but changes nothing.
• The migration tool marks the version as failed but the schema is partially changed: ERROR 1060 (42S21): Duplicate column name 'region_code' on the retry, because the column from the first attempt is already there.
• Flyway on MySQL reports Migration ... failed ... Changes successfully rolled back when in fact the DDL committed and nothing rolled back — the message reflects the tool’s assumption, not MySQL’s behavior.
• SHOW CREATE TABLE reveals columns or indexes from a migration the history table records as not successfully applied.
• A retried migration fails on ALTER TABLE ... ADD COLUMN because the column already exists from the half-applied first run.

The general behavior of DDL that cannot participate in a transaction is the subject of Handling Non-Transactional DDL in MySQL Migrations; this page focuses specifically on the implicit commit and how it defeats multi-statement rollback.

Root Cause Analysis

MySQL’s InnoDB engine is transactional for DML but not for DDL. Any data-definition statement — CREATE TABLE, ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE, and others — causes an implicit commit of any open transaction before the statement executes, and a second implicit commit after. There is no way to defer it: autocommit=0 does not help, and wrapping the DDL in START TRANSACTION is silently ineffective because the START TRANSACTION is committed away the instant the first DDL runs. The consequence is that a migration containing several DDL statements is really several independent transactions, each committed on its own. If statement two fails, statement one is already durably committed and statement three never ran — the migration is left in a state with no atomic boundary around it.

PostgreSQL is the opposite: its DDL is fully transactional (with the notable exception of CREATE INDEX CONCURRENTLY), so wrapping several ALTERs in BEGIN ... COMMIT gives true all-or-nothing semantics. Code and intuition built on PostgreSQL break silently on MySQL because the same migration that is atomic on one engine is a sequence of point-of-no-return commits on the other. This is the single most important line in the Transactional vs Non-Transactional Databases split.

Because MySQL cannot give you atomicity across statements, the only safe unit of change is a single DDL statement per migration, made re-runnable so a retry is a no-op rather than a duplicate-column error.

Immediate Mitigation

If a multi-statement MySQL migration just failed partway, do not blindly retry — the first statements are already live.

1. Inspect the real schema, not the tool’s history. The history table and the database disagree after a partial DDL failure; trust SHOW CREATE TABLE.

-- MySQL 8.0 · read-only · reveals what actually applied, regardless of the history table
-- Compare this against the migration to see which statements committed.
SHOW CREATE TABLE orders\G

2. Reconcile the tool’s history with reality. If the first ALTER committed but the version is marked failed, either repair the history row or manually finish the remaining statements — never let the tool re-run the whole script blindly.

# Shell · run with a migration role · repairs Flyway's history to match actual schema
# Use AFTER you have confirmed by hand which statements are already applied.
flyway repair

3. Make the remaining statements idempotent before re-running, so the already-applied ones become no-ops instead of duplicate-column errors. MySQL 8.0 lacks ADD COLUMN IF NOT EXISTS in older minors; guard with a conditional check.

-- MySQL 8.0 · run as migration role · DDL implicit-commits, so run ONE statement at a time
-- Guard each ALTER so a retry of an already-applied statement is a safe no-op.
SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_NAME='orders' AND COLUMN_NAME='region_code' AND TABLE_SCHEMA=DATABASE());
SET @sql := IF(@col=0, 'ALTER TABLE orders ADD COLUMN region_code VARCHAR(8)', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

4. Re-run the migration once the schema and history agree. With guards in place, the retry applies only the statements that have not yet committed.

Permanent Fix / Long-Term Pattern

Stop relying on transaction atomicity that MySQL does not provide. The durable fix is structural: make each migration a single, idempotent, independently recoverable DDL statement. This is the MySQL-specific application of the recoverability contract in Transactional vs Non-Transactional Databases and the guard techniques in How to Write Idempotent SQL Scripts for Safe Deploys.

One DDL statement per migration. Because MySQL commits after every DDL, a migration with one statement has the same atomicity guarantee as a single implicit commit — there is no partial state to leave behind. Split a three-ALTER change into three migrations. The tool’s history table then records each as applied independently, so a failure on the third leaves the first two cleanly applied and the third simply unrun, which is a recoverable state.

Every DDL migration is idempotent. Guard each statement so re-running it is a no-op, using CREATE TABLE IF NOT EXISTS, DROP ... IF EXISTS, or the information_schema check above for column and index adds. A retry then never fails on “already exists,” which is what makes one-DDL-per-migration safe to re-run after a transient failure.

Order migrations so each is independently safe. Sequence them so that applying any prefix of the chain leaves a working schema — additive first, destructive last, following the expand-then-contract ordering. Then a partial failure always halts at a consistent boundary.

-- MySQL 8.0 · one statement, idempotent · safe to re-run after any transient failure
-- This whole migration is a single implicit commit, so it is atomic by construction.
CREATE TABLE IF NOT EXISTS order_region (
  order_id   BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  region_code VARCHAR(8) NOT NULL,
  CONSTRAINT fk_order_region FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB;

Verification Checklist

• Each migration contains exactly one DDL statement so it is atomic by construction under MySQL’s implicit commit.
• Every DDL statement is guarded (IF NOT EXISTS, IF EXISTS, or an information_schema check) so a retry is a no-op.
• No migration relies on START TRANSACTION/ROLLBACK to undo DDL on MySQL.
• After a deliberately failed test migration, the schema and the tool’s history table agree, or flyway repair reconciles them.
• Migrations are ordered so any applied prefix leaves a working schema (additive before destructive).
• A retry of a half-applied migration completes without a duplicate-column or already-exists error.

Frequently Asked Questions

Why did my ROLLBACK not undo the MySQL ALTER TABLE? Because DDL in MySQL forces an implicit commit before it runs, the transaction you opened with START TRANSACTION was already committed by the time the ALTER executed. There was no open transaction left, so ROLLBACK had nothing to reverse. MySQL DDL is simply not transactional; you cannot roll it back, only apply a compensating change.

How do I make a multi-step MySQL migration atomic? You cannot make several DDL statements atomic together on MySQL — each commits independently. Instead, split the change into one DDL statement per migration and make each idempotent. The history table then tracks each step, so a failure stops at a clean boundary and a retry resumes from there without corrupting earlier steps.

Does setting autocommit=0 prevent the implicit commit? No. The implicit commit on DDL is unconditional; autocommit=0 controls only DML auto-commit behavior. Any CREATE, ALTER, DROP, RENAME, or TRUNCATE will commit the current transaction regardless of the autocommit setting. The only safe pattern is one idempotent DDL statement per migration.