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?

Christian MICHON · Accepted Answer · 2015-08-26 09:27:30Z

3

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.

edited Aug 26, 2015 at 9:27

answered Aug 26, 2015 at 9:21

Christian MICHON

2,1802 gold badges21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Apostolos Over a year ago

Yes lock is not supported in H2...I get a new error of tables not existing, when they are used as fk to another table and created after that table. It works in sql...

Christian MICHON Over a year ago

@Apostolos could you share a sqlfiddle? or is your data model confidential/proprietary?

Christian MICHON Over a year ago

please show at least how the fk are created: a contrived example would do for 2 tables and 1 foreign key.

Michal Tenenberg Over a year ago

@ChristianMICHON Your answer might not have helped OP but it did help me, so thanks!

Collectives™ on Stack Overflow

JUnit testing flywaydb using h2 database

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related