platform directories not respected when applying migrations generated for multiple platforms

[Incanus3]

Hi guys. We just started experimenting with ebean-migration and encountered some problems. We have a migration generation script like this:

fun main(args: Array<String>) {
    System.setProperty("spring.profiles.active", "kb-dev")
    System.setProperty("spring.main.banner-mode", "off")
    System.setProperty("spring.main.web-application-type", "none")
    System.setProperty("logging.level.root", "WARN")
    System.setProperty("kb.background.run-jobs", "false")
    System.setProperty("kb.architecture.load-registry-network", "false")

    val context = runApplication<QwazarKB>(*args)
    val database = context.getBean(INDEX_DB_QUALIFIER, Database::class.java)

    val migration = DbMigration.create().apply {
        setServer(database)

        addPlatform(Platform.H2)
        addPlatform(Platform.POSTGRES)
        addPlatform(Platform.ORACLE)
        addPlatform(Platform.SQLSERVER17)

        setIncludeIndex(true)

        // setApplyPrefix("3.14.0-") // TODO: change before running!
        setName("add ZkratkaListRow") // TODO: change before running!

        // TODO: can we somehow deduce this?
        setPathToResources("backends/kb/src/main/resources")
    }

    migration.generateMigration()
}

this correctly generates a directory structure like this

then when I'm trying to apply these migrations in H2, all of the SQL files (not just the two H2-specific ones) are found and attempted to be applied, as you can see in this debugger screenshot

And this results in the following error when applying I__create_procs.sql (which is SQL server specific!):

Caused by: io.ebean.migration.MigrationException: Error running DB migrations
	at io.ebean.migration.runner.MigrationEngine.run(MigrationEngine.java:93) ~[ebean-migration-14.2.0.jar:14.2.0]
	at io.ebean.migration.runner.MigrationEngine.run(MigrationEngine.java:44) ~[ebean-migration-14.2.0.jar:14.2.0]
	at io.ebean.migration.MigrationRunner.run(MigrationRunner.java:100) ~[ebean-migration-14.2.0.jar:14.2.0]
	at io.ebean.migration.MigrationRunner.run(MigrationRunner.java:72) ~[ebean-migration-14.2.0.jar:14.2.0]
	at io.ebean.migration.MigrationRunner.run(MigrationRunner.java:65) ~[ebean-migration-14.2.0.jar:14.2.0]
	at cz.sentica.qwazar.kb.configuration.persistence.IndexPersistenceConfiguration.indexDatabase(IndexPersistenceConfiguration.kt:79) ~[main/:na]
	at cz.sentica.qwazar.kb.configuration.persistence.IndexPersistenceConfiguration$$SpringCGLIB$$0.CGLIB$indexDatabase$4(<generated>) ~[main/:na]
	at cz.sentica.qwazar.kb.configuration.persistence.IndexPersistenceConfiguration$$SpringCGLIB$$FastClass$$1.invoke(<generated>) ~[main/:na]
	at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:258) ~[spring-core-6.1.13.jar:6.1.13]
	at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:348) ~[spring-context-6.1.13.jar:6.1.13]
	at cz.sentica.qwazar.kb.configuration.persistence.IndexPersistenceConfiguration$$SpringCGLIB$$0.indexDatabase(<generated>) ~[main/:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:569) ~[na:na]
	at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:146) ~[spring-beans-6.1.13.jar:6.1.13]
	... 93 common frames omitted
Caused by: java.sql.SQLException: Error executing [if not exists (select name  from sys.types where name = 'ebean_bigint_tvp') create type ebean_bigint_tvp as table (c1 bigint)] error[Syntax error in SQL statement "[*]if not exists (select name  from sys.types where name = 'ebean_bigint_tvp') create type ebean_bigint_tvp as table (c1 bigint)"; SQL statement:
if not exists (select name  from sys.types where name = 'ebean_bigint_tvp') create type ebean_bigint_tvp as table (c1 bigint) [42000-224]]
	at io.ebean.ddlrunner.DdlRunner.runStatement(DdlRunner.java:106) ~[ebean-ddl-runner-2.3.jar:2.3]
	at io.ebean.ddlrunner.DdlRunner.runStatements(DdlRunner.java:72) ~[ebean-ddl-runner-2.3.jar:2.3]
	at io.ebean.ddlrunner.DdlRunner.runAll(DdlRunner.java:55) ~[ebean-ddl-runner-2.3.jar:2.3]
	at io.ebean.migration.runner.MigrationScriptRunner.runScript(MigrationScriptRunner.java:34) ~[ebean-migration-14.2.0.jar:14.2.0]
	at io.ebean.migration.runner.MigrationTable.executeMigration(MigrationTable.java:432) ~[ebean-migration-14.2.0.jar:14.2.0]
	at io.ebean.migration.runner.MigrationTable.executeMigration(MigrationTable.java:406) ~[ebean-migration-14.2.0.jar:14.2.0]
	at io.ebean.migration.runner.MigrationTable.runMigration(MigrationTable.java:326) ~[ebean-migration-14.2.0.jar:14.2.0]
	at io.ebean.migration.runner.MigrationTable.shouldRun(MigrationTable.java:286) ~[ebean-migration-14.2.0.jar:14.2.0]
	at io.ebean.migration.runner.MigrationTable.runAll(MigrationTable.java:524) ~[ebean-migration-14.2.0.jar:14.2.0]
	at io.ebean.migration.runner.MigrationEngine.runMigrations(MigrationEngine.java:131) ~[ebean-migration-14.2.0.jar:14.2.0]
	at io.ebean.migration.runner.MigrationEngine.run(MigrationEngine.java:76) ~[ebean-migration-14.2.0.jar:14.2.0]
	... 108 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "[*]if not exists (select name  from sys.types where name = 'ebean_bigint_tvp') create type ebean_bigint_tvp as table (c1 bigint)"; SQL statement:
if not exists (select name  from sys.types where name = 'ebean_bigint_tvp') create type ebean_bigint_tvp as table (c1 bigint) [42000-224]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:514) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:489) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.message.DbException.get(DbException.java:223) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.message.DbException.get(DbException.java:199) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.message.DbException.getSyntaxError(DbException.java:247) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.command.ParserBase.getSyntaxError(ParserBase.java:748) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.command.Parser.parsePrepared(Parser.java:773) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.command.Parser.parse(Parser.java:592) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.command.Parser.parse(Parser.java:569) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.command.Parser.prepareCommand(Parser.java:483) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:639) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:559) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1166) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:245) ~[h2-2.2.224.jar:2.2.224]
	at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:231) ~[h2-2.2.224.jar:2.2.224]
	at io.ebean.ddlrunner.DdlRunner.runStatement(DdlRunner.java:101) ~[ebean-ddl-runner-2.3.jar:2.3]
	... 118 common frames omitted

I guess I can work around this by setting a narrower migrationPath, but then this code would have to be changed btw platforms.

[Incanus3]

I tried setting MigrationConfig.migrationPath to db.platform().base().name.lowercase() and then it does work, but this doesn't seem like it should be needed.

[rbygrave]

The issue here is with running the migrations ... but you haven't show us any code for running the migrations? Are you running the migrations declaratively? Or how are you running the migrations?

[Incanus3]

oh, right, sorry about that. this is how we run them:

const val INDEX_DB_NAME = "index"
const val INDEX_DB_QUALIFIER = "indexDatabase"

@Configuration
@Import(JacksonAutoConfiguration::class)
class IndexPersistenceConfiguration {
    // example yaml:
    // datasource:
    //   index:
    //     username: sa
    //     password: sa
    //     url: jdbc:h2:mem:indexdb
    //     driver: org.h2.Driver
    @Bean
    @ConfigurationProperties(prefix = "datasource.$INDEX_DB_NAME")
    fun indexDataSourceConfig() = DataSourceConfig().also {
        it.addProperty("quoteReturningIdentifiers", false)
    }

    // example yaml:
    // database:
    //   index:
    //     ddl-run: false
    //     ddl-generate: false
    //     ddl-migrate: true
    @Bean
    @ConfigurationProperties(prefix = "database.$INDEX_DB_NAME")
    fun indexDatabaseConfig(
        objMapper: ObjectMapper,
        @Qualifier("indexDataSourceConfig") dsConfig: DataSourceConfig,
    ) = DatabaseConfig().also {
        it.name = INDEX_DB_NAME
        it.objectMapper = objMapper
        it.isDefaultServer = false
        it.packages = listOf(
            "cz.sentica.qwazar.kb.modules.dora.qstore.resources",
            // ...
        )

        it.setDataSourceConfig(dsConfig)
    }

    @Bean(name = [INDEX_DB_QUALIFIER], destroyMethod = "")
    fun indexDatabase(
        @Qualifier("indexDatabaseConfig") dbConfig: DatabaseConfig,
        @Value("\${database.$INDEX_DB_NAME.ddl-migrate:false}") migrate: Boolean,
    ): Database = DatabaseFactory.create(dbConfig).also {
        if (migrate) {
            val migrationConfig = MigrationConfig().apply {
                // otherwise ALL migrations (for all platforms) are applied
                val platformDir = it.platform().base().name.lowercase()
                migrationPath = "dbmigration/$INDEX_DB_NAME/$platformDir"
            }

            MigrationRunner(migrationConfig).run(it.dataSource())
        }
    }
}