Can't acquire lock on "manual" migrations

[josephbuchma]

Using MySQL
This code (cut from test):

        // ...
        t.Log("mg.Version():")
        t.Log(mg.Version())
        err = mg.Migrate(1)
        if err != nil {
                t.Errorf("Migrate(1) err: %s", err)
        }
        t.Log("mg.Version():")
        t.Log(mg.Version())

        // up
        err = mg.Migrate(2)
        if err != nil {
                t.Errorf("Migrate(2): %s", err)
        }
        t.Log("mg.Version():")
        t.Log(mg.Version())

        err = mg.Down()
        if err != nil {
                t.Errorf("Down err: %s", err)
        }

Results in following output:

--- FAIL: TestMigrate (3.12s)
        migrate_test.go:58: mg.Version():
        migrate_test.go:59: 0 false no migration
        migrate_test.go:64: mg.Version():
        migrate_test.go:65: 1 false <nil>
        migrate_test.go:70: Migrate(2): can't acquire lock
        migrate_test.go:72: mg.Version():
        migrate_test.go:73: 1 false <nil>
        migrate_test.go:77: Down err: can't acquire lock

May be related to #220
And is not direclty related to #235 for sure (I do not run migrations concurrently, and in my case it is 100% reproduceable)

[mattes]

Might be related to #274 .

How do you run the tests?

[josephbuchma]

go test -v -run TestMigrate

Migrations are basic create / drop table without transactions.

Complete test code:

func TestMigrate(t *testing.T) {
        migrationsDir, cleanup := setupTestMigrations() // creates few up-down *.sql pairs
        defer cleanup()

        var err error

        db, err = sql.Open("mysql", "root:@tcp(localhost:3306)/migrations_test?multiStatements=true")
        if err != nil || db == nil {
                t.Fatalf("failed to open database connection: %s", err)
        }

        dbdrv, err := mysql.WithInstance(db, &mysql.Config{})
        if err != nil {
                t.Fatalf("mysq.WithInstance error: %s", err)
        }

        fsrc, err := (&file.File{}).Open("file://" + migrationsDir)
        if err != nil {
                t.Fatalf("Failed to open migrations source: %s", err)
        }

        mg, err := migrate.NewWithInstance(
                "file",
                fsrc,
                "mysql",
                dbdrv,
        )
        if err != nil {
                t.Fatalf("Failed to initialize Migrate: %s", err)
        }

        t.Log("mg.Version():")
        t.Log(mg.Version())
        err = mg.Migrate(1)
        if err != nil {
                t.Errorf("Migrate(1) err: %s", err)
        }
        t.Log("mg.Version():")
        t.Log(mg.Version())

        // up
        err = mg.Migrate(2)
        if err != nil {
                t.Errorf("Migrate(2): %s", err)
        }
        t.Log("mg.Version():")
        t.Log(mg.Version())

        err = mg.Down()
        if err != nil {
                t.Errorf("Down err: %s", err)
        }
}

[ernsheong]

Any help with this? I am getting can't acquire lock error on Postgres when trying to run migrations (could be no new migrations pending) on a new app instance while an existing one is already present (this is for rolling restart).

Killing the current app instance first and then running migrations on the new instance works fine. migrate is being run as a lib.

Possible workarounds?

[Teddy-Schmitz]

See my issue #296 the postgres driver is unable to unlock properly. What we have done is setup a different db conn just to migrate and close it after migration. Closing the connection releases the lock.

[josephbuchma]

I switched to https://github.com/db-journey/migrate (which is hard fork of this repo), primarily because it supported timestamp-based migrations versioning, where you don't need to ensure that your migration version is higher than current db schema version. But it evolved into major contribution, where I refactored code, removed all redundant complication with channels, simplified Driver interface and added few new features, including database locking during migrations. Now it's much more contribution-friendly, take a look @mattes

[miguelmota]

Getting can't acquire lock error as well.