CODEWITHSUNDEEP
javasql-serverspringh2flyway
naXa stands with Ukraine
naXa stands with Ukraine

Reputation: 37916

Flyway migration succeeds in MS SQL Server but fails in H2 database

A database scheme in my application is managed by Flyway migrations. These migrations are designed for MS SQL Server and have been executed a lot of times with no problems.

I've decided to reuse the migrations for setting up a testing database (H2) before running tests. And created the following beans in Spring context for this:

@Bean
public DataSource dataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("org.h2.Driver");
    // Scheme = test_db, Compatibility mode = MSSQLServer
    dataSource.setUrl("jdbc:h2:mem:test_db;MODE=MSSQLServer;DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1;" +
            "INIT=CREATE SCHEMA IF NOT EXISTS test_db\\;SET SCHEMA test_db");
    dataSource.setUsername("sa");
    dataSource.setPassword("");

    return dataSource;
}

@Bean
public Flyway flyway() {
    Flyway flyway = new Flyway();
    flyway.setDataSource(dataSource());
    flyway.setSchemas("test_db");
    flyway.setLocations("filesystem:db\\migration");
    flyway.migrate();  // Exception is thrown from here !!!
    return flyway;
}

But during Spring context initialization I get the following exception saying about a syntax error on the very first line of my migration script:

org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: Migration V1__init.sql failed

SQL State  : 42001
Error Code : 42001
Message    : Syntax error in SQL statement "SET XACT_ABORT[*] ON
...

Obviously there's some compatibility issue between Flyway / MS SQL Server scripts / H2, cause if I remove the line SET XACT_ABORT[*] ON from the migration script, it gives me another error.

I'm new in using in-memory database and I want to keep Flyway migrations unmodified. What are the other possible solutions?

Upvotes: 2

Views: 2747

Answers (1)

wi2ard
wi2ard

Reputation: 1545

This is a database generated error, no a Flyway error. The two DBMS you are using are not fully compatible: some datatypes, functions, etc. accepted by MS SQL Server are not available in H2, SQL syntax is not identical and so on. That statement generating your error is specific to MS SQL Server (see docs)

I don't know of any way that Flyway would automatically translate the DML scripts you use. You'll have to create your in memory schema yourself. Unless someone knows of any tools that do this automagically.

Tell us about your persistance layer, what framework are you using?

If you are testing a JPA Hibernate application then you could enable autogeneration of the schema at runtime (by setting hibernate.hbm2ddl.auto property in the persistence.xml used by your test app context). Of course you would still need to populate your schema with data. Possibly your scripts will work for this. A manual sollution for this could be, before any tests start, opening a second DB connection to your main database, selecting the data you need in your domain objects and then persisting them through your in memory DB connection. I didn't try this, just an idea...

Upvotes: 3

Related Questions