CODEWITHSUNDEEP
gomigrationgolang-migrate
pkaramol
pkaramol

Reputation: 19342

Dirty database version error when using golang-migrate

I am a new user of golang-migrate.

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?

Upvotes: 29

Views: 55118

Answers (6)

user10737845
user10737845

Reputation: 1

The situation I encountered is that after an error occurs during a migrate operation (e.g., a syntax error), running migrate again causes an issue. My solution is to roll back the value of the version field in the schema_migrations table in the database and set the dirty field to false. For example, if the error is on version 6 and the migration file being executed is 000006XXX.up.sql, I would change the value of the version field to 5 and set dirty to false. After that, I can re-execute the migrate operation successfully.

Upvotes: 0

Chen A.
Chen A.

Reputation: 11280

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

Upvotes: 5

Jacob
Jacob

Reputation: 2414

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;

Upvotes: 2

Izhari Ishak Aksa
Izhari Ishak Aksa

Reputation: 898

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
}

Upvotes: 0

Vineeth Peddi
Vineeth Peddi

Reputation: 544

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"

Upvotes: 0

Dmitry Harnitski
Dmitry Harnitski

Reputation: 6008

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.

Upvotes: 28

Related Questions