REVINFO table is missing the sequence "revinfo_seq"

Question

I am migrating to SpringBoot 3.0.1 and updated "hibernate-envers" version to "6.1.6.Final". My DB is PostgreSQL 13.6. Hibernate is configured to create the DB schema: spring.jpa.hibernate.ddl-auto:create

After starting the application I get the following error:

pim 2022-12-27 12:00:13,715 WARN  C#c7b942ec-33b4-4749-b113-22cbb2946a8d [http-nio-9637-exec-1]     SqlExceptionHelper/133              - SQL Error: 0, SQLState: 42P01
pim 2022-12-27 12:00:13,715 ERROR C#c7b942ec-33b4-4749-b113-22cbb2946a8d [http-nio-9637-exec-1]     SqlExceptionHelper/138              - ERROR: relation "revinfo_seq" does not exist
  Position: 16

The revinfo table look like this:

create table revinfo
(
    revision           bigint not null
        primary key,
    client_id          varchar(255),
    correlation_id     varchar(255),
    origin             varchar(255),
    request_id         varchar(255),
    revision_timestamp bigint not null,
    timestamp_utc      timestamp with time zone,
    user_name          varchar(255)
);

The sequence "revinfo_seq" does not exist, but in the old DB structure with envers

5.6.8.Final

and SpringBoot 2.6.6 it didn't exist either without any problems. What am i Missing?

I tried to toggle the paramter

org.hibernate.envers.use_revision_entity_with_native_id

but it did not help.

Posada

Same problem here. Spring Boot 3.0.1. Previously, with 2.7.7 worked just fine. LocalContainerEntityManagerFactoryBean : Failed to initialize JPA EntityManagerFactory: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing sequence [revinfo_seq] – Lafayette

In my case, my schema is created with Liquibase. And worked fine with previous versions. In my PostgreSQL instance, I can see that the sequence is named in a different way: revinfo_rev_seq – Lafayette

I switched off Liquibase and now use Hibernate only. With SpringBoot 2.6.6: When using Hibernate 5.6.7 together with Hibernate-envers 5.6.8 the sequence "revinfo_seq" is created during application startup and everything works fine. With SpringBoot 3.0.1: When using Hibernate 6.1.6 together with Hibernate-envers 6.1.6 no sequence like "revinfo_seq" or "revinfo_rev_seq" is created and an error occurs when trying to do an insert to the table. I think I will raise an issue to th envers project. – Posada

Same problem here, envers, spring boot 3.0 and liquibase. revinfo_rev_seq always was created in spring boot 2.x and is still be created in 3.x. I tried renaming it to revinfo_seq but the error message stays the same. – Wizen

Answer 1

You can solve it with this property:

spring.jpa.properties.hibernate.id.db_structure_naming_strategy: legacy

Tested with Spring Boot 3.0.1

Reason:

Hibernate 6 changed the sequence naming strategy, so it was searching for a sequence ending with "_seq". You can read a really detailed explanation here: https://thorben-janssen.com/sequence-naming-strategies-in-hibernate-6/

Lafayette

Thanks. This solved my problem. There is also some nice info over at the JpaBuddy site. jpa-buddy.com/blog/hibernate6-whats-new-and-why-its-important – Dalessio

Thanks. After that change new error appeared: ERROR: column "revtstmp" of relation "revinfo" does not exist 🤔 – Houston

Works, although the word legacy is a bit of a turn-off. But still better than adding (or, more likely, forgetting to add) a sequence each time you create a new entity. Also cf. Hibernate's original Migration Guide: docs.jboss.org/hibernate/orm/6.0/migration-guide/… – Bestir

Answer 2

Switching the naming strategy didn't help in my case. I created a custom revision entity and set the id generation strategy to identity to solve the problem:

@Entity
@RevisionEntity
@Table(name="revinfo")
public class RevInfoEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @RevisionNumber
    @Column(name="rev")
    private long rev;

    @RevisionTimestamp
    @Column(name="revtstmp")
    private LocalDateTime revtstmp;
}

If you store the timestamp as bigint, you may have to change the type of revtstmp to long.

Recapture

The custom revision entity worked for me as well. The revtmsmp comment was also helpful. The legacy behavior changed in the accepted answer caused other issues for me. – September