Java 带有 Hibernate 的 Spring Boot 在使用 H2 数据库启动时生成删除约束错误

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

提问by Aaron Zeckoski

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

我正在使用 spring-boot 并且有一个像这样配置的 H2 数据库（在 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.

即使 hibernate 将这些报告为错误，我也可以登录到 H2 控制台并查看约束，它们似乎很好。

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?

如果真的看起来像 hibernate 试图在它实际创建数据库之前删除这些约束（即 hibernate 中的某种错误）。有什么方法可以避免这些错误堵塞日志或指示某处真正的故障？

UPDATE 1

更新 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

注意：来源在这里：https: //github.com/azeckoski/lti_starter

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

特别是配置：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

和模型：https: //github.com/azeckoski/lti_starter/tree/master/src/main/java/ltistarter/model

回答by Vlad Mihalcea

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

因为您使用的是内存数据库，所以 Hibernate 在执行时不会找到任何表：

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][]} 
  

• 删除约束（fk）
• 放下桌子
• 创建表

• 创建约束（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 更改为更新来解决此问题：

hibernate.hbm2ddl.auto=update

回答by user1470687

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

回答by Sergey Ponomarev

Try create-only:

尝试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

它至少从 5.2（甚至更早）开始被添加到 Hibernate 中，我没有找到任何合适的文档，但它提到了用户指南：23.15。自动模式生成

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

这可能是误报，实际上它有效