search

mattes / migrate

Database migrations. CLI and Golang library.
Other
2.29k stars 326 forks source link

Migrate doesn't work with CockroachDB #294

Closed crosslogic closed 6 years ago

crosslogic commented 7 years ago

I've compiled the CLI with cockroachdb tag and run this command on CocrkoachDB v1.1:

migrate \
            -database cockroachdb://test:test@localhost:26257/test_cl?sslmode=disable     \
            -source file://sqlfiles                                                     \
            -verbose up

This is the log:

2017/10/17 16:12:38 Start buffering 1/u persona 2017/10/17 16:12:38 Start buffering 2/u centros 2017/10/17 16:12:38 Start buffering 3/u conceptos 2017/10/17 16:12:38 Start buffering 4/u contratos 2017/10/17 16:12:38 Start buffering 5/u cuentas 2017/10/17 16:12:38 Start buffering 6/u sucursales 2017/10/17 16:12:38 Start buffering 7/u unicidades 2017/10/17 16:12:38 Start buffering 8/u ubicaciones 2017/10/17 16:12:38 Start buffering 9/u productos 2017/10/17 16:12:38 Start buffering 10/u skus 2017/10/17 16:12:38 Start buffering 11/u usuarios 2017/10/17 16:12:38 Start buffering 30/u ops 2017/10/17 16:12:39 error: pq: relation "schema_migrations" does not exist

Am I missing something?

nickjackson commented 6 years ago

I think there is an issue here where the table_name = $1 value needs double quoting.

I believe this is due to some changes made in v1.1.0 around case sensitivity of table names. https://www.cockroachlabs.com/docs/releases/v1.1.0.html#backwards-incompatible-changes

zikes commented 6 years ago

This is a result of using TRUNCATE to clear the migrations table within a transaction at https://github.com/mattes/migrate/blob/master/database/cockroachdb/cockroachdb.go#L224

One of the current limitations of CRDB is that schema changes can't be in the same transaction as data manipulation: https://www.cockroachlabs.com/docs/stable/known-limitations.html#schema-changes-within-transactions

Unfortunately, it seems that TRUNCATE is considered a schema change. My guess is that it drops and recreates the table behind the scenes for efficiency's sake, rather than deleting individual records. I changed that line to DELETE FROM and this error was resolved for me.