Tests failing when upgrading to Spring Boot 2.7 - "CommandAcceptanceException: Error executing DDL"

user_6661361 0
Viewed 1289

After upgrading to Boot 2.7 the integration tests that were using an embedded H2 database started failing.

I see this WARN message in the logs, but it's not very clear the cause or the solution for this:

WARN 8053 ---[           main] o.h.t.s.i.ExceptionHandlerLoggedImpl     :GenerationTarget encountered exception accepting command : Error executing DDL "create table user (id bigint generated by default as identity, email varchar(255) not null, name varchar(255), primary key (id))" via JDBC Statement

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table user (id bigint generated by default as identity, email varchar(255) not null, name varchar(255), primary key (id))" via JDBC Statement
...
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "create table [*]user (id bigint generated by default as identity, email varchar(255) not null, name varchar(255), primary key (id))"; expected "identifier"; SQL statement:
create table user (id bigint generated by default as identity, email varchar(255) not null, name varchar(255), primary key (id)) [42001-212]
...

It seems my User table is not created after the upgrade, thus making my tests fail.

spring-data-jpa spring-boot h2 hibernate
2 Answers

It seems Boot 2.7 upgraded to its H2 dependency to 2.x, which is not backward compatible and introduces several changes: https://github.com/spring-projects/spring-boot/wiki/Spring-Boot-2.7-Release-Notes#h2-21

The issue was that User is now a Keyword / reserved word (The H2 "migration-to-v2" guide was not very helpful in my case; it mentioned new keywords were added, but it didn't provide a link to ):

https://www.h2database.com/html/advanced.html#keywords

So, what I had to do is use "quoted names" to define the Table name of my Entity (it seems I can use backticks in the table annotation too instead of escaping double quotes):

@Table(name="\"user\"")
@Entity
public class User {
...

I also had to use double quotes on my data.sql files for this table:

INSERT INTO "user"(id, email, name) VALUES(1, 'test@user.com', 'Test User');

Note: the migration guide also mentions the possibility of using the SET NON_KEYWORDS command as a workaround, but it also discourages it.

user_66613610

Add the following to your src/test/resources/application-test.properties file (assuming your tests run with the test profile):

spring.jpa.properties.hibernate.globally_quoted_identifiers=true
spring.jpa.properties.hibernate.globally_quoted_identifiers_skip_column_definitions = true

If any of your JPA entities have UUID fields, ensure those fields are annotated with @Column and the annotation's columnDefinition defines the column as type UDID. (In its simplest form: @Column(columnDefinition="UDID").) This works around a Hibernate bug.

user_5846700
Related