Hibernate post-boot schema-validation fails with Google Spanner after upgrade to Quarkus 2.7.5

[juan-antonio-alba-db]

Describe the bug

When migrating from Quarkus version 1.13.6 to 2.7., I´m facing the following error:

2022-04-04 15:06:59,391 ERROR [io.qua.hib.orm.run.sch.SchemaManagementIntegrator] (Hibernate post-boot validation thread for <default>) Failed to validate Schema: Schema-validation: missing table [RentedCars]
2022-04-04 15:06:59,703 ERROR [io.qua.hib.orm.run.sch.SchemaManagementIntegrator] (Hibernate post-boot validation thread for <default>) The following SQL may resolve the database issues, as generated by the Hibernate schema migration tool. WARNING: You must manually verify this SQL is correct, this is a best effort guess, do not copy/paste it without verifying that it does what you expect.

After googling, I saw there was an error with the same error at #23445. But the version I´m trying to use already contains the fix. Then I started to analyse and debug it, and then I´ve found that seems there is another bug.

so let me give you some context information.

• quarkus: 2.7.5-final
• google-cloud-spanner-hibernate-dialect 1,5,2
• google-cloud-spanner-jdbc: 2.5.6
• Quarkus microservice with two databases: First and default with read and write access, second with only read access.
• Database 1 (default) has table RentedCars and database 2 (read-database) Cars
• application configuration

  datasource:
    db-kind: other
    jdbc:
      url: jdbc:cloudspanner://localhost:9011/projects/test-integration/instances/test-integration/databases/test-integration;usePlainText=true
      driver: com.google.cloud.spanner.jdbc.JdbcDriver
    "read-database":
      db-kind: other
      jdbc:
        url: jdbc:cloudspanner://localhost:9010/projects/test-integration/instances/test-integration/databases/test-integration;usePlainText=true
        driver: com.google.cloud.spanner.jdbc.JdbcDriver
  hibernate-orm:
    datasource: <default>
    dialect: com.google.cloud.spanner.hibernate.SpannerDialect
    packages: com.renting.rent
    "read-database":
      datasource: read-database
      dialect: com.google.cloud.spanner.hibernate.SpannerDialect
      packages: com.renting.cars

Testing it, I´ve seen that adding in the application configuration for the default

      database:
        generation: update

The table RentedCars is created in the second database. It explains why the table is not found in default because it checking in the wrong database.

Debugging quarkus code, I´ve found that in SchemaManagementIntegrator.runPostBootValidation(..) when getting the validator for "" the validator.tool.serviceRegistry.configurationsValues contains
hibernate.ejb.persistenceUnitName -> read-database instead of ""

Expected behavior

No response

Actual behavior

No response

How to Reproduce?

configure in application-yml two databases which already define the tables, having database generation to none for both.

1. Define databases as follow
   database 1

CREATE TABLE IF NOT EXISTS RentedCars (
    CarId VARCHAR(64),
    Start DATETIME,
    End DATETIME
);

database 2

CREATE TABLE IF NOT EXISTS Cars (
    Id VARCHAR(64),
    Description VARCHAR(255)
);

2. Create a micro service with 2 entities (one for each table)
3. Configure the datasources as shown in the bug description field to have each table in different databases
4. Start the micro service

Automatically the error in shown

I hope this helps to solve the issue.

Thanks in advance

Output of uname -a or ver

No response

Output of java -version

openjdk 11.0.9 2020-10-20 LTS

GraalVM version (if different from Java)

No response

Quarkus version or git rev

2.7.5-final

Build tool (ie. output of mvnw --version or gradlew --version)

apache-maven-3.8.2

Additional information

No response

[quarkus-bot]

/cc @Sanne, @gsmet, @yrodiere

[geoand]

Does the same happen in Quarkus 2.8.0.Final?

[juan-antonio-alba-db]

Yes, the problem still is there with version 2.8.0.Final

[gsmet]

@yrodiere does it ring a bell? I remember us having a similar issue but not sure if it was the same?

[yrodiere]

@gsmet Not sure it's related, but I'm checking.

[yrodiere]

@juan-antonio-alba-db The message mentions a missing table named "RentedCars" (with a trailing "s") while your script creates a table named "RentedCar". So the table "RentedCars" is indeed missing, and if your model maps an entity to that table, then it's completely normal that you're getting that error.

Can you check that adding a trailing "s" to "RentedCar" in your SQL script solves the problem? If so, I'll close the ticket. If not, please provide a small reproducer (a small Quarkus application that shows the error upon starting).

[juan-antonio-alba-db]

Hi Yoann,
when writing the issue description, I gave different names to the tables and databases due to company privacy where I work :-), and then I added a typo when writing the table name.

The microservice is working ok with version 1.13.6, having a similar application configuration I provided. The issue appears when migrating to the new quarkus version.

Let me prepare a full code example to reproduce it

[luca-bassoricci]

@yrodiere does it ring a bell? I remember us having a similar issue but not sure if it was the same?

#23445

[juan-antonio-alba-db]

Hi Luca, this issue was solved in 2.7.2.final version, this is happening in 2.8.0.final.

preparing example ...

[luca-bassoricci]

Yes, I know. I thought it was the issue the comment I quoted was referring to.

[MrMiyagiSan]

@yrodiere
Hi, I have put together the reproducer you requested: https://github.com/MrMiyagiSan/hibernate-schema-validation-failure

The readme contains some information worth reading.

[yrodiere]

@MrMiyagiSan Thanks a lot! That made the investigation much simpler.

The good news is, I reproduced the problem on my machine.

The bad news is... It seems to be a spanner-specific problem?

I debugged the code, restarting again and again, trying to get to the earliest occurrence of this problem, namely a moment where we're dealing with the <default> persistence unit, but the code somehow gets its hands on the read-database datasource.

I ended up here:

buildBootstrapJdbcConnectionAccess:160, JdbcEnvironmentInitiator (org.hibernate.engine.jdbc.env.internal)
getBootstrapJdbcConnectionAccess:73, JdbcServicesImpl (org.hibernate.engine.jdbc.internal)
<init>:314, HibernateSchemaManagementTool$JdbcContextBuilder (org.hibernate.tool.schema.internal)
resolveJdbcContext:209, HibernateSchemaManagementTool (org.hibernate.tool.schema.internal)
getDatabaseMetadataConnection:64, SpannerSchemaManagementTool (com.google.cloud.spanner.hibernate.schema)
doCreation:56, SpannerSchemaCreator (com.google.cloud.spanner.hibernate.schema)
doExecution:314, SchemaExport (org.hibernate.tool.hbm2ddl)
generateDDL:69, HibernateOrmDevConsoleInfoSupplier (io.quarkus.hibernate.orm.runtime.devconsole)
pushPersistenceUnit:50, HibernateOrmDevConsoleInfoSupplier (io.quarkus.hibernate.orm.runtime.devconsole)
integrate:15, HibernateOrmDevConsoleIntegrator (io.quarkus.hibernate.orm.runtime.devconsole)
<init>:300, SessionFactoryImpl (org.hibernate.internal)
build:74, FastBootEntityManagerFactoryBuilder (io.quarkus.hibernate.orm.runtime.boot)
createEntityManagerFactory:71, FastBootHibernatePersistenceProvider (io.quarkus.hibernate.orm.runtime)
createEntityManagerFactory:80, Persistence (javax.persistence)
createEntityManagerFactory:55, Persistence (javax.persistence)
get:138, JPAConfig$LazyPersistenceUnit (io.quarkus.hibernate.orm.runtime)
run:54, JPAConfig$1 (io.quarkus.hibernate.orm.runtime)
run:833, Thread (java.lang)

At this point we're trying to generate DDL for the <default> persistence unit, and strangely, JdbcEnvironmentInitiator retrieves a ConnectionProvider that points to the read-database datasource.

I noticed that, also strangely, the schema management tool involved here is... Spanner's own override of Hibernate ORM's implementation: com.google.cloud.spanner.hibernate.schema.SpannerSchemaManagementTool.

I had a quick look at the code and, sure enough:

https://github.com/GoogleCloudPlatform/google-cloud-spanner-hibernate/blob/2c97da01f00eaa0865c9205a8f8788fa896aee60/google-cloud-spanner-hibernate-dialect/src/main/java/com/google/cloud/spanner/hibernate/SpannerServiceContributor.java#L42-L45

public class SpannerServiceContributor implements ServiceContributor {

  private static final SpannerSchemaManagementTool SCHEMA_MANAGEMENT_TOOL =
      new SpannerSchemaManagementTool();

Spanner's schema management tool, which holds state related to the persistence unit it's working on, is shared among all persistence units...

In this case this means each persistence unit will inject the service registry into the schema management tool, one after the other, and the last one will win: all persistence units will use the same tool, which happens to use connections to the last persistence unit only.

So, yeah. This cannot work.

I don't see how it could work in older versions of Quarkus; I suspect the problem was there, but not as visible for you, because you disabled schema management and Quarkus' wasn't forcing validation on startup.

IMO Spanner should update their code so that their schema management tool is no longer a singleton; that should solve this problem, at least.

I'll edit the ticket description to clarify this is very specific to Spanner.

[Sanne]

We can try reach out to the Google team to improve the integration.

But I also happen to think that - when running in Quarkus - Hibernate ORM shouldn't pick up random ServiceContributor instances from the classpath; perhaps we should disable this and have extensions drive such contributions explicitly; that would also allow us to have better control about which persistence units are affected.

Essentially, we could fix overriding the upstream issue but only by creating a Spanner extension. Might be worth it for the long term.

[Leonardofreua]

I'm moving from 2.4.1 version to 2.5.0 and this error is also occurring. Is there any way to disable the Hibernate Post Boot validation?

[yrodiere]

Is there any way to disable the Hibernate Post Boot validation?

You can set quarkus.hibernate-orm.validate-in-dev-mode/quarkus.hibernate-orm.my-persistence-unit.validate-in-dev-mode to false.

However, the problem goes beyond validation: anything related to schema initialization through Hibernate ORM with Spanner is likely to produce incorrect results as well. For example, schema management will not work correctly. The Dev UI that displays SQL to create/drop the schema will display incorrect SQL. The button in the Dev UI that recreates the database will not work correctly.

Really, the problem is in google-cloud-spanner-hibernate and should be solved there. I opened an issue: GoogleCloudPlatform/google-cloud-spanner-hibernate#406

[yrodiere]

The issue was reported to the google-cloud-spanner-hibernate project, who is waiting for someone from the community to fix it.

As this is not an issue in Quarkus proper, and would affect any framework as soon as you're using google-cloud-spanner-hibernate with multiple persistence units, I'll close this ticket.

If you happen to find this and want to fix the problem, head over to GoogleCloudPlatform/google-cloud-spanner-hibernate#406.