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

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

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.

Not specified by the original author.

TL;DR: Let the migration tool's state table do the dedup - that's what it's for. Read the applied set, fix the cause of the failure (or repair its failed row), and re-run the migrate command. Applied migrations are skipped by version and checksum; only the pending ones run.

The problem as reported:

agent migrated half the tables to the new schema then hit an error - how to re-run safely without double-applying

1. Ask the tool what's applied and what's pending: flyway info, alembic current, npx prisma migrate status, python manage.py showmigrations, or rails db:migrate:status. Expected: two clear lists - applied and pending.
2. Investigate the error on the first pending migration and fix the cause (bad SQL, missing extension, lock timeout, bad data assumption). Expected: a named, fixed root cause - not a retry of the same failure.
3. If the tool recorded a failed row, resolve it before re-running: for prisma, npx prisma migrate resolve --rolled-back "MIGRATION_NAME" after manually cleaning partial effects; for flyway, flyway repair. Expected: the failed entry is gone and the migration shows as pending again.
4. Re-run the migrate command. Expected: output shows only the pending migrations applying, in order, ending with the database up to date.
5. If the migration moved data, verify with row counts or a schema diff against a known-good environment. Expected: counts match, schema matches.

Use this when

• A migration run died partway and you're afraid of double-applying.
• You need to resume a half-finished migration safely.
• The tool's state table and your notes disagree - trust the table.

Not for this skill when

• Migrations are hand-rolled SQL with no state tracking - then there's no dedup to lean on and you need manual bookkeeping.
• The error is a data problem inside an already-applied migration - that's a repair migration, not a resume.

Variant phrasings

• resume migration without double applying
• migration failed halfway safe to re-run
• will migrate re-run applied migrations
• continue migration after error

Why it happens

Runners stop at the first error by design, and the applied set is durable in the state table. Re-running is safe because the tool skips what's applied - the fear of double-apply comes from not trusting that mechanism.

Edge cases

• Non-transactional DDL (MySQL) can leave a migration half-applied with no clean row to repair - reconcile the schema by hand first.
• A migration file edited after it applied: the next run fails checksum validation; ship the change as a new migration.
• Concurrent runners against the same database: use the tool's locking (advisory locks, single runner) so two agents don't interleave.

Provenance

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

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

Thread query: agent migrated half the tables to the new schema then hit an error - how to re-run safely without double-applying

TL;DR: