Hibernate-sequence generates duplicate keys when using hibernate envers

I am using hibernate envers 5.6.9.final with spring boot 2.7.2.

my problem is that hibernate_sequence sometimes generates duplicates value when it needs to insert a new record in revisionInfo tabel. and it only happens on openshift platform and not my local system and not always it fails but sometimes it is running successfully.

this is how the AuditRivitionEntity is defined:

@Getter
@Setter
@Entity
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "revision_info")
@RevisionEntity(AuditRevisionListener.class)
@SequenceGenerator(name = "revision_seq", sequenceName = "hibernate_sequence")
@AttributeOverride(name = "timestamp", column = @Column(name = "rev_timestamp"))
@AttributeOverride(name = "id", column = @Column(name = "revision_id"))
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class AuditRevisionEntity extends DefaultRevisionEntity {

  @Column(name = "user")
  private String user;

  @Override
  public boolean equals(Object obj) {
    if (!super.equals(obj)) {
      return false;
    }
    return user.equals(((AuditRevisionEntity) obj).getUser());
  }

  @Override
  public int hashCode() {
    return Objects.hash(super.hashCode(), user);
  }

}

the issue is that it is useing hibernate_sequence to generate revision_id but on the server on which the system is deployed sometimes it trys to insert duplicate values for revision_id which are already inserted and used.

how come is it possible that a sequence generates a duplicate key while if I call SELECT nextval(hibernate_sequence) manually and it returns correct value.

this is where the entities are persisted and I expect the auditing be persisted as well:

  @Transactional
  public void saveNumberDirectoryEntry(NumberDirectoryEntry entry) {
    numberDirectoryEntryRepository.save(entry);
    numberDirectoryEntryUriRepository.saveAll(entry.getUriList());
  }

here is the log from hibernate:

Hibernate: 
    select
        nextval(hibernate_sequence)
[34m2023-11-14 15:10:32,378[0;39m [39mDEBUG[0;39m [[34mTopicSubscriptionManager[0;39m] [33mo.h.e.j.s.SqlStatementLogger.logStatement:144[0;39m - 
    insert 
    into
        revision_info
        (rev_timestamp, user, revision_id) 
    values
        (?, ?, ?)
Hibernate: 
    insert 
    into
        revision_info
        (rev_timestamp, user, revision_id) 
    values
        (?, ?, ?)
[34m2023-11-14 15:10:32,378[0;39m [39mTRACE[0;39m [[34mTopicSubscriptionManager[0;39m] [33mo.h.t.d.s.BasicBinder.bind:64[0;39m - binding parameter [1] as [BIGINT] - [1699974632377]
[34m2023-11-14 15:10:32,378[0;39m [39mTRACE[0;39m [[34mTopicSubscriptionManager[0;39m] [33mo.h.t.d.s.BasicBinder.bind:64[0;39m - binding parameter [2] as [VARCHAR] - [Erpm admin admin]
[34m2023-11-14 15:10:32,378[0;39m [39mTRACE[0;39m [[34mTopicSubscriptionManager[0;39m] [33mo.h.t.d.s.BasicBinder.bind:64[0;39m - binding parameter [3] as [INTEGER] - [2]
[34m2023-11-14 15:10:32,379[0;39m [31mWARN [0;39m [[34mTopicSubscriptionManager[0;39m] [33mo.m.j.u.l.Slf4JLogger.warn:100[0;39m - Error: 1062-23000: Duplicate entry '2' for key 'PRIMARY'
[34m2023-11-14 15:10:32,380[0;39m [31mWARN [0;39m [[34mTopicSubscriptionManager[0;39m] [33mo.h.e.j.s.SqlExceptionHelper.logExceptions:137[0;39m - SQL Error: 1062, SQLState: 23000
[34m2023-11-14 15:10:32,380[0;39m [1;31mERROR[0;39m [[34mTopicSubscriptionManager[0;39m] [33mo.h.e.j.s.SqlExceptionHelper.logExceptions:142[0;39m - (conn=1884477) Duplicate entry '2' for key 'PRIMARY'
[34m2023-11-14 15:10:32,381[0;39m [34mINFO [0;39m [[34mTopicSubscriptionManager[0;39m] [33mo.h.e.j.b.i.AbstractBatchImpl.release:213[0;39m - HHH000010: On release of batch it still contained JDBC statements
[34m2023-11-14 15:10:32,383[0;39m [1;31mERROR[0;39m [[34mTopicSubscriptionManager[0;39m] [33mi.k.e.s.w.n.CreateNumberDirectoryEntryWorker.execute:64[0;39m - Failed to persist number directory entry due to: Unable to perform beforeTransactionCompletion callback: org.hibernate.exception.ConstraintViolationException: could not execute statement; nested exception is org.hibernate.HibernateException: Unable to perform beforeTransactionCompletion callback: org.hibernate.exception.ConstraintViolationException: could not execute statement
[34m2023-11-14 15:10:32,385[0;39m [39mDEBUG[0;39m [[34mTopicSubscriptionManager[0;39m] [33mi.k.e.s.w.n.CreateNumberDirectoryEntryWorker.execute:67[0;39m - Some exception occurred while storing number directory entry: 
org.springframework.orm.jpa.JpaSystemException: Unable to perform beforeTransactionCompletion callback: org.hibernate.exception.ConstraintViolationException: could not execute statement; nested exception is org.hibernate.HibernateException: Unable to perform beforeTransactionCompletion callback: org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:331)

I get this error Duplicate entry '2' for key 'PRIMARY' while sequence should return 10.

I have tried to change the AuditRevisionEntity and added
@SequenceGenerator(name = "revision_seq", sequenceName = "hibernate_sequence") and also changes the sequence name but nothing has changes.
Also I change the boarder of transaction, exclude extra code from transaction and made as small as possible. or I removed the cascade strategy on the association of numberDirectoryEntry and Uri and persist the entity and Uri separately, no difference.

I am using Maria db 10.6

  • The issue is fixed: as mariadb supports auto-increment id generation. I changed the AuditRevisionEntity to a custom defined entiry and set id generation strategy to Identity and removed hibernate_sequence which for some strange reasons was returning invalid values.

    – 




Leave a Comment