BeanCreationException: Error creating bean with name 'flywayInitializer'

Question

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 1

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

Solution 2

It looks like the test container with the database has started successfully, so no issue there, you're getting an empty database.

Then you try running the flyway and this fails. Flyway in spring boot works during the initialization of the spring application context, so the actual migration runs while the application context gets initialized, so the migration failure looks like a spring failure.

The reason, however, is logged: the migration file has an invalid content:

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)
)

This GENERATED BY is unsupported.

Why? Probably your docker image includes the version of RDBMS that doesn't support this syntax. So it differs from the DB that you use in a local environment without docker.

In any case it's not about docker, spring or flyway but about the DB and the migration code.

In terms of resolution, I suggest running the docker image of the DB directly (without java, testcontainers and flyway). When it runs, just run this migration "manually" in pgadmin or something. You're expected to see the same error.

Comments

[vivekdubey]

I think you should check db details are correct and you are able to create table using below script (not from code using externally any editor) CREATE TABLE revinfo ( rev INTEGER GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 ), revtstmp BIGINT, PRIMARY KEY (rev) ); Error is pointing syntax error in query

[Tambet Tamm]

I run the query through pgAdmin 4, where I created new database and executed the same query with response: "CREATE TABLE Query returned successfully in 77 msec."

[M. Deinum]

Make sure you are using the same version of Postgres for your test. You are seemingly using quite an old version in Docker.

[Tambet Tamm]

Might the issue be that as the docker is creating container postgres:9.6.12, it is not compatible with the sql script?

[vivekdubey]

Do not create new database try with existing database.try when you are running your code from container,create empty db(do not keep any tables) before running your code.