JPA+Hibernate+Spanner: Execution failed for statement: RUN BATCH with Batch DDL Statements > 10

[Jeevan-J]

ERROR: INVALID_ARGUMENT: Too many DDL statements that require data validation in request. Limit 10.

2024-05-02 11:21:05.321 [main] ERROR c.u.f.j.c.HapiFhirLocalContainerEntityManagerFactoryBean [AbstractEntityManagerFactoryBean.java:426] Failed to initialize JPA EntityManagerFactory: [PersistenceUnit: HAPI_PU] Unable to build Hibernate SessionFactory; nested exception is com.google.cloud.spanner.SpannerException: UNKNOWN: INVALID_ARGUMENT: io.grpc.StatusRuntimeException: INVALID_ARGUMENT: Too many DDL statements that require data validation in request. Limit 10.

We are trying to configure GCP Cloud Spanner for HAPI FHIR JPA server using JDBC. Using the latest version from HAPI JPA Starter Project.

We have included the Spanner Driver and Dialect dependencies in pom.xml and updated the application.yaml file to use Spanner database.

Updated configuration (partial, rest of config remains same) looks like these:

spring:
  main:
    allow-circular-references: true
    #allow-bean-definition-overriding: true
  flyway:
    enabled: false
    baselineOnMigrate: true
    fail-on-missing-locations: false
  datasource:
    url: jdbc:cloudspanner:/projects/<project-id>/instances/<instance-name>/databases/<database-name>
    driverClassName: com.google.cloud.spanner.jdbc.JdbcDriver
    max-active: 9
    hikari:
      maximum-pool-size: 9
  jpa:
    properties:
      hibernate.format_sql: false
      hibernate.show_sql: true
      hibernate.dialect: com.google.cloud.spanner.hibernate.SpannerDialect
      hibernate.hbm2ddl.auto: update
      hibernate.jdbc.batch_size: 2
      hibernate.jdbc.batch_versioned_data: false

When I start the application, it creates all the tables and stops while creating the foreign keys. It fails when trying to run a batch ddl with alter table statements.

Hibernate: START BATCH DDL
Hibernate: alter table hfj_search_include add constraint FK_SEARCHINC_SEARCH foreign key (search_pid) references hfj_search (pid)
Hibernate: alter table hfj_spidx_coords add constraint FKC97MPK37OKWU8QVTCEG2NH9VN foreign key (res_id) references hfj_resource (res_id)
Hibernate: alter table hfj_spidx_date add constraint FK_SP_DATE_RES foreign key (res_id) references hfj_resource (res_id)
Hibernate: alter table hfj_spidx_number add constraint FK_SP_NUMBER_RES foreign key (res_id) references hfj_resource (res_id)
Hibernate: alter table hfj_spidx_quantity add constraint FK_SP_QUANTITY_RES foreign key (res_id) references hfj_resource (res_id)
Hibernate: alter table hfj_spidx_quantity_nrml add constraint FK_SP_QUANTITYNM_RES foreign key (res_id) references hfj_resource (res_id)
Hibernate: alter table hfj_spidx_string add constraint FK_SPIDXSTR_RESOURCE foreign key (res_id) references hfj_resource (res_id)
Hibernate: alter table hfj_spidx_token add constraint FK_SP_TOKEN_RES foreign key (res_id) references hfj_resource (res_id)
Hibernate: alter table hfj_spidx_uri add constraint FKGXSREUTYMMFJUWDSWV3Y887DO foreign key (res_id) references hfj_resource (res_id)
Hibernate: alter table hfj_subscription_stats add constraint FK_SUBSC_RESOURCE_ID foreign key (res_id) references hfj_resource (res_id)
Hibernate: alter table mpi_link add constraint FK_EMPI_LINK_GOLDEN_RESOURCE foreign key (golden_resource_pid) references hfj_resource (res_id)
Hibernate: alter table mpi_link add constraint FK_EMPI_LINK_PERSON foreign key (person_pid) references hfj_resource (res_id)
Hibernate: alter table mpi_link add constraint FK_EMPI_LINK_TARGET foreign key (target_pid) references hfj_resource (res_id)
Hibernate: alter table mpi_link_aud add constraint FKkbqi6ie5cmr64rl4a1qbeury1 foreign key (rev) references hfj_revinfo (rev)
Hibernate: alter table npm_package_ver add constraint FK_NPM_PKV_PKG foreign key (package_pid) references npm_package (pid)
Hibernate: alter table trm_valueset_concept add constraint FK_TRM_VALUESET_PID foreign key (valueset_pid) references trm_valueset (pid)
Hibernate: RUN BATCH

and errors out

2024-05-02 11:21:05.321 [main] ERROR c.u.f.j.c.HapiFhirLocalContainerEntityManagerFactoryBean [AbstractEntityManagerFactoryBean.java:426] Failed to initialize JPA EntityManagerFactory: [PersistenceUnit: HAPI_PU] Unable to build Hibernate SessionFactory; nested exception is com.google.cloud.spanner.SpannerException: UNKNOWN: INVALID_ARGUMENT: io.grpc.StatusRuntimeException: INVALID_ARGUMENT: Too many DDL statements that require data validation in request. Limit 10.

Are any possible ways to handle this within the dialect or hibernate settings? We have tried to set hibernate.jdbc.batch_size but that doesn't seem to work.

[olavloite]

@Jeevan-J One option could be to capture the DDL statements that are being generated in a SQL file, and then use Flyway to execute those DDL statements, instead of letting Hibernate do that. That would also allow you to manually add START BATCH DDL / RUN BATCH statements at the most appropriate places.

We'll also look into whether there is an automated solution for this.

[olavloite]

@Jeevan-J What I don't quite understand is that it seems like the application is only trying to create the foreign keys in this DDL batch. Normally, Hibernate creates both the tables and all primary keys in one batch. Have you already created the tables in some other way first? Or are you doing something else in order to only generate the foreign key constraints?

[burkedavison]

Closing due to lack of customer response. Please reopen with requested clarification by olavloite@ if this is still an issue.