@Column(unique = true) produces a WARN o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Warning Code: 0, SQLState: 00000

Question

In a project with Spring Boot 2.0.0.RELEASE, when I start the application the first time, when the database tables are being created, I'm getting the following warning message:

Hibernate: alter table if exists bpermission drop constraint if exists UK_qhp5om4s0bcb6j0j8pgcwitke
2018-03-14 11:32:03.833  WARN 15999 --- [  restartedMain] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Warning Code: 0, SQLState: 00000
2018-03-14 11:32:03.833  WARN 15999 --- [  restartedMain] o.h.engine.jdbc.spi.SqlExceptionHelper   : constraint "uk_qhp5om4s0bcb6j0j8pgcwitke" of relation "bpermission" does not exist, skipping

Notice the constraint name -> uk_qhp5om4s0bcb6j0j8pgcwitke

...and below I'm seeing this being logged:

Hibernate: alter table if exists bpermission add constraint UK_qhp5om4s0bcb6j0j8pgcwitke unique (label)
Hibernate: alter table if exists bpermission drop constraint if exists UK_ow4uw3orjjykeq869spvqtv6u

From the previous message we can see Hibernate is adding the constraint UK_qhp5om4s0bcb6j0j8pgcwitke, the same as the one shown in the warning but the first letter is uppercase. This is related to the unique constraint in the label property (see class below).

The (possible) involved entities in getting this warning are:

BPermission

@Data
@NoArgsConstructor(force = true)
@RequiredArgsConstructor
@EqualsAndHashCode(callSuper = true, exclude = "roles")
@ToString(callSuper = true, exclude = "roles")
@Entity
public class BPermission extends GmsEntity {

    @NotNull()
    @NotBlank()
    @Size(max = 255)
    @Pattern(regexp = "someDefinedRegexp")
    @Column(unique = true, nullable = false, length = 255)
    private final String name;

    @NotNull()
    @NotBlank()
    @Size(max = 255)
    @Column(unique = true, nullable = false, length = 255)
    private final String label;

    @ManyToMany(mappedBy = "permissions")
    private Set<BRole> roles;
}

BPermission is related (in case this info helps in any way) to

BRole

@Data
@NoArgsConstructor(force = true)
@RequiredArgsConstructor
@EqualsAndHashCode(callSuper = true, exclude = "permissions")
@ToString(callSuper = true, exclude = {"description", "permissions"})
@Entity
public class BRole extends GmsEntity{

    @NotNull()
    @NotBlank()
    @Size(max = 255)
    @Pattern(regexp = "someDefinedRegexp"))
    @Column(unique = true, nullable = false, length = 255)
    private final String label;

    @Size(max = 10485760)
    @Column(length = 10485760)
    private String description;

    private Boolean enabled = false;

    @ManyToMany
    @JoinTable(
            name = "brole_bpermission",
            joinColumns = @JoinColumn(name = "brole_id"),
            inverseJoinColumns = @JoinColumn(name = "bpermission_id")
    )
    private Set<BPermission> permissions;

    public void addPermission(BPermission... p) {
        // code for adding permissions
    }

    public void removePermission(BPermission... p) {
        // code for removing permissions
    }

    public void removeAllPermissions() {
        // code for removing permissions
    }

They are mapped to a PostgreSQL9.5.11 database as follow:

The related spring configurations are:

spring.datasource.url = jdbc:postgresql://127.0.0.1/mydbname
spring.datasource.username = postgres
spring.datasource.password = postgres
spring.datasource.driver-class-name = org.postgresql.Driver
spring.jpa.hibernate.ddl-auto = update
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation = true
spring.jpa.open-in-view = false

I'm getting the mentioned warning message for all entities with properties which are annotated with @Column(unique = true)

Question

• Why is this warning being thrown? Maybe... a bug?
• How can I get rid of it?

---

Of course, a warning sometimes is not bad at all, but I feel here this is either unnecessary or it is indicating that "something should be done differently" despite SQLCode 0000 means "successful_completion".

PS: I'm using Lombok.

Answer 1

Spring Boot 2.0 (Hibernate 5?) apparently uses DROP_RECREATE_QUIETLY as unique constraint update strategy which is really wrong as a default option, because simply what it does each time you start the app is removing the unique index and creating it again. If you work on databases with some (a lot of?) data I can imagine how slow will be the start of everything with this option.

In such scenario, when you start on empty database, operation of removing the index generates warning which you can see in the logs. When you start again, the warning dissapears but it silently does the expensive operation of recreating index.

To disable this you need to switch the strategy back to RECREATE_QUIETLY with following params:

# for plain hibernate
hibernate.schema_update.unique_constraint_strategy=RECREATE_QUIETLY

# for spring data
spring.jpa.properties.hibernate.schema_update.unique_constraint_strategy=RECREATE_QUIETLY

Answer 2

This looks like a bug.

I'd recommend you to create the schema via higher-level migration tools like flywaydb and let hibernate only validate the generated schema. It is integrated to spring-boot and it is very easy to setup, see the documantation and examples.

The benefit is that you have full control on the schema, you don't have unexpected schema changes while upgrading hibernate.

Usually automatical schema generation is used only during development, but not in production. You can find more details on why it is so important here.

Having such setup you might let hibernate generate the schema only in development mode, but flighway will take responsibility for the rest of the cases.