You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Which version and edition of Flyway are you using?
9.22.3
If this is not the latest version, can you reproduce the issue with the latest one as well? (Many bugs are fixed in newer releases and upgrading will often resolve the issue)
Yes, Flyway 10 requires newer jdk than my software supports but the source code inside Flyway is the same so the problem would still be there.
Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)
Java API
Which database are you using? (Type & version)
Derby SQL 10.14 (same problem found on 10.15 and 10.16)
Which operating system are you using?
Windows 11
What did you do? (Please include the content causing the issue, any relevant configuration settings, the SQL statement(s) that failed (if any), and the command you ran)
When creating a embedded database with the following connection string
jdbc:derby:somedb;create=true;territory=sv_SE;collation=TERRITORY_BASED
This is the code snippet
log.info("Migrate main database. jdbcUrl=${jdbcUrl}")
def configuration = new FluentConfiguration()
.dataSource("${jdbcUrl};create=true;territory=sv_SE;collation=TERRITORY_BASED", null, null)
.locations("migrations/maindb/")
Flyway flyway = new Flyway(configuration)
flyway.migrate()
you will get
Caused by: org.flywaydb.core.internal.exception.FlywaySqlException: Unable to retrieve all tables in schema "APP"
---------------------------------------------
SQL State : 42818
Error Code : 30000
Message : Comparisons between 'CHAR (UCS_BASIC)' and 'CHAR (TERRITORY_BASED)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
at org.flywaydb.core.internal.database.base.Schema.allTables(Schema.java:141)
at org.flywaydb.core.internal.database.derby.DerbySchema.doEmpty(DerbySchema.java:50)
at org.flywaydb.core.internal.database.base.Schema.empty(Schema.java:66)
at org.flywaydb.core.Flyway.lambda$migrate$0(Flyway.java:162)
at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:213)
at org.flywaydb.core.Flyway.migrate(Flyway.java:140)
at org.flywaydb.core.Flyway$migrate.call(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:47)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:130)
at se.easycashier.client.database.DatabaseService.migrateMainDatabase(DatabaseService.groovy:32)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleElement.invoke(InitDestroyAnnotationBeanPostProcessor.java:389)
at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:333)
at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:157)
... 77 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: Comparisons between 'CHAR (UCS_BASIC)' and 'CHAR (TERRITORY_BASED)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement42.<init>(Unknown Source)
at org.apache.derby.jdbc.Driver42.newEmbedPreparedStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.flywaydb.core.internal.jdbc.JdbcTemplate.prepareStatement(JdbcTemplate.java:300)
at org.flywaydb.core.internal.jdbc.JdbcTemplate.queryForStringList(JdbcTemplate.java:82)
at org.flywaydb.core.internal.database.derby.DerbySchema.listObjectNames(DerbySchema.java:156)
at org.flywaydb.core.internal.database.derby.DerbySchema.doAllTables(DerbySchema.java:133)
at org.flywaydb.core.internal.database.derby.DerbySchema.doAllTables(DerbySchema.java:31)
at org.flywaydb.core.internal.database.base.Schema.allTables(Schema.java:139)
... 94 common frames omitted
The problem arises because at DerbySchema.java:156 the sql being executed looks like this
SELECT TABLEname FROM sys.sysTABLEs WHERE schemaid in (SELECT schemaid FROM sys.sysschemas where schemaname = ?) AND TABLETYPE='T'
So the root cause might very well be a problem in Derby but the workaround could be implemented in Flyway.
If the above sql would use CAST then it work
SELECT TABLEname FROM sys.sysTABLEs WHERE schemaid in (SELECT schemaid FROM sys.sysschemas where CAST(schemaname as VARCHAR(128)) = ?) AND CAST(TABLETYPE as VARCHAR(128))='T'
If this would be something to implement then there might be other queries found in DerbySchema.java that need to be updated as well.
The text was updated successfully, but these errors were encountered:
Which version and edition of Flyway are you using?
9.22.3
If this is not the latest version, can you reproduce the issue with the latest one as well? (Many bugs are fixed in newer releases and upgrading will often resolve the issue)
Yes, Flyway 10 requires newer jdk than my software supports but the source code inside Flyway is the same so the problem would still be there.
Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)
Java API
Which database are you using? (Type & version)
Derby SQL 10.14 (same problem found on 10.15 and 10.16)
Which operating system are you using?
Windows 11
What did you do? (Please include the content causing the issue, any relevant configuration settings, the SQL statement(s) that failed (if any), and the command you ran)
When creating a embedded database with the following connection string
jdbc:derby:somedb;create=true;territory=sv_SE;collation=TERRITORY_BASED
This is the code snippet
you will get
The problem arises because at DerbySchema.java:156 the sql being executed looks like this
SELECT TABLEname FROM sys.sysTABLEs WHERE schemaid in (SELECT schemaid FROM sys.sysschemas where schemaname = ?) AND TABLETYPE='T'
This very old email mentions this problem together with a workaround, https://lists.apache.org/thread/j5w8n8oqtmzpdrobsjh9ydqhcqnhyy4r
So the root cause might very well be a problem in Derby but the workaround could be implemented in Flyway.
If the above sql would use CAST then it work
SELECT TABLEname FROM sys.sysTABLEs WHERE schemaid in (SELECT schemaid FROM sys.sysschemas where CAST(schemaname as VARCHAR(128)) = ?) AND CAST(TABLETYPE as VARCHAR(128))='T'
If this would be something to implement then there might be other queries found in DerbySchema.java that need to be updated as well.
The text was updated successfully, but these errors were encountered: