java - 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?

Answer 1

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.