Dirty database version error when using golang-migrate

Question

I have run some migrations that executed with success.

I am on development mode so I want to fore re-run the migrations so in psql shell and after connecting to my database, I executed drop database schema_migrations

The problem now is that when I run the code that executes the migrations (shown below)


func RunMigrations() {
    m, err := migrate.New(
        "file://db/migrations",
        "postgres://postgres:postgres@localhost:5432/mydatabase?sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    if err := m.Up(); err != nil {
        if err.Error() == "no change" {
            log.Println("no change made by migration scripts")
        } else {
            log.Fatal(err)
        }
    }
}

I get this error

Dirty database version 2. Fix and force version.

What is this error about and how can I address this?

score 23 · Accepted Answer · answered Jan 06 '20 at 17:37

23

Dirty database version 2 means that you tried to run migration v2 and it failed.

Database can be inconsistent or broken if migration failed.

Rerunning additional migrations on top of broken state is unpredictable and therefore migrations are blocked until you clean up Database.

https://github.com/golang-migrate/migrate/blob/master/FAQ.md#what-does-dirty-database-mean

What does "dirty" database mean?

Before a migration runs, each database sets a dirty flag. Execution stops if a migration fails and the dirty state persists, which prevents attempts to run more migrations on top of a failed migration. You need to manually fix the error and then "force" the expected version.

After you clean up you database you can also open schema_migrations table and change Dirty flag and rollback version number to last migration that was successfully applied.

answered Jan 06 '20 at 17:37

Dmitry Harnitski

5,838
1
28
43

But why dropping `schema_migrations` table does not solve the problem? – pkaramol Jan 06 '20 at 18:11
1

Migrations should be safe. Database without `schema_migrations` table interpreted as database that is not covered by migrations. – Dmitry Harnitski Jan 06 '20 at 18:18
1

If the migrations table is not present, go-migrate would know of the last successful change that it had applied. In that case, go-migrate would start from beginning and try to upgrade to the latest version. – not-a-robot Aug 07 '20 at 05:10
2

Unrelated: If multiple services use the same database, a good practice would be specify a migrations table that should be used by go-migrate. Relying on the same table, you would not be able to upgrade tables for separate services from different places. – not-a-robot Aug 07 '20 at 05:11
1

Note that, In case, you want to skip the migration which has an issue, just set the `schema_migrations` entry dirty flag to false & redeploy your service. – Ashutosh Chamoli Jun 09 '21 at 08:03

score 1 · Answer 2 · answered Mar 02 '23 at 15:31

Go to your postgres console and find out the details in the Schema_Migrations table.

If you see Dirty=true then you have found your root cause, you just need to update the same by running an update query.

Have a look at the below commands.

select * from schema_migrations;

update schema_migrations set dirty =false where version=XXXX;

score 0 · Answer 3 · answered Aug 19 '22 at 10:31

I resolved this error by updating dirty flag to zero. In my case, my database name was "auth". So, I ran the below command first to check the dirty version.

select * from auth_migrations

In the output I found that column dirty has a value 1.

Then I have updated it to zero and my problem is resolved.

Note: If your database name is "exampleDb" , then your dirty version will be in "exampleDb_migrations"

score 0 · Answer 4 · answered Sep 19 '22 at 15:36

If you drop schema_migrations table, you can recreate it by forcing the version of migrations.

For example

m, err := migrate.New("file://db/migrations","postgres://postgres:postgres@localhost:5432/mydatabase?sslmode=disable")
if err != nil {
    log.Fatal(err)
}
err := m.Force(11) //11 is migrations version number, you may use your latest version
if err != nil {
    return err
}

score 0 · Answer 5 · answered Aug 12 '23 at 19:56

When this happens, you probably need to fix the error manually.

Then, using the CLI tool you can clean the database. Basically you should run: migrate -path PATH_TO_YOUR_MIGRATIONS -database YOUR_DATABASE_URL force VERSION, where N is the current state of the DB.

You can read about it more on the getting started docs

Dirty database version error when using golang-migrate

5 Answers5