Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

platform directories not respected when applying migrations generated for multiple platforms #162

Open
Incanus3 opened this issue Nov 1, 2024 · 3 comments

Comments

@Incanus3
Copy link

Incanus3 commented Nov 1, 2024

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

Screenshot_20241101_140509

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

Screenshot_20241101_140145

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
Copy link
Author

Incanus3 commented Nov 1, 2024

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
Copy link
Member

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
Copy link
Author

Incanus3 commented Dec 3, 2024

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())
        }
    }
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants