Migrate: pq: relation "schema_migrations" does not exist in line 0: TRUNCATE "schema_migrations"

[zemirco]

Hey,

I've recently upgraded from version 3 to the latest version v4.3.1. Since then I'm getting the error.

pq: relation "schema_migrations" does not exist in line 0: TRUNCATE "schema_migrations"

I looked around at StackOverflow and here in existing issues. I think it might have something to do with search_path but so far I couldn't find a solution that works for me.

I have set up a demo repo with Circle CI integration which shows my problem. Here is my code https://github.com/zemirco/golang-migrate-err and here is the failing test https://circleci.com/gh/zemirco/golang-migrate-err/2.

Migrate Version
v4.3.1

Loaded Source Drivers
file

Loaded Database Drivers
postgres

Go Version
go version go1.12 linux/amd64

Here is my demo code

func main() {
    source := "user=postgres host=localhost dbname=postgres sslmode=disable"
    db, err := sql.Open("postgres", source)
    if err != nil {
        panic(err)
    }
    if err := db.Ping(); err != nil {
        panic(err)
    }
    driver, err := postgres.WithInstance(db, &postgres.Config{})
    if err != nil {
        panic(err)
    }
    migration, err := migrate.NewWithDatabaseInstance("file://migrations", "postgres", driver)
    if err != nil {
        panic(err)
    }
    if err := migration.Drop(); err != nil {
        panic(err)
    }
    if err := migration.Up(); err != nil {
        panic(err)
    }
}

The Up migration fails. The funny thing is that when I only run Drop without Up OR only Up without Drop everything works. However during development it's nice to start with a clean db every time. So I'd like to use both.

[dhui]

This sounds like an issue that was introduced in v4.3.0.
Namely, after Migrate.Drop() is called, the schema version table is no longer recreated.

Maybe we should rethink how/when the schema version table is created...

In the meanwhile, you can create a new Migrate instance, to run your migrations after running Migrate.Drop()

[aignas]

This sounds like an issue that was introduced in v4.3.0.
Namely, after Migrate.Drop() is called, the schema version table is no longer recreated.

Maybe we should rethink how/when the schema version table is created...

In the meanwhile, you can create a new Migrate instance, to run your migrations after running Migrate.Drop()

This does indeed break the behaviour we depended on in our service and I think having Up recreate the migrations table if it does not exist, as we were using Drop() and Up() to reset the db state in our integration tests and it was working very well so far.

Are there any downsides to doing it this way? Could you maybe explain a little bit more on how the v4 behaviour results in better flows?

[dhui]

Are there any downsides to doing it this way? Could you maybe explain a little bit more on how the v4 behaviour results in better flows?

There are issues with how the db locks are managed by migration which probably can't be addressed w/o a breaking change. e.g. the locks are managed by migrate except when the schema version table is created, then it's managed by the db driver

One idea is to make the db driver explicitly support schema version table creation (probably in the new driver interface) so the locks can be fully managed by the migrate again. Then we can explore the best places in the migration workflow to ensure the schema version table.

For efficiency reasons, we probably don't want to re-create or check schema version table on every run or invocation. Maybe every we only check it on every Up() run?
We don't want to do it on Drop() since that's suppose to leave the db in a clean state.

[mdere-unbound]

Hello so I am running into this issue and pq: relation "schema_migrations" does not exist in line 0: TRUNCATE "schema_migrations" too - but it does create my tables and schemas, at this point should I always see this error message when I up until the above mentioned is thought through and fixed?

also based on my pgadmin dashboard I see the following after up

[ankur-anand]

In the meanwhile, you can create a new Migrate instance, to run your migrations after running Migrate.Drop()

Two migrate instances also result in the same error.

[matheuss-leonel]

Does anyone know any solutions to this issue?

[carlows]

I use two migrate instances (only for my test environment):

m1, _ := migrate.New(migrationURL, pgURL)

err = m1.Drop()
if err != nil && err.Error() != "no change" {
    log.Fatal(err)
}

m2, _ := migrate.New(migrationURL, pgURL)

err = m2.Up()
if err != nil && err.Error() != "no change" {
    log.Fatal(err)
}

[matheuss-leonel]

I use two migrate instances (only for my test environment):

m1, _ := migrate.New(migrationURL, pgURL)

err = m1.Drop()
if err != nil && err.Error() != "no change" {
  log.Fatal(err)
}

m2, _ := migrate.New(migrationURL, pgURL)

err = m2.Up()
if err != nil && err.Error() != "no change" {
  log.Fatal(err)
}

Thanks for trying to help me, but I was looking for a more concise way to do this. I don't think that creating a new connection is the solution, as it can lead to other issues.

(Instead of comparing Error() strings, you can just use the migrate.ErrNoChange)

[trevoro]

I was running into this and was sort of confused as to what was going on. It turns out that because I created my initial migrations with pg_dump it had prepended some information about setting the search_path. pg_dump nullifies the search path and then explicitly creates all the tables with their schema prefix after that. My understanding is that because of the way migrate is running all of this stuff it's just using the search path. In other words, if you have this one line from the dump in your migration it will result in this error.

SELECT pg_catalog.set_config('search_path', '', false);

Just grep for that in your migrations and get rid of it if that's the case.
You _most likely_ don't need that in there, and your migrations will still work properly.

[dhui]

My understanding is that because of the way migrate is running all of this stuff it's just using the search path.

@trevoro Your understanding is correct. Thanks for sharing your solution!