Writing Idempotent MySQL Migrations Without IF NOT EXISTS

A MySQL migration with three ALTER TABLE statements failed on the third because of a lock-wait timeout. The runner retried it, and the retry failed on the first statement: ERROR 1060 (42S21): Duplicate column name 'region'. The first two statements had already committed — MySQL DDL commits implicitly — so the migration is now half-applied and cannot be rerun. On PostgreSQL you would reach for ADD COLUMN IF NOT EXISTS; on MariaDB too. MySQL 8.0 supports neither ADD COLUMN IF NOT EXISTS nor CREATE INDEX IF NOT EXISTS, so idempotency has to be built by hand. This guide shows the guard patterns that make MySQL migrations safe to rerun, applying the principles of Idempotent Script Design to an engine that gives you fewer tools.

Which Guards MySQL 8.0 Gives You Matrix of DDL operations against native MySQL 8.0 guard support and the workaround needed. CREATE TABLE and DROP TABLE support IF [NOT] EXISTS natively. ADD COLUMN, DROP COLUMN, CREATE INDEX, DROP INDEX, and ADD CONSTRAINT have no native guard and need an information_schema check with a prepared statement. Which Guards MySQL 8.0 Gives You Operation Native guard in MySQL 8.0 Workaround CREATE TABLE IF NOT EXISTS none needed DROP TABLE IF EXISTS none needed ADD COLUMN none information_schema.COLUMNS check DROP COLUMN none information_schema.COLUMNS check CREATE / DROP INDEX none information_schema.STATISTICS check ADD CONSTRAINT (FK, CHECK) none information_schema.TABLE_CONSTRAINTS check
Only table-level statements have native guards in MySQL 8.0; every column, index and constraint change needs an explicit catalog check.

Symptom / Error Signatures

A MySQL migration that is not idempotent fails on rerun with one of these:

ERROR 1060 (42S21): Duplicate column name 'region'
ERROR 1061 (42000): Duplicate key name 'idx_orders_region'
ERROR 1091 (42000): Can't DROP 'legacy_code'; check that column/key exists
ERROR 1826 (HY000): Duplicate foreign key constraint name 'fk_orders_customer'
ERROR 3822 (HY000): Duplicate check constraint name 'chk_orders_amount'

In a migration tool the same failure appears as a failed version that the tool will not move past — Flyway marks it failed in flyway_schema_history, golang-migrate sets the dirty flag — and an operator must repair it by hand. That manual repair, under incident pressure, is where most migration mistakes happen.

Root Cause Analysis

MySQL executes each DDL statement in its own implicit transaction: it commits any open transaction before the statement and commits the statement’s effect immediately after. A multi-statement migration is therefore a sequence of independently committed steps, and a failure on step N leaves steps 1 to N−1 applied, as described in avoiding implicit commits in MySQL DDL migrations. Retrying from the top then collides with what already exists.

Idempotency fixes this without transactions: if every statement checks whether its effect is already present and does nothing if so, rerunning the whole migration converges on the same end state regardless of where the previous attempt stopped. MySQL’s catalog in information_schema has everything needed for those checks — COLUMNS, STATISTICS for indexes, TABLE_CONSTRAINTS and REFERENTIAL_CONSTRAINTS for constraints. The awkward part is that plain SQL has no conditional DDL, so the check must decide which statement to run via a prepared statement or a stored procedure.

Rerun After a Mid-Migration Failure Two runs of a three-step migration. In the unguarded first run, step 1 and step 2 commit, step 3 fails. The unguarded retry fails immediately on step 1 with duplicate column. In the guarded retry, steps 1 and 2 detect their effects already exist and skip; step 3 runs and succeeds. Rerun After a Mid-Migration Failure Step 1 ADD COLUMN commit 1060 dup skip Step 2 ADD INDEX commit skip Step 3 ADD FK timeout commit attempt 1 unguarded retry guarded retry applied error guarded skip
Implicit commits make partial application unavoidable on MySQL; guards make it harmless.

Immediate Mitigation

1. Repair the half-applied migration by finishing it, not reverting it. Compare what the migration intended with what exists, and run only the missing steps by hand. Then mark the version as applied in the tool’s history (for Flyway, flyway repair removes the failed entry so the fixed file can run again; for golang-migrate, migrate force <version> clears the dirty flag).

2. Guard column changes with a catalog check and a prepared statement. The pattern chooses between the real DDL and a harmless no-op:

-- MySQL 8.0 · migration session · requires ALTER on shop.orders and SELECT on information_schema
-- WARNING: DDL still commits implicitly; the guard only makes a rerun safe, not the step atomic.
SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'region') = 0,
  'ALTER TABLE orders ADD COLUMN region VARCHAR(32) NULL, ALGORITHM=INSTANT',
  'DO 0');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- ROLLBACK PATH: same guard with COUNT(*) = 1 and 'ALTER TABLE orders DROP COLUMN region'.

3. Guard indexes and constraints the same way. Index names live in information_schema.STATISTICS; foreign keys and check constraints in TABLE_CONSTRAINTS.

-- MySQL 8.0 · migration session · online index build, guarded by name
SET @ddl := IF(
  (SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND INDEX_NAME = 'idx_orders_region') = 0,
  'ALTER TABLE orders ADD INDEX idx_orders_region (region), ALGORITHM=INPLACE, LOCK=NONE',
  'DO 0');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- ROLLBACK PATH: guarded 'ALTER TABLE orders DROP INDEX idx_orders_region, ALGORITHM=INPLACE, LOCK=NONE'.

4. Keep one DDL statement per migration where the tool allows it. Even with guards, a migration that contains one DDL statement has only two states — applied or not — which makes failures trivial to reason about.

Permanent Fix / Long-Term Pattern

Wrap the guard in a small set of stored procedures created once by the migration tooling, so individual migrations read cleanly and cannot get the catalog query subtly wrong. A procedure such as add_column_if_missing(table, column, definition) builds and executes the statement only when the column is absent. Install the helpers in a schema the migration role owns, and version them like any other migration.

-- MySQL 8.0 · one-time helper, installed by an early migration · requires CREATE ROUTINE
-- WARNING: the definition string is concatenated into DDL; call it only from reviewed migrations.
DELIMITER //
CREATE PROCEDURE add_column_if_missing(IN p_table VARCHAR(64), IN p_column VARCHAR(64), IN p_def TEXT)
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table AND COLUMN_NAME = p_column) THEN
    SET @ddl = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_def);
    PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  END IF;
END //
DELIMITER ;
CALL add_column_if_missing('orders', 'region', 'VARCHAR(32) NULL, ALGORITHM=INSTANT');
-- ROLLBACK PATH: DROP PROCEDURE IF EXISTS add_column_if_missing;

Name-based guards check that something with the name exists, not that it matches the intended definition. Pair them with a post-migration verification — compare the resulting schema against the expected one, as in detecting production schema drift against a desired state — so an index with the right name and wrong columns does not slip through. Test the whole approach by running every migration twice in CI, as described in how to write idempotent SQL scripts for safe deploys.

Guarded Statement Lifecycle Four steps for each guarded DDL: query information_schema for the object; build either the DDL or a DO 0 no-op; execute the prepared statement; after the migration, verify the object's definition matches the expected schema. Guarded Statement Lifecycle STEP 1 Check catalog COLUMNS / STATISTICS / TABLE_CONSTRAINTS STEP 2 Choose statement DDL or DO 0 STEP 3 Execute PREPARE / EXECUTE STEP 4 Verify definition schema diff after run
The guard answers "does it exist?"; the post-migration verification answers "is it the right thing?" — you need both.

Verification Checklist

Frequently Asked Questions

Does MySQL 8.0 support ADD COLUMN IF NOT EXISTS? No. MySQL 8.0 supports IF NOT EXISTS and IF EXISTS for creating and dropping tables, databases, views and some other objects, but not for adding or dropping columns and indexes. MariaDB does support them, which is a common source of confusion.

Why use DO 0 as the no-op? PREPARE needs a valid statement in both branches. DO 0 evaluates an expression and returns nothing, takes no locks and causes no implicit commit, so it is the cheapest possible placeholder.

Can I wrap the MySQL migration in a transaction instead? No. DDL statements cause an implicit commit before and after they run, so a surrounding START TRANSACTION does not make them atomic. Idempotent guards are the reliable substitute.

Do these guards affect online DDL? No. The guard runs a quick catalog query and then executes the same ALTER TABLE you would have written, including its ALGORITHM and LOCK clauses. Metadata-lock behaviour and the need for a short lock_wait_timeout are unchanged.

Related Articles