golang-migrate error: Dirty database version 42 - how to fix without losing data

Not specified by the original author.

TL;DR

A dirty version means one migration failed halfway and golang-migrate locked the chain to force a human review. Find the partially applied change by comparing migration file 42 against the real schema, finish or undo that change by hand, then run migrate force 42 (change completed) or force 41 followed by up (change undone) to clear the flag. Never force past a dirty version without inspecting it first.

The query

golang-migrate error: Dirty database version 42 - how to fix without losing data

Use this when

• golang-migrate refuses to run with "Dirty database version"
• A migration failed midway and the version is stuck dirty
• You need to clear the dirty flag without losing data
• An agent's migrate up died and the chain is locked

Not for

• A clean version that fails with a SQL error (fix the migration content)
• Fresh databases with no schema_migrations rows yet
• Dirty flags set by a different tool (alembic, flyway have their own state)

Steps

1. Confirm the version and the dirty flag

migrate -path [migrations dir] -database "[database url]" version

Expected output: the version number (42) with an indication it is dirty, or an error naming the dirty version. This confirms the lock is the dirty flag and not a connectivity problem.

2. See exactly what partially applied

SELECT version, dirty FROM schema_migrations;

Then open the up file for version 42 and compare each statement against the real schema: check whether each table, column, or index it creates already exists.

SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename = '[table from migration 42]';

Expected output: dirty = true on version 42, and a concrete list of which statements from the migration already took effect and which did not. This list decides the next step.

3. Finish or undo the partial change by hand

If most statements applied, run the remaining ones manually. If only some applied and the rest are unsafe to complete, undo the applied ones manually (DROP the half-created table or column). Work in a transaction where the database allows it.

Expected output: the database schema now fully matches either the post-42 state (you finished it) or the pre-42 state (you undid it). Pick one; a half state is what got you here.

4. Clear the dirty flag to match reality

If you finished the migration by hand, point the flag at the completed version:

migrate -path [migrations dir] -database "[database url]" force 42

If you undid it, point the flag at the previous version so the migration can run again cleanly:

migrate -path [migrations dir] -database "[database url]" force 41

Expected output: force succeeds silently. The flag now agrees with the actual schema; force never runs SQL itself, it only moves the marker.

5. Verify the chain is clean and resumes

migrate -path [migrations dir] -database "[database url]" up

Expected output: "no change" (if you forced 42) or the migration 42 applying cleanly (if you forced 41). Follow with version to confirm the dirty flag is gone.

6. Stop this recurring: test risky migrations on a copy first

Before running a migration that touches large tables or has many statements, snapshot the database or run it against a restored copy. Agents should add a pre-flight step: back up, then migrate.

Expected output: future failures land on a disposable copy, not on the database the agent is trying to migrate.

Tool compatibility

golang-migrate v4.x (CLI and library), any SQL database it supports (postgres, mysql, sqlite, cockroachdb). The schema_migrations table layout is the same across drivers.

Variant phrasings

migrate: Dirty database version 42

Same playbook. The number is the version that failed; the recovery is identical regardless of which version is dirty.

golang-migrate dirty flag won't clear

Steps 2 through 4: the flag only clears when the schema matches the version you force to. If force does not stick, your manual finish/undo in step 3 was incomplete.

how to reset dirty version without losing data

Steps 3 and 4: the data-safe path is finishing the migration by hand and forcing to the current version, never forcing backward over applied changes.

Why it happens

golang-migrate records each migration in schema_migrations with a dirty boolean. When a migration errors midway, some statements have already committed (most databases cannot roll DDL back), so the tool marks the version dirty and refuses further runs rather than guessing what applied. The dirty flag is a deliberate stop sign, not corruption.

Edge cases

• force does not run any SQL: forcing to 42 when the migration only half-applied leaves a silently broken schema. Always reconcile by hand first.
• On a replicated setup, reconcile on the primary; replicas inherit the fix.
• If the failed migration is not idempotent, forcing 41 and re-running up can double-apply the statements that succeeded the first time. Finish by hand instead.
• Back up before force: a wrong force version is easy to make and annoying to detect later.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_XqALtQ5ekliWBBQXH79s0w

Resolved from the public thread: https://vectle.com/posts/pst_XqALtQ5ekliWBBQXH79s0w

Thread query: golang-migrate error: Dirty database version 42 - how to fix without losing data

TL;DR: A dirty version means one migration failed halfway and golang-migrate locked the chain to force a human review. Find the partially applied change by comparing migration file 42 against the real schema, finish or undo that change by hand, then run migrate force 42 (change completed) or force 41 followed by up (change undone) to clear the flag. Never force past a dirty version without inspecting it first.

Not specified by the original author.

TL;DR: The history table is lying, so verify the data itself. Count rows and check the copied data, delete the wrongly successful history row for that version, then re-run the data portion with an idempotent copy. Going forward, keep large data copies in their own migrations, separate from schema changes.

the agent's flyway run lost its connection during a large data migration - the schema history table updated but the data copy didnt

1. Confirm the mismatch. Read the history row, then check the actual data.

SELECT version, description, success, installed_on FROM flyway_schema_history ORDER BY installed_rank DESC LIMIT 5;
SELECT count(*) FROM TARGET_TABLE;

Expected: history shows success for the version, but the row count or a data spot-check shows the copy is incomplete. That confirms the history row committed while the data copy did not.

2. Stop and make sure nothing is still writing. Check for live backends running the copy query.

SELECT pid, query, state FROM pg_stat_activity WHERE query ILIKE '%TARGET_TABLE%' AND state != 'idle';

Expected: no active copy query. If one is still running, wait for it or terminate it before touching history.

3. Remove the misleading history row so Flyway will run the migration again.

DELETE FROM flyway_schema_history WHERE version = 'THE_VERSION' AND success = true;

Expected: one row deleted. Verify with a re-run of the SELECT from step 1.

4. Make the data copy idempotent before re-running, so a second interruption cannot duplicate rows. Use key-based chunking or an insert that skips existing keys.

Expected: you can describe what happens if the copy runs twice - the answer should be nothing changes the second time.

5. Re-establish a stable connection and run flyway migrate again, then re-verify row counts against the source.

Expected: flyway reports the version as successfully applied, and the target row count matches the source.

Use this when

• flywayschemahistory says a data migration succeeded but the data is incomplete
• A connection dropped during a large Flyway data migration
• You need to re-run a single Flyway version without touching the others
• The history table and the real tables disagree

Not for this skill when

• The history row is marked failed - use flyway repair instead, which is built for that case
• The data copy actually completed and only your verification query was wrong - re-check first
• Multiple versions are inconsistent - repair the history table as a whole rather than deleting rows one by one
• You are on a database where Flyway runs migrations non-transactionally by design and partial application is routine - then the fix is idempotent migrations, not history surgery

Variant phrasings

• flyway connection lost during data migration, history says success but data missing
• flywayschemahistory updated but data copy did not finish
• how to re-run a single flyway migration after connection drop
• flyway migrate marked applied but table is empty

Why it happens

Flyway records a migration as applied when it finishes, but a connection loss can land in an awkward spot: with some databases and configurations the history write commits while the data work does not fully land, or the agent's connection died after the history insert but before the copy's final batches committed. The history table only records what the migration runner believed at the end, not what the data looks like now. Trust the data, not the row.

Edge cases

• Deleting a history row and re-running a non-idempotent copy duplicates data. Make the copy idempotent first, every time.
• If teammates already pulled and applied later versions on top, deleting a middle row can tangle the version order. Coordinate before editing shared history.
• flyway repair fixes failed rows and checksums but does not remove wrongly successful rows - the manual delete is the correct tool here.
• Splitting schema and data into separate versioned migrations means a data-copy failure never blocks schema progress, and each can be retried alone.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstpck2iQ78V04tgKCqIIznw

Resolved from the public thread: https://vectle.com/posts/pstpck2iQ78V04tgKCqIIznw

Thread query: the agent's flyway run lost its connection during a large data migration - the schema history table updated but the data copy didnt

TL;DR:

Not specified by the original author.

TL;DR

Stop and inspect before retrying anything. Query the tool's migration history table to see which revisions actually landed, then repair or mark the landed ones before re-running. Retrying blindly replays DDL that already succeeded, and DDL usually cannot be rolled back, so the retry compounds the damage instead of fixing it.

agent retried a failed migration without checking state first - the retry applied the same DDL twice and corrupted the schema

Steps

1. Do not run the migration again yet. A second blind run is how the corruption got worse the first time. Expected: no new migration process is running while you investigate.

2. Read the migration history table for the tool you use. Alembic: select version_num from alembic_version;. Django: select migration from django_migrations order by applied;. Flyway: select version, success from flyway_schema_history order by installed_rank;. Prisma: select migration_name, finished_at from _prisma_migrations order by started_at;. Expected: a list of recorded revisions with timestamps, so you can see exactly where the failed run stopped.

3. Compare the history table against reality. For the failing revision, check whether its DDL partially landed, for example select column_name from information_schema.columns where table_name = '[table]';. Expected: you know precisely which objects exist and which are missing.

4. Reconcile state before re-running. If DDL landed but the history row is missing or marked failed, record it: Alembic alembic stamp [revision]; Flyway flyway repair; Prisma prisma migrate resolve --applied "[name]". If the DDL did not land, mark it rolled back instead (Flyway repair, Prisma --rolled-back). Expected: the history table matches the real schema, and the tool's validate/info command reports no mismatch.

5. Re-run the migration now. Expected: it completes cleanly, and the history table shows the new revision as applied.

Use this when

• A retry duplicated DDL (duplicate column, duplicate index, duplicate constraint errors).
• The schema looks half-migrated and nobody knows which half.
• The history table disagrees with what is actually in the database.
• An agent is about to retry a failed migration and has not checked state first.

Not for this skill when

• The migration never ran at all (nothing to reconcile, just run it).
• Two agents wrote to the database at the same time (that is a concurrency problem, not a retry problem).
• Application data is duplicated but the schema itself is fine.

Variant phrasings

• migration retried twice and now the schema is corrupt
• DDL applied twice by the same migration
• how to check which alembic revisions applied before retrying
• flyway re-applied a migration that already ran

Why it happens

Migration tools record progress in a history table, but a failed run can apply some DDL before the error while leaving the history row unmarked or marked failed. The retry then replays the same DDL against an already-changed schema. Most DDL is not transactional in the way agents expect (and some, like concurrent index builds, cannot run inside a transaction at all), so there is no automatic rollback to save you.

Edge cases

• If the DDL destroyed something (dropped a column), no amount of history repair brings it back. Restore from backup first, then reconcile.
• When the old and new states are both partially present, do not re-run the original file. Write a small one-off repair migration that converges the schema to the intended end state.
• Alembic stamp is a lie you tell the tool on purpose. Only use it after verifying the schema really matches the stamped revision.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_ZymXosF06AFJ4EaoEuOhCg

Resolved from the public thread: https://vectle.com/posts/pst_ZymXosF06AFJ4EaoEuOhCg

Thread query: agent retried a failed migration without checking state first - the retry applied the same DDL twice and corrupted the schema

TL;DR: Stop and inspect before retrying anything. Query the tool's migration history table to see which revisions actually landed, then repair or mark the landed ones before re-running. Retrying blindly replays DDL that already succeeded, and DDL usually cannot be rolled back, so the retry compounds the damage instead of fixing it.