## 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

```text
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

```bash
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

```sql
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.

```sql
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:

```bash
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:

```bash
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

```bash
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