Dirty database version 1. Fix and force version #282

duyanghao · 2019-09-04T04:32:22Z

the error appears as below when migrate timeout:

Dirty database version 1. Fix and force version

Steps to Reproduce
Steps to reproduce the behavior:

do migrate timeout
migrate again

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

The text was updated successfully, but these errors were encountered:

dhui · 2019-09-09T14:58:10Z

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 · 2019-09-10T01:56:50Z

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

christopher-kleine · 2019-09-10T08:13:01Z

@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 · 2019-09-12T02:14:43Z

@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 · 2019-09-12T08:55:22Z

@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 · 2019-09-12T09:24:56Z

@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 · 2020-07-20T02:11:11Z

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 · 2020-07-29T04:29:11Z

@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 · 2020-08-03T04:13:53Z

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 · 2020-08-29T15:13:29Z

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 · 2020-09-03T14:46:53Z

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 · 2020-09-04T05:37:24Z

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 · 2020-09-04T05:39:58Z

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

mnvx · 2021-02-24T19:31:25Z

I agree with @tiagosimao, would be great to have option

Allowing disabling the dirty feature

It is not handy to work with dirty migrations on production. In my case migrations executes from docker container with code.

This container cant start until migrations are successfully executed.
So it is not possible to run force command in this container.
I see here two ways and they are both bad (because it is a bit of hell for admins and more large time of services unavailability):
- adapt ci/cd processes to support force command,
- change version directly in database

And my database is Posgtes where DDL queries are support transactions... So best way in my case is as said above

Allowing disabling the dirty feature

xrn · 2021-03-23T20:21:40Z

Regarding that topic also I would like to speak following:

If my migration failed during the Down execution - let's say I would like to rollback step 5 and back to migration 4 and it crash. I will see Dirty database version 5. Fix and force version - Without information regarding the direction even if I am using transactions for each migration I do not know if I should force it to version 4 (this will be a case for broken Up) or force it to version 5 (broken Down)

Currently, there is a blocker for new migrations if there is a dirty flag - thanks to that we can not mess up with db state (making several up and down). What do you think about adding information about the direction which caused that dirty state? Thanks to that user can automate forcing to correct version if is sure that is using transactions. It will be very helpful IMO

joncodo · 2021-12-16T02:30:26Z

If the admins can make a decision here, I will implement it. This is really not a good design and is blocking new users from adoption.

2021/12/15 22:28:06 Applying all down migrations
2021/12/15 22:28:06 error: Dirty database version 1. Fix and force version.

kskenyon · 2022-01-14T00:27:57Z

I'm trying to install Chirpstack with Postgresql on Debian Buster. I'll be danged if I can get past this error when starting the application-server.

level=fatal msg="setup storage error: storage: migrate up error: Dirty database version 27. Fix and force version."

No idea what to do and where to do it. This must be a common problem. Golang related?

joncodo · 2022-01-14T12:11:56Z

I'm trying to install Chirpstack with Postgresql on Debian Buster. I'll be danged if I can get past this error when starting the application-server.

level=fatal msg="setup storage error: storage: migrate up error: Dirty database version 27. Fix and force version."

No idea what to do and where to do it. This must be a common problem. Golang related?

Try this readme I made for our project: https://github.com/merico-dev/lake/blob/migrations-v2/MIGRATIONS.md

kskenyon · 2022-01-14T14:45:48Z

Thanks. I managed to get it working.

…

Sent from my iPad

On Jan 14, 2022, at 7:12 AM, joncodo ***@***.***> wrote: I'm trying to install Chirpstack with Postgresql on Debian Buster. I'll be danged if I can get past this error when starting the application-server. level=fatal msg="setup storage error: storage: migrate up error: Dirty database version 27. Fix and force version." No idea what to do and where to do it. This must be a common problem. Golang related? Try this readme I made for our project: https://github.com/merico-dev/lake/blob/migrations-v2/MIGRATIONS.md — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.

buraksecer · 2022-05-26T19:36:10Z

the error appears as below when migrate timeout:
Dirty database version 1. Fix and force version
Steps to Reproduce Steps to reproduce the behavior:

do migrate timeout

migrate again

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

If you use docker for database, you have to clear database volumes, this is working for me.

CodeNinjaUG · 2022-06-03T18:52:42Z

this is what worked for me
migrate -path db/migrations -database "postgresql://root:8bdc7axyzex@localhost:5432/simple_bank?sslmode=disable" force 1

i had to even drop a database for it work.. but the force version works well

aksenof · 2022-11-16T16:49:58Z

i resolved this error by inserting version with zero dirty flag in the database

in my case, my database name was "schema"

so, check the dirty version:

SELECT * FROM schema_migrations;

in the output i found version = 1 and dirty = 1 and sequence = 1666689290970899911

then i insert new data:

INSERT INTO schema_migrations(version, dirty, sequence) VALUES (1, 0, 1666689299985899911);

congratulations, problem solved!

note: sequence - it's a current datetime in nanoseconds, so, you need the new sequence is larger than the previous one

dominikbraun · 2023-01-26T20:51:50Z

Sorry to be so pointed, but if this is really by design, the design is wrong.

idc77 · 2023-02-22T11:33:37Z

I'm getting

Error running migration: Dirty database version 2. Fix and force version.

with v1.5.4

I have no idea what the previous version was or what to do. Help?

edit: sorry, this is posted in the wrong repo... I was using shiori.

cxMoonGlade · 2023-11-15T03:15:00Z

this is what worked for me migrate -path db/migrations -database "postgresql://root:8bdc7axyzex@localhost:5432/simple_bank?sslmode=disable" force 1

i had to even drop a database for it work.. but the force version works well

Works for me. The hint maybe close every db management application before run the migrate verbose up.
The first version is dirty, which is funny.

tanishasri22 · 2024-06-01T06:36:16Z

I'm getting

Error running migration: Dirty database version 2. Fix and force version.

with v1.5.4

I have no idea what the previous version was or what to do. Help?

edit: sorry, this is posted in the wrong repo... I was using shiori.

@idc77 can you post redirect to the error you mentioned, if you managed to get the solution?

crackc0der · 2024-07-05T11:05:55Z

Can I get more details about "Dirty database"?

This was referenced Sep 4, 2019

adminserver V1.70 initialization failed goharbor/harbor#6672

Closed

postgresql migrate timeout: Dirty database version xxx. Fix and force version goharbor/harbor#8970

Closed

dhui added the wontfix This will not be worked on label Sep 9, 2019

dhui closed this as completed Oct 8, 2019

mknycha mentioned this issue Feb 26, 2020

Dirty Database Troubleshooting Docs #337

Closed

cammellos mentioned this issue Oct 1, 2020

Android 1.6.1 -> 1.7 upgrade error (status-go/protocol migration error) status-im/status-mobile#11249

Closed

gutakk mentioned this issue Feb 18, 2021

RFC: Versioning schema migration tools doesn't support out-of-order migration nimblehq/gin-templates#22

Closed

forana mentioned this issue Mar 7, 2021

Implement a way to upgrade without "dirty" functionality #518

Open

joncodo mentioned this issue Dec 16, 2021

Upgrade for Existing users/DB migrations for existing data apache/incubator-devlake#806

Closed

ZrytyADHD mentioned this issue Jan 23, 2023

Problem upgrade from 2.4.0 to 2.6.0 goharbor/harbor#18122

Closed

Milias mentioned this issue Nov 19, 2023

Initial migration corrupts database on new installs when Kubernetes pod times out coder/coder#10781

Closed

ctfrancia mentioned this issue Mar 20, 2024

Dirty database version 1. Fix and force version and error: can't read limit argument N #1059

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Dirty database version 1. Fix and force version #282

Dirty database version 1. Fix and force version #282

duyanghao commented Sep 4, 2019

dhui commented Sep 9, 2019

duyanghao commented Sep 10, 2019

christopher-kleine commented Sep 10, 2019

duyanghao commented Sep 12, 2019 •

edited

Loading

christopher-kleine commented Sep 12, 2019

duyanghao commented Sep 12, 2019

bkakadiya42 commented Jul 20, 2020

ann-kilzer commented Jul 29, 2020

warent commented Aug 3, 2020

tiagosimao commented Aug 29, 2020

nnachefski commented Sep 3, 2020 •

edited

Loading

dhui commented Sep 4, 2020

dhui commented Sep 4, 2020 •

edited

Loading

mnvx commented Feb 24, 2021

xrn commented Mar 23, 2021

joncodo commented Dec 16, 2021

kskenyon commented Jan 14, 2022

joncodo commented Jan 14, 2022

kskenyon commented Jan 14, 2022 via email

buraksecer commented May 26, 2022 •

edited

Loading

CodeNinjaUG commented Jun 3, 2022 •

edited

Loading

aksenof commented Nov 16, 2022

dominikbraun commented Jan 26, 2023

idc77 commented Feb 22, 2023 •

edited

Loading

cxMoonGlade commented Nov 15, 2023

tanishasri22 commented Jun 1, 2024

crackc0der commented Jul 5, 2024

Dirty database version 1. Fix and force version #282

Dirty database version 1. Fix and force version #282

Comments

duyanghao commented Sep 4, 2019

dhui commented Sep 9, 2019

duyanghao commented Sep 10, 2019

christopher-kleine commented Sep 10, 2019

duyanghao commented Sep 12, 2019 • edited Loading

christopher-kleine commented Sep 12, 2019

duyanghao commented Sep 12, 2019

bkakadiya42 commented Jul 20, 2020

ann-kilzer commented Jul 29, 2020

warent commented Aug 3, 2020

tiagosimao commented Aug 29, 2020

nnachefski commented Sep 3, 2020 • edited Loading

dhui commented Sep 4, 2020

dhui commented Sep 4, 2020 • edited Loading

mnvx commented Feb 24, 2021

xrn commented Mar 23, 2021

joncodo commented Dec 16, 2021

kskenyon commented Jan 14, 2022

joncodo commented Jan 14, 2022

kskenyon commented Jan 14, 2022 via email

buraksecer commented May 26, 2022 • edited Loading

CodeNinjaUG commented Jun 3, 2022 • edited Loading

aksenof commented Nov 16, 2022

dominikbraun commented Jan 26, 2023

idc77 commented Feb 22, 2023 • edited Loading

cxMoonGlade commented Nov 15, 2023

tanishasri22 commented Jun 1, 2024

crackc0der commented Jul 5, 2024

duyanghao commented Sep 12, 2019 •

edited

Loading

nnachefski commented Sep 3, 2020 •

edited

Loading

dhui commented Sep 4, 2020 •

edited

Loading

buraksecer commented May 26, 2022 •

edited

Loading

CodeNinjaUG commented Jun 3, 2022 •

edited

Loading

idc77 commented Feb 22, 2023 •

edited

Loading