CockroachDb invalid default database name

[FlakM]

What version of Flyway are you using?

current master (I believe future 5.0.0)

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin, SBT plugin, ANT tasks)

API

What database are you using (type & version)?

CockroachDb latest (v1.1.3)

What operating system are you using?

ElementaryOS

What did you do?

Running migrations with newly implemented support for roach with url (without schema param):

jdbc:postgresql://127.0.0.1:26257/?sslmode=disable

results in method org.flywaydb.core.internal.database.Connection#doGetCurrentSchemaName
implemented for roach as SHOW database returning empty String and that results in Errors during executing doChangeCurrentSchemaTo

due to expression "SET DATABASE =" being invalid

According to documentation https://www.cockroachlabs.com/docs/stable/set-vars.html
It is possible to set database to default with expression:

SET DATABASE=DEFAULT;

Dont you think it would be good idea to change doChangeCurrentSchemaTo to something like that:

    @Override
    public void doChangeCurrentSchemaTo(String schema) throws SQLException {
        if (schema == null || schema.equals("")){
            // avoid empty schema name when original schema was the default one
            schema = "DEFAULT";
        }
        jdbcTemplate.execute("SET database = " + schema);
    }

I have tested it manually. Should you agree I'd be happy to send a PR

[axelfontaine]

Thanks for reporting this. Can you help me understand which problem you are trying to solve?

Note that CockroachDB 1.1 doesn't support working with objects in other databases and doesn't have a real default database.

[FlakM]

Thanks for all the work! Well I'm trying to run my migrations without specifying database in url.
Flyway itself runs migration smoothly but trips up on going back to original database.
Checkout scenario https://gist.github.com/FlakM/2870a512093b085cba6490348eb9967a

I've also specified schema mydatabse in connection url (that did not exist at the point of migraiting) and it went ok (except for running create schema_version table 4 times)

I reported this mainly because the migration itself went well and the result seems strange to me.

[axelfontaine]

Thanks for the detailed explanation and the gist. That was super helpful. Merged