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

Juvenility

It looks like this is a bug in hibernate maybe. Is that the general consensus here? – Juvenility

I have the same problem (on a slightly older version, 4.2.8). The errors are benign, Hibernate catches these exceptions and creates the schema anyway. But it does indeed look like a bug. There's no point in dropping the constraints when you're creating the schema for the first time. "create" is implemented as "replace". – Linn

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

Potiche

That's really close but results in the following errors: 2014-07-14 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] I will update the question with extra details – Juvenility

That's why I use Flyway incremental updates instead of relying on Hibernate schema generation. – Potiche

So are you saying that if I am using hibernate there is no way to avoid those failures? – Juvenility

A common misconception is that create-drop drops tables first and then creates then. In fact, just as the order of the terms in create-drop suggest, it creates tables first, then drops them when the session factory is disposed of. See the Hibernate docs: docs.jboss.org/hibernate/orm/3.3/reference/en/html/… . So this wouldn't explain azeckoski's problem. – Vella

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

Malia

This one worked for me, but I'm curious as to whether it will have any repercussions on the schema? I had "update" as value and I had all sorts of annoying warnings. Once I changed it to "create-only", they all vanished. But is it okay to go from "update" to "create-only"? – Zima

Well... for in-memory H2 it doesn’t really matter because the db will be destroyed on exit. – Malia

For any other persistent DBs you have to use Liqubase/Flyweight/DBMaintain or any other migration tool – Malia

Answer 3

You can create a custom Dialect and prevent the drop contraints statements from being executed as below:

public class CustomH2Dialect extends H2Dialect {

    @Override
    public boolean dropConstraints() {
        return false;
    }
}

And reference this in your properties:

spring.jpa.database-platform=my.package.CustomH2Dialect

Tawny

Answer 4

thanks, this work for me .

## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=postgres
# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQL9Dialect
# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto=create
logging.level.org.hibernate.SQL= DEBUG
hibernate.hbm2ddl.auto=update

spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
# Enable response compression
server.compression.enabled=true
# The comma-separated list of mime types that should be compressed
server.compression.mime-types=text/html,text/xml,text/plain,text/css,text/javascript,application/javascript,application/json
# Compress the response only if the response size is at least 1KB
server.compression.min-response-size=1024
server.http2.enabled=true
# Maximum time the response should be cached (in seconds)
spring.resources.cache.cachecontrol.max-age=120
# The cache must re-validate stale resources with the server. Any expired resources must not be used without re-validating.
spring.resources.cache.cachecontrol.must-revalidate=true
#https://www.callicoder.com/configuring-spring-boot-application/
server.port=8080

Foil

Sorry, but your example is not related to the problem that we trying to solve – Malia