Spring-boot: Latest Spring Boot buggy support for Flyway + Hibernate + test

[eepstein]

Spring Boot supports flyway + hibernate per: https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html

But, the combination appears buggy and doesn't seem to work when using a custom DB schema.

In particular, this behavior is encountered when running tests and:

• flyway is on the classpath
• flyway is used to create the schema and tables
• hibernate is used to map one or more entities to those tables with "validate" set.

Simple example is attached.

springflywayhibernateissue.zip

This appears to be the same as [#12569]

[wilkinsona]

Thanks for the report. It appears to be an issue to do with different quoting and capitalisation between your migration script, Hibernate, and H2. Your migration script does not quote the table name so an_entity becomes AN_ENTITY while Hibernate's DDL validation is looking for an_entity. As a result, validation fails due to a missing table.

The validation problem is resolved by quoting the table name in the migration:

CREATE SEQUENCE IF NOT EXISTS public.pk_id_seq MINVALUE 1000;


drop table if exists "example"."an_entity";

CREATE TABLE "example"."an_entity" (
    id                  BIGINT                    DEFAULT nextval('pk_id_seq') PRIMARY KEY
  , created_at          timestamp     NOT NULL    DEFAULT NOW()
  , updated_at          timestamp     NOT NULL    DEFAULT NOW()
  , version             INTEGER       NOT NULL    DEFAULT 1

  , name         VARCHAR       NOT NULL
  , foo          VARCHAR       NOT NULL

  , CONSTRAINT unq_name      UNIQUE (name)

);

This then reveals a secondary problem where the Hibernate-generated SQL does not quote the table name so it looks for AN_ENTITY this time and this fails as the table is named an_entity. I don't understand why Hibernate expects a lower-case table name when validating the schema but generates an SQL query that expects an upper-case table name later on.

This situation does not improve when using Hibernate's PhysicalNamingStrategyStandardImpl. In that case the table is named AnEntity but the SQL query expects ANENTITY.

Perhaps someone else on the team will have some insight, but this looks like a Hibernate bug to me at the moment.

[wilkinsona]

I should have mentioned above the a custom schema doesn't seem to be required to trigger the problem.

[wilkinsona]

I have convinced myself that this has nothing to do with Boot. It's either a bug in Hibernate or a Hibernate configuration issue. The problem only occurs when use_jdbc_metadata_defaults is set to false. With that configuration removed, a secondary failure occurs due to pk_id_seq not being found. That can be addressed by updating the migration script to remove the public. prefix.

@eepstein This is a great example of why providing a _minimal_ sample is so useful. I identified the above simply by removing parts of what you had provided that I suspected were not needed. The same goes for the custom schema that also isn't needed to reproduce the problem.

[eepstein]

Thank you Andy (@wilkinsona) for the rapid and thorough triage. I feel amply rewarded for the time spent to put together the example.

The resolution in this case: remove the use_jdbc_metadata_defaults setting.

I just did so and the tests now pass. Seems like this is an issue just with Hibernate tooling that gets triggered when the validator runs with that setting turned off (set to false). I no longer recall the reason that setting was needed (in a prior project).

And, yes, that should have been: nextval('public.pk_id_seq')

[wilkinsona]

Just to be clear, the point I was trying to make was that you could have been rewarded by finding the problem yourself if you had taken the time to produce a minimal sample. If that had been done, there would have been no need for the Spring Boot team to spend time triaging a Hibernate configuration problem.

[eepstein]

Ah, you meant more minimal. Good point.