Last Updated
Viewed 16 Times
              

I have to following migration working in postgres:

ALTER TABLE task_def
  DROP COLUMN retry_count,
  DROP COLUMN timeout_seconds;

(and running in prod) but now i want to switch to h2 for my unit test but h2 doesnt seem to accept it My database config in spring boot:

spring.datasource.url=jdbc:h2:./target/testdb;MODE=PostgreSQL
spring.datasource.username="sa"
spring.datasource.password=""
spring.jpa.hibernate.ddl-auto=none
spring.datasource.driver-class-name=org.postgresql.Driver

spring.flyway.url=jdbc:h2:./target/testdb;MODE=PostgreSQL
spring.flyway.user="sa"
spring.flyway.password=""
spring.flyway.schemas=

The error:

 Migration V3__.....sql failed
---------------------------------------
SQL State  : 42S22
Error Code : 42122
Message    : Column "DROP" not found; SQL statement:
ALTER TABLE task_def
  DROP COLUMN retry_count,
  DROP COLUMN timeout_seconds [42122-200]
Location   : db/migration/V3__.....sql
Line       : 1
Statement  : ALTER TABLE task_def
  DROP COLUMN retry_count,
  DROP COLUMN timeout_seconds

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?

I am using h2 db for testing and I am using hibernate to create tables automatically but want flyway to insert scripts in the database. So, my flyway is not having create script.

Firstly I am not sure if this is possible or not. But when I am using some insert script my test is failing as flyway is not able to insert the record complaining that the table doesn't exist.

It seems it is trying to run the script much before than h2 is creating the table.

I am using spring boot to invoke the flyway. So, I just need this dependency and a db.migaration folder in the class path to activate the flyway:

<dependency> <groupId>org.flywaydb</groupId> <artifactId>flyway-core</artifactId> </dependency>

Any though would be great.

If you need more info, please do let me know.

If I add the create script for the various in the sql file, then it will work fine but I don't want to write create scripts. Rather I want to rely on hibernate to create it for me.

Thanks

I'm working on a project where I need to migrate database and use H2 database on a file for development environment.

I added the flyway dependendy into pom of my Spring Boot (1.4.0.RELEASE) web application:

    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-core</artifactId>
    </dependency>

Then I configured db and flyway on application.properties:

spring.h2.console.enabled=true
spring.h2.console.path=/h2-console
spring.datasource.url=jdbc:h2:file:./db/pippo.db;
spring.datasource.username=pippo
spring.datasource.password=pluto


flyway.enabled=true
flyway.url=jdbc:h2:file:./db/pippo.db
flyway.user=pippo
flyway.password=pluto
flyway.locations=classpath:db/migration

Then if I launch the application it properly creates the database file into ./db/pippo.db... and so I suppose that all will work fine, but when I write http://localhost:8081/h2-console on my browser I receive 404 response. What's wrong with my configuration?

Thank you all

Similar Question 5 (1 solutions) : Use H2 Database for Spring Test Profile with Flyway

cc