Flyway clean can't drop H2 tables in lowercase

Question

This is my flyway config:

url: jdbc:h2:./target/test;MODE=MYSQL;INIT=CREATE SCHEMA IF NOT EXISTS "test";

and this is how I initialise it in my test suite:

Flyway flyway = new Flyway();
flyway.setDataSource(APP.getConfiguration().getDatabaseUrl(), APP.getConfiguration().getDatabaseUser(),
        APP.getConfiguration().getDatabasePass());
flyway.setBaselineOnMigrate(true);
flyway.setSchemas("test");
flyway.clean();
flyway.migrate();

This runs fine, but only the first time around. Any subsequent times with the same schema, I get the error:

org.flywaydb.core.api.FlywayException: Unable to drop "test"."my_table"

    at org.flywaydb.core.internal.dbsupport.SchemaObject.drop(SchemaObject.java:82)
    at org.flywaydb.core.internal.dbsupport.h2.H2Schema.doClean(H2Schema.java:69)
    at org.flywaydb.core.internal.dbsupport.Schema.clean(Schema.java:148)
    at org.flywaydb.core.internal.command.DbClean$4.doInTransaction(DbClean.java:154)
    at org.flywaydb.core.internal.command.DbClean$4.doInTransaction(DbClean.java:152)
    at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
    at org.flywaydb.core.internal.command.DbClean.cleanSchema(DbClean.java:152)
    at org.flywaydb.core.internal.command.DbClean.clean(DbClean.java:106)
    at org.flywaydb.core.Flyway$3.execute(Flyway.java:1125)
    at org.flywaydb.core.Flyway$3.execute(Flyway.java:1121)
    at org.flywaydb.core.Flyway.execute(Flyway.java:1418)
    at org.flywaydb.core.Flyway.clean(Flyway.java:1121)
    at com.TestSuite.beforeClass(TestSuite.java:63)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:24)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:234)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:74)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)
Caused by: org.h2.jdbc.JdbcSQLException: Table "my_table" not found; SQL statement:
DROP TABLE "test"."my_table" CASCADE [42102-190]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.command.ddl.DropTable.prepareDrop(DropTable.java:69)
    at org.h2.command.ddl.DropTable.update(DropTable.java:113)
    at org.h2.command.CommandContainer.update(CommandContainer.java:78)
    at org.h2.command.Command.executeUpdate(Command.java:253)
    at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:198)
    at org.flywaydb.core.internal.dbsupport.JdbcTemplate.execute(JdbcTemplate.java:219)
    at org.flywaydb.core.internal.dbsupport.h2.H2Table.doDrop(H2Table.java:43)
    at org.flywaydb.core.internal.dbsupport.SchemaObject.drop(SchemaObject.java:80)
    ... 31 more

If I remove flyway.clean() from the initialisation, I get a bunch of errors to do with migrations not working due to the database already being populated, so all the tables are there all right.

Previously, this was working fine before I added MODE=MYSQL;. Here is how the tables are being created, in V1_migration.sql:

CREATE TABLE my_table (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, session_id INT, token VARCHAR(50), CONSTRAINT s_id FOREIGN KEY (session_id) REFERENCES user_sessions(id));

What is going on? Why can't Flyway drop the tables?

Does it work if `DROP TABLE "test"."my_table" CASCADE` get executed from mysql admin or any other db client? — Boris Pavlović, Feb 25 '16 at 12:49
@BorisPavlović This is using H2, so the actual database can't be accessed from a mysql client I don't think. — fredley, Feb 25 '16 at 12:53
Sorry, but you can use some other db client http://stackoverflow.com/a/2761825/32090 — Boris Pavlović, Feb 25 '16 at 12:55
@BorisPavlović Thanks, I have opened a connection with the console tool, and I can see the schema and tables, but all the tables are listed with names in upper case. Could this be the problem? — fredley, Feb 25 '16 at 13:08
Try running the query `DROP TABLE "test"."MY_TABLE"` or `DROP TABLE "TEST"."MY_TABLE"` — Boris Pavlović, Feb 25 '16 at 13:09
@BorisPavlović The first of those worked. I put `DATABASE_TO_UPPER=false;` in my config and it fixed the problem! — fredley, Feb 25 '16 at 13:13

score 6 · Accepted Answer · answered Feb 25 '16 at 13:15

6

H2 database has to be configured as case insensitive with

DATABASE_TO_UPPER=false;

answered Feb 25 '16 at 13:15

Boris Pavlović

63,078
28
122
148

I'm working with jpa and hibernate. I can't get this setting working in conjunction with "hibernate.hbm2ddl.auto" = "validate". Is there another possible solution? – Gabriel Oliveira Feb 13 '19 at 21:10

Flyway clean can't drop H2 tables in lowercase

1 Answers1