-lock-timeout option doesn't work with CockroachDB

[andreis]

I came across this while launching multiple services at the same time (they run migrations on startup using the migrate cli, calling it from Go doesn't work for CRDB either but that's a different story).

I replicated it by running up migrations at the same time in 2 different terminals.

➜  sql git:(master) ✗ migrate -path . -lock-timeout 100 -database cockroach://root@localhost:26257/redacted?sslmode=disable up
error: lock could not be acquired; already locked in line 0: SELECT * FROM schema_lock WHERE lock_id = $1 (details: <nil>)

[andreis]

In case anyone else is affected by this, you can easily migrate this by retrying. Here is my code, using backoff.

func Migrate(dsn string, service string) DBSetupOpt {
    return func(logger *logrus.Logger, _ *sql.DB) error {
        if dsn == "" {
            return errors.New("no dsn specified")
        }
        if service == "" {
            return errors.New("no service specified")
        }

        // This is needed because the migrate tool recognizes 'postgres/postgresql'
        // driver part of the DSN and applies the Postgres driver instead of the Cockroach one.
        dsn = strings.Replace(dsn, "postgresql", "cockroach", 1)
        dsn = strings.Replace(dsn, "postgres", "cockroach", 1)

        sqlPath := filepath.Join(cli.GetProjectPath(),
            fmt.Sprintf(sqlProjPathFmt, service))
        logger.WithFields(logrus.Fields{"dsn": dsn, "sql-path": sqlPath}).Infoln("Running migrations")

        var retry int
        migrate := func() error {
            err := cli.ForkDir(nil, []string{"migrate",
                "-verbose",
                "-database", dsn,
                "-path", ".",
                "up"},
                sqlPath)

            if err != nil {
                logger.WithFields(logrus.Fields{
                    "err":     err,
                    "retries": migrateRetries - retry,
                }).Infoln("Retrying to run migrations")
                retry++
            }

            return err
        }

        return backoff.Retry(migrate,
            backoff.WithMaxTries(backoff.NewExponentialBackOff(), uint64(10)))
    }
}

As you can notice, I have to run the CLI command because I haven't managed to make migrations work by calling the library directly (complains about schema_migrations not existing, possibly related to https://github.com/mattes/migrate/issues/294).