Migration of Java 17 and Spring Boot 3.1.2 results in PRIMARY KEY violation (data_tracing.domain_event_entry_seq)

Axon Framework
1

Hi all,

currently the Axon setup is running with Java 11, Spring Boot 2.7.14 and axon-spring-boot-starter 4.8.0.
I try to migrate to Java 17 and Spring Boot 3.1.2 using the official upgrading guide: Upgrading to 4.7 - Axon Reference Guide

What I’ve changed:

  1. all javax imports are adjusted to jakarta
  2. new sequences has been created dt.domain_event_entry_seq and initialized with the old value +1 of the deprecated hibernate_sequence

The architecture overview: the application is consuming messages from eventhub and publishing them on the Axon EventBus as GenericEventMessage. We are using the Event Store (Azure SQL DB) to store the events and some Eventhandler (PooledStreamingEventProcessor based) to project the events into variouse query models.
No magic.

The problems that occure with Java 17 + SB 3.1.2 + Axon (4.7.6, 4.8.0 and 4.8.1):

although the db sequence dt.domain_event_entry_seq has been initialized with the deprecated hibernate_sequence value +1, the events can’t be stored to the event store because of primary key viaolation.

2023-08-21 11:37:52.430 org.springframework.kafka.KafkaListenerEndpointContainer#0-0-C-1 [ERROR] [KafkaMessageListenerContainer] - Error handler threw an exception
com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK__domain_e__30BB9782A875E41D'. Cannot insert duplicate key in object 'dt.domain_event_entry'. The duplicate key value is (864145026).
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1676)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:620)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:540)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7620)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3916)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:268)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:242)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:486)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:275)
	... 62 common frames omitted
Wrapped by: org.hibernate.exception.ConstraintViolationException: could not execute statement [Violation of PRIMARY KEY constraint 'PK__domain_e__30BB9782A875E41D'. Cannot insert duplicate key in object 'dt.domain_event_entry'. The duplicate key value is (864145026).]

domain_event_entry DDL:

CREATE TABLE dt.domain_event_entry (
    global_index         BIGINT         NOT NULL
        PRIMARY KEY,
    event_identifier     NVARCHAR(255)  NOT NULL,
    meta_data            VARBINARY(MAX),
    payload              VARBINARY(MAX) NOT NULL,
    payload_revision     NVARCHAR(255),
    payload_type         NVARCHAR(255)  NOT NULL,
    time_stamp           NVARCHAR(255)  NOT NULL,
    aggregate_identifier NVARCHAR(255)  NOT NULL,
    sequence_number      BIGINT         NOT NULL,
    type                 NVARCHAR(255),
    partner_id           AS CONVERT([varchar](36), JSON_VALUE(CONVERT([varchar](MAX), [payload]), '$.partnerId')),
    event_uuid           AS CONVERT([uniqueidentifier], JSON_VALUE(CONVERT([varchar](MAX), [payload]), '$.eventUID'))
)

What am I missing? Happy to get some feedback.

Best Regards

2

Just to state the obvious, but the only thing you’re doing is adjusting the Java and Spring Boot versions, right?
So, you already are using the latest AF version for your setup, correct?

And, based on this:

It seems you also tried things out with AF 4.7.6 and 4.8.0. And, I assume, with the same exception you’ve shared, right?

If you check the database you’re using as an event store (note I don’t have any experience with Azure SQL DB), does the sequence show any overlap with the previous sequence?
Axon Framework bases the globalIndex (the primary key from the RDBMS’ perspective) on the sequence generator set for the domain_event_entry table, without any framework-specific magic in there.

By checking online docs concerning the new sequence approach for Hibernate 6, it seems to me like you’re taking the right angle to it to be honest with you…

Just curious, but have you tried to make this migration without the new sequence?
There’s a property for a persistence.xml, as described in this blog post (which I’ve referred to here) that allows you to use the old approach.
The hibernate.id.db_structure_naming_strategy property to be exact.

I would suggest trying out whether that works just to rule out whether the predicament is internal to AF or to some other component of your setup. From there, we can investigate further to get you to a new version of the JDK and SB. :slight_smile: