Spring Boot with Hibernate generating drop constraint errors on startup with H2 database

Question

I am using spring-boot and have an H2 database configured like so (in the application.properties).

spring.datasource.url=jdbc:h2:mem:AZ;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

In the logs I am seeing the following errors:

o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
        name: default
        ...]
org.hibernate.Version                    : HHH000412: Hibernate Core {4.3.5.Final}
org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
org.hibernate.cfg.Environment            : HHH000021: Bytecode provider name : javassist
o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {4.0.4.Final}
org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.H2Dialect
o.h.h.i.ast.ASTQueryTranslatorFactory    : HHH000397: Using ASTQueryTranslatorFactory
org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000227: Running hbm2ddl schema export
org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: alter table key_request drop constraint FK_53shrbc21c25inskpp1yoxxss if exists
org.hibernate.tool.hbm2ddl.SchemaExport  : Table "KEY_REQUEST" not found; SQL statement:
    alter table key_request drop constraint FK_53shrbc21c25inskpp1yoxxss if exists [42102-178]

Even though hibernate reports these as errors, I can then login to the H2 console and see the constraints and they appear to be just fine.

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, INDEX_TYPE_NAME FROM information_schema.indexes WHERE TABLE_NAME='KEY_REQUEST';
TABLE_NAME    INDEX_NAME                            COLUMN_NAME  INDEX_TYPE_NAME  
KEY_REQUEST   PRIMARY_KEY_F                         REQUEST_ID   PRIMARY KEY
KEY_REQUEST   FK_53SHRBC21C25INSKPP1YOXXSS_INDEX_F  USER_ID      INDEX

If really looks like hibernate is trying to drop these constraints before it actually creates the database (i.e. some kind of bug in hibernate). Is there any way to avoid these errors clogging up the logs or are the indicative of a real failure somewhere?

UPDATE 1

Trying to force the application to only do updates using this setting:

spring.jpa.hibernate.ddl-auto=update

Results in the following errors (all the other errors disappear):

org.hibernate.tool.hbm2ddl.SchemaUpdate  : HHH000388: Unsuccessful: alter table lti_result add constraint FK_1cnh9amy5br8owkmafsrth3as foreign key (result_id) references lti_link
org.hibernate.tool.hbm2ddl.SchemaUpdate  : Constraint "FK_1CNH9AMY5BR8OWKMAFSRTH3AS" already exists; SQL statement: 
    alter table lti_result add constraint FK_1cnh9amy5br8owkmafsrth3as foreign key (result_id) references lti_link [90045-178]

NOTE: the source is here: https://github.com/azeckoski/lti_starter

Specifically the config: https://github.com/azeckoski/lti_starter/blob/master/src/main/resources/application.properties

and the model: https://github.com/azeckoski/lti_starter/tree/master/src/main/java/ltistarter/model

Answer 1

Because you are using an in-memory database, Hibernate won't find any table when executing:

hibernate.hbm2ddl.auto=create-drop

That's because the statements order is:

• drop the constraints (fk)
• drop the tables
• create the tables

• create constraints (fk)

  Query:{[alter table tableIdentifier drop constraint FK_202gbutq8qbxk0chvcpjsv6vn][]} 
  ERROR [main]: o.h.t.h.SchemaExport - HHH000389: Unsuccessful: alter table tableIdentifier drop constraint FK_202gbutq8qbxk0chvcpjsv6vn
  ERROR [main]: o.h.t.h.SchemaExport - user lacks privilege or object not found: PUBLIC.TABLEIDENTIFIER
  Query:{[drop table sequenceIdentifier if exists][]} 
  Query:{[drop table tableIdentifier if exists][]} 
  Query:{[create table sequenceIdentifier (id bigint not null, primary key (id))][]} 
  Query:{[create table tableIdentifier (id bigint not null, sequenceIdentifier_id bigint, primary key (id))][]} 
  Query:{[alter table tableIdentifier add constraint FK_202gbutq8qbxk0chvcpjsv6vn foreign key (sequenceIdentifier_id) references sequenceIdentifier][]} 
  Query:{[create sequence hibernate_sequence start with 1 increment by 1][]} 
  

You can fix this by changing the hibernate.hbm2ddl.auto to update:

hibernate.hbm2ddl.auto=update

Answer 2

Try create-only:

spring.jpa.properties.hibernate.hbm2ddl.auto=create-only

It was added into Hibernate at least starting from 5.2 (maybe even earlier) and I didn't find any proper documentation but it mentioned User Guide: 23.15. Automatic schema generation

Also if you see in logs:

WARN  SessionFactoryOptionsBuilder:394 - Unrecognized hbm2ddl_auto value : create-only.  Supported values include 'create', 'create-drop', 'update', 'none' and 'validate'.  Ignoring

That's may be a false alarm and in fact it works