JUnit testing flywaydb using h2 database

Question

I want to JUnit my migrations. I am setting up a H2 database in memory using jdbc:h2:mem:./test as url.

Flyway connects normally to database but fails to execute sql script giving me the following

com.nokia.avalanche.manager.migrations.FlywaydbWrap$MigrationException: Something happened in migration
at com.nokia.avalanche.manager.migrations.FlywaydbWrap.migrateDB(FlywaydbWrap.java:66)
at com.nokia.avalanche.manager.databasemanagement.FlywayDbMigrationTest.testIsDbCreated(FlywayDbMigrationTest.java:64)
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:497)
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.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: 
Migration V1__Base_version.sql failed
-------------------------------------
SQL State  : 42000
Error Code : 42000
Message    : Syntax error in SQL statement "LOCK[*] TABLES ""ENODEB"" WRITE "; SQL statement:
LOCK TABLES `enodeb` WRITE [42000-187]
Location   : db/migration/V1__Base_version.sql (D:\development\IPSL_source\trunk\myProject\target\classes\db\migration\V1__Base_version.sql)
Line       : 120
Statement  : LOCK TABLES `enodeb` WRITE

    at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:117)
at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:71)
at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:284)
at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:282)
at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
at org.flywaydb.core.internal.command.DbMigrate.applyMigration(DbMigrate.java:282)
at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:46)
at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:207)
at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:156)
at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:156)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:1059)
at org.flywaydb.core.Flyway$1.execute(Flyway.java:1006)
at org.flywaydb.core.Flyway.execute(Flyway.java:1418)
at org.flywaydb.core.Flyway.migrate(Flyway.java:1006)
at com.nokia.avalanche.manager.migrations.FlywaydbWrap.migrateDB(FlywaydbWrap.java:63)
... 27 more
Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "LOCK[*] TABLES ""ENODEB"" WRITE "; SQL statement:
LOCK TABLES `enodeb` WRITE [42000-187]
    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.message.DbException.getSyntaxError(DbException.java:191)
    at org.h2.command.Parser.getSyntaxError(Parser.java:518)
    at org.h2.command.Parser.parsePrepared(Parser.java:476)
    at org.h2.command.Parser.parse(Parser.java:305)
    at org.h2.command.Parser.parse(Parser.java:281)
    at org.h2.command.Parser.prepareCommand(Parser.java:242)
    at org.h2.engine.Session.prepareLocal(Session.java:461)
    at org.h2.engine.Session.prepareCommand(Session.java:403)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1189)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:170)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158)
    at org.flywaydb.core.internal.dbsupport.JdbcTemplate.executeStatement(JdbcTemplate.java:238)
    at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:114)
... 42 more

When I run flyway with the same sql scripts on my real MySql database it works normally no errors....Does H2 support Lock Tables?

Answer 1

You might be using MySQL commands inside your flyway SQL scripts. I suggest to add to your H2 JDBC url the following string: ;MODE=MYSQL

It might solve your problem if the commands used are supported by the MySQL compatibility mode of H2. If not, you're in a fix and you need to create H2 only flyway SQL scripts.

A word of caution: usage of H2 memory based databases with flyway may cause trouble to your JUnit. If the connection is closed, you cannot reopen it and find the content back.

Good luck with your experiments.

Update: LOCK seems unsupported in H2 even with MODE=MYSQL. It might confirm you need to write flyway SQL without MySQL specific commands.