Migrate: Dirty database version 1. Fix and force version

[duyanghao]

the error appears as below when migrate timeout:

Dirty database version 1. Fix and force version

Steps to Reproduce
Steps to reproduce the behavior:

1. do migrate timeout
2. migrate again

Expected Behavior
migrate again could automatically solve this problem and do migrate normally.

[dhui]

Timeouts may occur and the correct fix is not obvious, so user intervention is necessary.
e.g. The migration could have never applied, partially applied, or fully applied

[duyanghao]

@dhui How to fix Dirty database status? Is there any documents for this?

[christopher-kleine]

@duyanghao Force to a previous version and then run "up" again.

So if you get an error in version 10, fix the error and then force it to version 9. Afterwards you can migrate again.

[duyanghao]

@duyanghao Force to a previous version and then run "up" again.

So if you get an error in version 10, fix the error and then force it to version 9. Afterwards you can migrate again.

@anihex Could you please give an example of fixing Dirty database status as described above?

[christopher-kleine]

@duyanghao Of course I can.

Let's assume I want to create a new table. But I made a "slight" mistake (missing Primary Key).

BEGIN;

CREATE TABLE auth_users (
    id BIGSERIAL,

    name VARCHAR(255),
    superior_id BIGINT REFERENCES auth_users
);

COMMIT;

Now I try to migrate it:

migrate -path migrations/ -database postgres://test:test@localhost/dummy?sslmode=disable up

migrate will refuse this:

error: 2 errors occurred:
    * migration failed: there is no primary key for referenced table "auth_users" in line 0: BEGIN;

CREATE TABLE auth_users (
    id BIGSERIAL,

    name VARCHAR(255),
    superior_id BIGINT REFERENCES auth_users
);

COMMIT;
 (details: pq: there is no primary key for referenced table "auth_users")
    * pq: current transaction is aborted, commands ignored until end of transaction block in line 0: SELECT pg_advisory_unlock($1)

Now I fixed it and added the PRIMARY KEY to the migration:

BEGIN;

CREATE TABLE auth_users (
    id BIGSERIAL PRIMARY KEY,

    name VARCHAR(255),
    superior_id BIGINT REFERENCES auth_users
);

COMMIT;

If I try to migrate again, migrate will STILL refuse:

error: Dirty database version 16. Fix and force version.

So I have to go the last successfull version, which is 15.

migrate -path migrations/ -database postgres://test:test@localhost/dummy?sslmode=disable force 15

If i migrate again, the output will now be:

16/u create_auth_users (17.608619ms)

The same works if you want to go down. But if would be 17 in this case, not 15.
I hope this helps a bit.

[duyanghao]

@duyanghao Of course I can.

Let's assume I want to create a new table. But I made a "slight" mistake (missing Primary Key).

BEGIN;

CREATE TABLE auth_users (
  id BIGSERIAL,

  name VARCHAR(255),
  superior_id BIGINT REFERENCES auth_users
);

COMMIT;

Now I try to migrate it:

migrate -path migrations/ -database postgres://test:test@localhost/dummy?sslmode=disable up

migrate will refuse this:

error: 2 errors occurred:
  * migration failed: there is no primary key for referenced table "auth_users" in line 0: BEGIN;

CREATE TABLE auth_users (
  id BIGSERIAL,

  name VARCHAR(255),
  superior_id BIGINT REFERENCES auth_users
);

COMMIT;
 (details: pq: there is no primary key for referenced table "auth_users")
  * pq: current transaction is aborted, commands ignored until end of transaction block in line 0: SELECT pg_advisory_unlock($1)

Now I fixed it and added the PRIMARY KEY to the migration:

BEGIN;

CREATE TABLE auth_users (
  id BIGSERIAL PRIMARY KEY,

  name VARCHAR(255),
  superior_id BIGINT REFERENCES auth_users
);

COMMIT;

If I try to migrate again, migrate will STILL refuse:

error: Dirty database version 16. Fix and force version.

So I have to go the last successfull version, which is 15.

migrate -path migrations/ -database postgres://test:test@localhost/dummy?sslmode=disable force 15

If i migrate again, the output will now be:

16/u create_auth_users (17.608619ms)

The same works if you want to go down. But if would be 17 in this case, not 15.
I hope this helps a bit.

@anihex Thank for your reply, that really helps a lot.

[bkakadiya42]

Maybe I am missing something here. If we are running migrations within a transaction, why do we need to mark the database as dirty? Asking this because it slows down rapid iterations on the migration if the developer has to force after every failure, and when it is the very first migration you can't force it to the version 000000

[ann-kilzer]

@bkakadiya42 I had the same issue, ended up resetting my entire DB to start clean and then fix my migration file. Hope that helps!

[warent]

It helps bandaid the problem, but it still leaves the question open why a failed transaction makes the database dirty. This must be a bug. The "force" UX is also very user hostile.

[tiagosimao]

Maybe I am missing something here. If we are running migrations within a transaction, why do we need to mark the database as dirty? Asking this because it slows down rapid iterations on the migration if the developer has to force after every failure, and when it is the very first migration you can't force it to the version 000000

Spot on. Error on first migration seems irrecoverable through migrate's interface. I will hack away and drop the schema_migrations table (if dirty && Version==1) but this is clearly an abstraction violation.
Also, all of this is just silly when using Postgres (or any other db with transaction support in schema changes for that matter)

Didn't understood why the issue is marked as resolved, even the initial question refers to error on version 1 but all answers just seem to ignore that detail. Maybe I'm missing something as well. The docs don't seem to scratch this itch tough.

Some possible ways this lib could help the developer in tackling this corner case I can think of:

• Allowing disabling the dirty feature
• Adding method to drop schema_migrations table
• Never setting first version as dirty (probably incoherent, bad idea)

All for helping out, just didn't fully understood if this is something recognized as a problem.
I really (really) don't want to sound like a jerk, but this bug makes the lib unusable in production. I cannot have an application that out of the blue requires a human messing with the DB.

Cheerio!

[nnachefski]

I'm hitting this while deploying harbor with the kube operator.

2020-09-03T14:39:47Z [INFO] [/common/dao/pgsql.go:127]: Upgrading schema for pgsql ...
2020-09-03T14:39:47Z [ERROR] [/common/dao/pgsql.go:132]: Failed to upgrade schema, error: "Dirty database version 10. Fix and force version."
2020-09-03T14:39:47Z [FATAL] [/core/main.go:123]: failed to migrate: Dirty database version 10. Fix and force version.

[dhui]

If we are running migrations within a transaction, why do we need to mark the database as dirty?

still leaves the question open why a failed transaction makes the database dirty

migrate does not enforce this (e.g. does not run all migrations in a transaction) so it cannot automatically rollback upon failure. If all of your migrations are wrapped in a transaction, then you can safely force the migration to the previous applied migration. Otherwise, you'll need to inspect the state of your db and determine how to proceed.

The "force" UX is also very user hostile.

Even if migrate were able to rollback failed migration, user intervention is likely required to fix some state for a successful migration. e.g. re-running the same migration with the same starting state won't succeed
Letting the developer/sysadmin know that something is broken and requires their attention and having them acknowledge that the issue has been resolved is a good thing. However, I agree that the UX could be smoother. e.g. forcing is an extra step vs just trying to re-apply the migration

[dhui]

@nnachefski This looks like an issue with harbor please find/file an issue there.
FWIW, here's their migrations