Java API fails accessing GCP Spanner

tpurcelltpurcell Posts: 5 New member
edited April 27, 2023 2:38PM in Flyway (General Discussion)
Hello

I'm trying to call migrate from within a java class. Here's my code:

private void flywayDatasource()  {
    JdbcDataSource jdbcDataSource = new JdbcDataSource();
    jdbcDataSource.setUrl("jdbc:cloudspanner://localhost:9010/projects/<myProject>/instances/<myInstance>/databases/<myDatabase>?usePlainText=true");
    try {
        Flyway flyway = Flyway.configure().dataSource(jdbcDataSource).load();

        logger.info("################# START PLUGIN CLASSES");
        for (Plugin registered_plugin : flyway.getConfiguration().getPluginRegister().REGISTERED_PLUGINS) {
            logger.info("PLUGIN CLASS: " + registered_plugin.getClass().getSimpleName() + ", VERSION: " + registered_plugin.getPluginVersion());
        }
        logger.info("################# END PLUGIN CLASSES");

        flyway.migrate();
        flyway.info();
    } catch (Exception e) {
        logger.info("ugh " + e.getMessage(), e);
        throw new RuntimeException(e);
    }
}

Here's the output from the failure:

2023-04-26 18:32:21,423 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) ################# START PLUGIN CLASSES
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: CockroachDBDatabaseType, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: RedshiftDatabaseType, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: DB2DatabaseType, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: DerbyDatabaseType, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: H2DatabaseType, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: HSQLDBDatabaseType, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: InformixDatabaseType, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: OracleDatabaseType, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: PostgreSQLDatabaseType, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: SAPHANADatabaseType, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: SnowflakeDatabaseType, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: SQLiteDatabaseType, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: SybaseASEJConnectDatabaseType, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: SybaseASEJTDSDatabaseType, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: TestContainersDatabaseType, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: BaseAppliedMigration, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: CoreResourceTypeProvider, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: PostgreSQLConfigurationExtension, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: CleanModeConfigurationExtension, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) PLUGIN CLASS: EnvironmentVariableResolver, VERSION: null
2023-04-26 18:32:21,424 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) ################# END PLUGIN CLASSES
2023-04-26 18:32:21,493 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) ugh Unsupported Database: Google Cloud Spanner 1.0
org.flywaydb.core.api.FlywayException: Unsupported Database: Google Cloud Spanner 1.0
        at org.flywaydb.core.internal.database.DatabaseTypeRegister.getDatabaseTypeForConnection(DatabaseTypeRegister.java:105)
        at org.flywaydb.core.internal.jdbc.JdbcConnectionFactory.<init>(JdbcConnectionFactory.java:75)
        at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:140)
        at org.flywaydb.core.Flyway.migrate(Flyway.java:129)
        at com.ingrid.ibp.SpannerAdmin.flywayDatasource(SpannerAdmin.java:145)
        at com.ingrid.ibp.SpannerAdmin.createDatabaseFromSchema(SpannerAdmin.java:128)
        at com.ingrid.ibp.GCPEnvironmentLoader.provisionAndLoadAllBaselines(GCPEnvironmentLoader.java:124)
        at com.ingrid.ibp.SpannerSchemaMigrator.main(SpannerSchemaMigrator.java:84)

Using maven to build. Here are my dependencies:

<dependency>
    <groupId>com.google.cloud</groupId>
    <artifactId>google-cloud-spanner-jdbc</artifactId>
    <version>2.7.7</version>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <version>9.15.1</version>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-gcp-spanner</artifactId>
    <version>9.15.1-beta</version>
</dependency>

I notice that all of the classes displayed in PLUGIN CLASS log statements above are in flyway-core. The spanner one is not. How do I get it registered?

Thanks
Tom

Tagged:
· Share on Twitter

Answers

  • tpurcelltpurcell Posts: 5 New member
    Okay,
     
    So I've changed my code slightly to altered the configuration:

                SpannerDatabaseType spannerDatabaseType = new SpannerDatabaseType();
                SpannerDatabaseExtension spannerDatabaseExtension = new SpannerDatabaseExtension();
                ClassicConfiguration classicConfiguration = new ClassicConfiguration();
                classicConfiguration.getPluginRegister().REGISTERED_PLUGINS.add(spannerDatabaseExtension);
                classicConfiguration.getPluginRegister().REGISTERED_PLUGINS.add(spannerDatabaseType);
                classicConfiguration.setDataSource(jdbcDataSource);

                Flyway flyway = Flyway.configure().configuration(classicConfiguration).load();

    As I step through the code in a debugger I find that everything goes fine up to: 

                org.flywaydb.core.internal.jdbc.JdbcConnectionFactory#JdbcConnectionFactory

    In that class the following line succeeds and gets the first connection to the database:

                this.databaseType = DatabaseTypeRegister.getDatabaseTypeForConnection(firstConnection);

    At this point this what REGISTERED_PLUGINS looks like:

                configuration.getPluginRegister().REGISTERED_PLUGINS

    result = {ArrayList@5234}  size = 22
                 0 = {SpannerDatabaseExtension@5236}
                 1 = {SpannerDatabaseType@5237} "Google Cloud Spanner"
                 2 = {CockroachDBDatabaseType@5238} "CockroachDB"
                 3 = {RedshiftDatabaseType@5239} "Redshift"
                 4 = {DB2DatabaseType@5240} "DB2"
                 5 = {DerbyDatabaseType@5241} "Derby"
                 6 = {H2DatabaseType@5242} "H2"
                 7 = {HSQLDBDatabaseType@5243} "HSQLDB"
                 8 = {InformixDatabaseType@5244} "Informix"
                 9 = {OracleDatabaseType@5245} "Oracle"
                 10 = {PostgreSQLDatabaseType@5246} "PostgreSQL"
                 11 = {SAPHANADatabaseType@5247} "SAP HANA"
                 12 = {SnowflakeDatabaseType@5248} "Snowflake"
                 13 = {SQLiteDatabaseType@5249} "SQLite"
                 14 = {SybaseASEJConnectDatabaseType@5250} "Sybase ASE"
                 15 = {SybaseASEJTDSDatabaseType@5251} "Sybase ASE"
                 16 = {TestContainersDatabaseType@5252} "Test Containers"
                 17 = {BaseAppliedMigration@5253}
                 18 = {CoreResourceTypeProvider@5254}
                 19 = {PostgreSQLConfigurationExtension@5255}
                 20 = {CleanModeConfigurationExtension@5256}
                 21 = {EnvironmentVariableResolver@5257}

    And firstConnection:

    result = {JdbcConnection@5222}
                 typeMap = {HashMap@5273}  size = 0
                 connectionUrl = "jdbc:cloudspanner://localhost:9010/projects/<obfuscated>?usePlainText=true"
                 options = {ConnectionOptions@5275} "cloudspanner://localhost:9010/<obfuscated>?usePlainText=true"
                 spanner = {ConnectionImpl@5276}
                 clientInfo = {Properties@5277}  size = 0
                 parser = null
                 firstWarning = null
                 lastWarning = null

    So we have successfully connected to the spanner instance.

    But then in DatabaseTypeRegister.getDatabaseTypeForConnection

    SORTED_DATABASE_TYPES resolves to:

    result = {ArrayList@5423}  size = 15
                 0 = {CockroachDBDatabaseType@5425} "CockroachDB"
                 1 = {RedshiftDatabaseType@5426} "Redshift"
                 2 = {DB2DatabaseType@5427} "DB2"
                 3 = {DerbyDatabaseType@5428} "Derby"
                 4 = {H2DatabaseType@5429} "H2"
                 5 = {HSQLDBDatabaseType@5430} "HSQLDB"
                 6 = {InformixDatabaseType@5431} "Informix"
                 7 = {OracleDatabaseType@5432} "Oracle"
                 8 = {PostgreSQLDatabaseType@5433} "PostgreSQL"
                 9 = {SAPHANADatabaseType@5434} "SAP HANA"
                 10 = {SnowflakeDatabaseType@5435} "Snowflake"
                 11 = {SQLiteDatabaseType@5436} "SQLite"
                 12 = {SybaseASEJConnectDatabaseType@5437} "Sybase ASE"
                 13 = {SybaseASEJTDSDatabaseType@5438} "Sybase ASE"
                 14 = {TestContainersDatabaseType@5439} "Test Containers"

    NOTE: SpannerDatabaseType is gone from the list. And so:

    2023-04-27 16:46:10,795 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) ugh Unsupported Database: Google Cloud Spanner 1.0
    org.flywaydb.core.api.FlywayException: Unsupported Database: Google Cloud Spanner 1.0
            at org.flywaydb.core.internal.database.DatabaseTypeRegister.getDatabaseTypeForConnection(DatabaseTypeRegister.java:105)
            at org.flywaydb.core.internal.jdbc.JdbcConnectionFactory.<init>(JdbcConnectionFactory.java:75)
            at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:140)
            at org.flywaydb.core.Flyway.migrate(Flyway.java:129)
            at com.ingrid.ibp.SpannerAdmin.flywayDatasource(SpannerAdmin.java:156)

    It looks like SORTED_DATABASE_TYPES in DatabaseTypeRegister is set to a new instance of PluginRegister:

    private static final List<DatabaseType> SORTED_DATABASE_TYPES = new PluginRegister().getPlugins(DatabaseType.class).stream().sorted().collect(Collectors.toList());

    As a result it ignores the configured values.

    Please let me know if I'm missing something in the config or should be taking a different approach.

    Thanks
    Tom

    · Share on Twitter
  • tpurcelltpurcell Posts: 5 New member
    All right

    I got this to work but I don't think its the best solution.


    By adding:
          org.flywaydb.database.spanner.SpannerDatabaseType
          org.flywaydb.database.SpannerDatabaseExtension

    The I built a flyway snapshot, built my code with that, then ran the code.

    Everything worked.

    I'd rather not continue to use a locally built snapshot. Is there some configuration setting I can use yo add the GCP Spanner classes to the services META-INF?

    Thanks
    Tom
    · Share on Twitter
  • Peter_LawsPeter_Laws Posts: 278 Silver 2
    edited May 2, 2023 8:38AM
    My apologies for the rather weak reply tpurcell, are you able to confirm please that the version of flyway running matches your dependencies and isn't running something older unintentionally?

    I ask because coping your code & maven dependencies returns:

    ################# START PLUGIN CLASSES

    PLUGIN CLASS: CockroachDBDatabaseType, VERSION: null

    PLUGIN CLASS: RedshiftDatabaseType, VERSION: null

    PLUGIN CLASS: DB2DatabaseType, VERSION: null

    PLUGIN CLASS: DerbyDatabaseType, VERSION: null

    PLUGIN CLASS: H2DatabaseType, VERSION: null

    PLUGIN CLASS: HSQLDBDatabaseType, VERSION: null

    PLUGIN CLASS: InformixDatabaseType, VERSION: null

    PLUGIN CLASS: OracleDatabaseType, VERSION: null

    PLUGIN CLASS: PostgreSQLDatabaseType, VERSION: null

    PLUGIN CLASS: SAPHANADatabaseType, VERSION: null

    PLUGIN CLASS: SnowflakeDatabaseType, VERSION: null

    PLUGIN CLASS: SQLiteDatabaseType, VERSION: null

    PLUGIN CLASS: SybaseASEJConnectDatabaseType, VERSION: null

    PLUGIN CLASS: SybaseASEJTDSDatabaseType, VERSION: null

    PLUGIN CLASS: TestContainersDatabaseType, VERSION: null

    PLUGIN CLASS: BaseAppliedMigration, VERSION: null

    PLUGIN CLASS: CoreResourceTypeProvider, VERSION: null

    PLUGIN CLASS: PostgreSQLConfigurationExtension, VERSION: null

    PLUGIN CLASS: CleanModeConfigurationExtension, VERSION: null

    PLUGIN CLASS: EnvironmentVariableResolver, VERSION: null

    PLUGIN CLASS: SpannerDatabaseType, VERSION: null

    PLUGIN CLASS: SpannerDatabaseExtension, VERSION: null

    ################# END PLUGIN CLASSESSSES

    Kind regards
    Peter Laws | Redgate Software
    Have you visited our Help Center?
    · Share on Twitter
  • tpurcelltpurcell Posts: 5 New member
    Peter

    Thanks for your response. Here are the versions in my pom:

            <flyway.version>9.17.0</flyway.version>
            <spanner.plugin.version>9.17.0-beta</spanner.plugin.version>

    As for your output, I see the same thing but if you review my post from 4/27 you'll notice that Flyway succeeds on firstConnect but then everything falls apart later in the process:
    2023-04-27 16:46:10,795 INFO  IAN=           REMOTE=                   [com.ingrid.ibp.SpannerAdmin] (main) ugh Unsupported Database: Google Cloud Spanner 1.0
    org.flywaydb.core.api.FlywayException: Unsupported Database: Google Cloud Spanner 1.0
            at org.flywaydb.core.internal.database.DatabaseTypeRegister.getDatabaseTypeForConnection(DatabaseTypeRegister.java:105)
            at org.flywaydb.core.internal.jdbc.JdbcConnectionFactory.<init>(JdbcConnectionFactory.java:75)
            at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:140)
            at org.flywaydb.core.Flyway.migrate(Flyway.java:129)
            at com.ingrid.ibp.SpannerAdmin.flywayDatasource(SpannerAdmin.java:156)

    It looks like SORTED_DATABASE_TYPES in DatabaseTypeRegister is set to a new instance of PluginRegister:

    private static final List<DatabaseType> SORTED_DATABASE_TYPES = new PluginRegister().getPlugins(DatabaseType.class).stream().sorted().collect(Collectors.toList());

    As a result it ignores the configured values.

    Thanks

    Tom

    · Share on Twitter
  • tpurcelltpurcell Posts: 5 New member
    Hello
    I have found a solution. In my 4/28 post I indicated that I "solved" the issue by:
    I downloaded the flyway source from git: https://github.com/flyway/flyway
    I updated this file: https://github.com/flyway/flyway/blob/main/flyway-core/src/main/resources/META-INF/services/org.flywaydb.core.extensibility.Plugin
    By adding:
          org.flywaydb.database.spanner.SpannerDatabaseType
          org.flywaydb.database.SpannerDatabaseExtension
    The I built a flyway snapshot, built my code with that, then ran the code.

    I have found a similar solution that does not require rebuilding Flyway source.

    In my java project I added the following file:

         src/main/resources/META-INF/services/org.flywaydb.core.extensibility.Plugin

    The contents of the file:

    org.flywaydb.database.spanner.SpannerDatabaseType
    org.flywaydb.database.SpannerDatabaseExtension
    org.flywaydb.core.internal.schemahistory.BaseAppliedMigration
    org.flywaydb.core.internal.resource.CoreResourceTypeProvider
    org.flywaydb.core.internal.command.clean.CleanModeConfigurationExtension
    org.flywaydb.core.internal.configuration.resolvers.EnvironmentVariableResolver
    org.flywaydb.core.internal.proprietaryStubs.CommandExtensionStub
    org.flywaydb.core.api.output.InfoHtmlRenderer
    org.flywaydb.core.internal.reports.json.InfoResultDeserializer
    org.flywaydb.core.api.output.MigrateHtmlRenderer
    org.flywaydb.core.internal.reports.json.MigrateResultDeserializer
    org.flywaydb.core.api.output.HoldingRenderer
    org.flywaydb.core.api.output.DashboardRenderer

    With that all migrations, both Java and SQL, run successfully.

    Thanks
    Tom



    · Share on Twitter
  • Peter_LawsPeter_Laws Posts: 278 Silver 2
    Nicely done, thanks for sharing what worked for you!
    Kind regards
    Peter Laws | Redgate Software
    Have you visited our Help Center?
    · Share on Twitter

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D;):\:/:o:s:p:'-(:|:-|B)o:)<38-)=)>_<:3:{/\:(`,`:‑J*|*<|>=}:[]:$B^D8D:‑D:O:'(>:O;‑]%‑);^)=p(~)=]xx:]
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • Flyway (General Discussion)Comment As ...