javapostgresqldockertestingflyway

BeanCreationException: Error creating bean with name 'flywayInitializer'

I am trying to run my project tests in a docker container. All of the tests work just fine when running locally. Errors started occurring when I tried to move my testing to docker container. Here is the error message:

java.lang.IllegalStateException: Failed to load ApplicationContext
[...]
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: 
Migration V1__initial_user.sql failed
-------------------------------------
SQL State  : 42601
Error Code : 0
Message    : ERROR: syntax error at or near "GENERATED"
  Position: 45
Location   : db/migration/V1__initial_user.sql (/Users/villemossip/Desktop/GRP/GRP-SAS/application/build/resources/main/db/migration/V1__initial_user.sql)
Line       : 36
Statement  : CREATE TABLE revinfo
(
    rev      INTEGER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 ),
    revtstmp BIGINT,
    PRIMARY KEY (rev)
)

From the log we can see that container image was created, but it fails to migrate the sql schema:

[...]
2019-10-10 10:36:18.768  INFO 49547 --- [           main] o.f.c.internal.license.VersionPrinter    : Flyway Community Edition 5.2.4 by Boxfuse
2019-10-10 10:36:18.777  INFO 49547 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2019-10-10 10:36:18.795  INFO 49547 --- [           main] 🐳 [postgres:9.6.12]                     : Creating container for image: postgres:9.6.12
2019-10-10 10:36:19.001  INFO 49547 --- [           main] 🐳 [postgres:9.6.12]                     : Starting container with ID: a32dd0850baf34770cce9bdc81918cd4db40502188b85dfaa90f74e2900f9fa7
2019-10-10 10:36:19.547  INFO 49547 --- [           main] 🐳 [postgres:9.6.12]                     : Container postgres:9.6.12 is starting: a32dd0850baf34770cce9bdc81918cd4db40502188b85dfaa90f74e2900f9fa7
2019-10-10 10:36:23.342  INFO 49547 --- [           main] 🐳 [postgres:9.6.12]                     : Container postgres:9.6.12 started
2019-10-10 10:36:23.426  INFO 49547 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2019-10-10 10:36:23.431  INFO 49547 --- [           main] o.f.c.internal.database.DatabaseFactory  : Database: jdbc:postgresql://localhost:32834/test (PostgreSQL 9.6)
2019-10-10 10:36:23.488  INFO 49547 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 6 migrations (execution time 00:00.024s)
2019-10-10 10:36:23.501  INFO 49547 --- [           main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table: "public"."flyway_schema_history"
2019-10-10 10:36:23.519  INFO 49547 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema "public": << Empty Schema >>
2019-10-10 10:36:23.520  INFO 49547 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema "public" to version 1 - initial user
2019-10-10 10:36:23.542 ERROR 49547 --- [           main] o.f.core.internal.command.DbMigrate      : Migration of schema "public" to version 1 - initial user failed! Changes successfully rolled back.
2019-10-10 10:36:23.546  WARN 49547 --- [           main] o.s.w.c.s.GenericWebApplicationContext   : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: 
Migration V1__initial_user.sql failed
-------------------------------------
[...]

Here is part of the sql script (app/src/main/resources/db/migration):

[...]
constraint user_aud_pkey primary key (id, rev)
);

CREATE TABLE revinfo
(
    rev      INTEGER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 ),
    revtstmp BIGINT,
    PRIMARY KEY (rev)
);

CREATE SEQUENCE hibernate_sequence INCREMENT 1 MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1;

Here is "application.properties" (app/test/java/resources):

spring.datasource.driver-class-name=org.testcontainers.jdbc.ContainerDatabaseDriver
spring.datasource.url=jdbc:tc:postgresql://localhost:5433/test
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=validate
spring.jackson.default-property-inclusion=NON_NULL

spring.flyway.baselineOnMigrate=true
spring.flyway.check-location=true
spring.flyway.locations=classpath:db/migration
spring.flyway.schemas=public
spring.flyway.enabled=true

Also in the same directory I have container-license-acceptance.txt file.

Inside "build.gradle" I added the following lines (app/build.gradle):

dependencies {
    [...]
    testImplementation "org.testcontainers:junit-jupiter:1.11.3"
    testImplementation "org.testcontainers:postgresql:1.11.3"

}

Inside BaseInitTest file, I have the following lines (app/test/java/com):

@Testcontainers
@SpringBootTest
public class BaseIntTest {

    @Container
    private static final PostgreSQLContainer<?> container = new PostgreSQLContainer<>();

    [...]

I don't understand, how can the same tests pass at first, but fail when I move them to docker container?

Solution

  • Thank you @M. Deinum and Mark Bramnik!

    I found out that the issue is with Postgres version. For some reason by default docker image is created with old version 9.6.12, but sql script GENERATED BY DEFAULT was added to Postgres with version 10.

    Solution 1 (Update the sql script to older version):

    CREATE TABLE revinfo
(
    rev      INTEGER PRIMARY KEY NOT NULL,
    revtstmp BIGINT
);

    Solution 2: Changed docker image version to 11.2 by creating CustomPostgreSQLContainer file in the project. 

    import org.testcontainers.containers.PostgreSQLContainer;

public class CustomPostgreSQLContainer extends PostgreSQLContainer<CustomPostgreSQLContainer> {
    private static final String IMAGE_VERSION = "postgres:11.2";
    private static CustomPostgreSQLContainer container;
    CustomPostgreSQLContainer() {
        super(IMAGE_VERSION);
    }
    public static CustomPostgreSQLContainer getInstance() {
        if (container == null) {
            container = new CustomPostgreSQLContainer();
        }
        return container;
    }
    @Override
    public void start() {
        super.start();
        System.setProperty("spring.datasource.url", container.getJdbcUrl());
        System.setProperty("spring.datasource.username", container.getUsername());
        System.setProperty("spring.datasource.password", container.getPassword());
    }
    @Override
    public void stop() {
        //do nothing, JVM handles shut down
    }
}

    And updating BaseIntTest file:

    @Testcontainers
@SpringBootTest
public class BaseIntTest {

    @Container
    private static final PostgreSQLContainer<?> container = CustomPostgreSQLContainer.getInstance();

    And last removing two lines from test application.properties file:

    spring.datasource.driver-class-name=org.testcontainers.jdbc.ContainerDatabaseDriver
spring.datasource.url=jdbc:tc:postgresql://localhost:5433/test