error: pq: column "dirty" does not exist in line 0

[raarts]

Hi,

I googled this error, the original repo has something on it, but it's not clear how to fix it.
I am using the binary from this repo:

go get -u -d github.com/golang-migrate/migrate/cli github.com/lib/pq
go build -tags 'postgres' -o /usr/local/bin/migrate github.com/golang-migrate/migrate/cli

With only this migration, on an empty postgres database:

BEGIN;
CREATE TABLE user (
  id uuid not null,
  sub varchar(255) not null,
  secret varchar(255),
  given_name varchar(255),
  family_name varchar(255),
  email varchar(255) not null,
  inserted_at timestamp without time zone not null default now(),
  updated_at timestamp without time zone not null default now()
);
create unique index user_id_index on user (id);
create unique index person_sub_index on person (sub);
COMMIT;

and it bombes out with the above error.

[dhui]

Looks like corruption w/ the schema migrations tracking table. My guess is that the postgres DB isn't actually empty. What's listing the tables return?

Try dropping the schema_migrations table or starting w/ a fresh DB: https://github.com/mattes/migrate/tree/master/database/postgres#upgrading-from-v1

[raarts]

@dhui That did not help, but I've got something else. I switched from postgres to sqlite, and an interesting error popped up: error: cannot start a transaction within a transaction in line 0: BEGIN;

Indeed I wrapped my migrations in BEGIN/COMMIT. After removing that, migrate does not complain any more, at least on sqlite.

Quite unfortunate that this error does not show when using postgres.

[dhui]

migrate has build issues with sqlite3, but it looks like you were able to get around that to test!

The issue you're encountering on postgres might be due to the advisory lock being held or failing statements not being rolledback. The latter should be fixed in this repo.

Reference: mattes/migrate#274

There's an open PR that fixes the advisory lock issue, but it's blocked by passing tests: #13

What's your schema_migrations table look like in postgres?

[raarts]

Well, I'm off postgres now, and sqlite works better for my use-case, but this is the query I had to perform to get things working again on postgres:

alter table schema_migrations add column dirty boolean not null default false;

Note I was deploying all this from a CI/CD pipeline, and my problems appeared on the test deploy which starts without databases, migrate being the only one touching the database, and the migration creating a single table. (it's for a microservice).

I grepped through the source code, but could not find a case where the dirty column was not created???

I am baffled. If I have time today or tomorrow, I'll do some more digging.

BTW here's my Dockerfile:

FROM golang:1.10-alpine
 
# Install migrate for db migrations
RUN apk add --no-cache --virtual .go-build git autoconf build-base \
 && go get -u -d github.com/golang-migrate/migrate/cli github.com/lib/pq github.com/mattn/go-sqlite3 \
 && go build -tags 'postgres sqlite3' -o /usr/local/bin/migrate github.com/golang-migrate/migrate/cli \
 && apk del --force .go-build

I didn't have any problem with sqlite. Can you take a good look at my build commands?

[dhui]

The migrate postgres db driver creates the schema_migrations table if one doesn't already exist.
Note, you'll need to create the DB, migration user/role, and grants before running migrate.

[dhui]

Closing due to inactivity. Please reopen if the issue persists

[bboreham]

I appear to have the same problem, using Postgres: cortexproject/cortex#1972
This is a table that is in use, in production, and has been through schema migrations before, using earlier versions of this library.

If you have any advice to troubleshoot, that would be helpful.