Saga causes "hibernate_sequence" does not exist issue

This is the first time I’m using saga, until now it was ok.
The test is passing with matching expectation
I am using axon 4.6.1 and springboot 1.7.3 and java 17.0.4
I have 2 issues

  1. I get exception when the saga is in play
    I see this error
2022-10-13 08:22:30.614 ERROR 69773 --- [agaProcessor]-0] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: relation "hibernate_sequence" does not exist
  Position: 17
2022-10-13 08:22:30.618  WARN 69773 --- [agaProcessor]-0] o.a.e.TrackingEventProcessor             : Error occurred. Starting retry mode.

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:762) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:742) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
	at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:311) ~[spring-orm-5.3.22.jar:5.3.22]
	at jdk.proxy2/jdk.proxy2.$Proxy161.persist(Unknown Source) ~[na:na]
	at org.axonframework.modelling.saga.repository.jpa.JpaSagaStore.storeAssociationValue(JpaSagaStore.java:225) ~[axon-modelling-4.6.1.jar:4.6.1]
	at org.axonframework.modelling.saga.repository.jpa.JpaSagaStore.insertSaga(JpaSagaStore.java:304) ~[axon-modelling-4.6.1.jar:4.6.1]
	at org.axonframework.modelling.saga.repository.AnnotatedSagaRepository.storeSaga(AnnotatedSagaRepository.java:217) ~[axon-modelling-4.6.1.jar:4.6.1]
	at org.axonframework.modelling.saga.repository.AnnotatedSagaRepository.lambda$doCreateInstance$3(AnnotatedSagaRepository.java:139) ~[axon-modelling-4.6.1.jar:4.6.1]
	at org.axonframework.messaging.unitofwork.MessageProcessingContext.notifyHandlers(MessageProcessingContext.java:72) ~[axon-messaging-4.6.1.jar:4.6.1]
	at org.axonframework.messaging.unitofwork.BatchingUnitOfWork.lambda$notifyHandlers$2(BatchingUnitOfWork.java:161) ~[axon-messaging-4.6.1.jar:4.6.1]
	at java.base/java.util.ArrayList$Itr.forEachRemaining(ArrayList.java:1003) ~[na:na]
	at org.axonframework.messaging.unitofwork.BatchingUnitOfWork.notifyHandlers(BatchingUnitOfWork.java:161) ~[axon-messaging-4.6.1.jar:4.6.1]
	at org.axonframework.messaging.unitofwork.AbstractUnitOfWork.changePhase(AbstractUnitOfWork.java:236) ~[axon-messaging-4.6.1.jar:4.6.1]
	at org.axonframework.messaging.unitofwork.AbstractUnitOfWork.commitAsRoot(AbstractUnitOfWork.java:87) ~[axon-messaging-4.6.1.jar:4.6.1]
	at org.axonframework.messaging.unitofwork.AbstractUnitOfWork.commit(AbstractUnitOfWork.java:75) ~[axon-messaging-4.6.1.jar:4.6.1]
	at org.axonframework.messaging.unitofwork.BatchingUnitOfWork.executeWithResult(BatchingUnitOfWork.java:117) ~[axon-messaging-4.6.1.jar:4.6.1]
	at org.axonframework.eventhandling.AbstractEventProcessor.processInUnitOfWork(AbstractEventProcessor.java:166) ~[axon-messaging-4.6.1.jar:4.6.1]
	at org.axonframework.eventhandling.TrackingEventProcessor.processBatch(TrackingEventProcessor.java:490) ~[axon-messaging-4.6.1.jar:4.6.1]
	at org.axonframework.eventhandling.TrackingEventProcessor.processingLoop(TrackingEventProcessor.java:318) ~[axon-messaging-4.6.1.jar:4.6.1]
	at org.axonframework.eventhandling.TrackingEventProcessor$TrackingSegmentWorker.run(TrackingEventProcessor.java:1145) ~[axon-messaging-4.6.1.jar:4.6.1]
	at org.axonframework.eventhandling.TrackingEventProcessor$WorkerLauncher.cleanUp(TrackingEventProcessor.java:1340) ~[axon-messaging-4.6.1.jar:4.6.1]
	at org.axonframework.eventhandling.TrackingEventProcessor$WorkerLauncher.run(TrackingEventProcessor.java:1317) ~[axon-messaging-4.6.1.jar:4.6.1]
	at java.base/java.lang.Thread.run(Thread.java:833) ~[na:na]
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.id.enhanced.SequenceStructure$1.getNextValue(SequenceStructure.java:107) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.id.enhanced.NoopOptimizer.generate(NoopOptimizer.java:40) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.id.enhanced.SequenceStyleGenerator.generate(SequenceStyleGenerator.java:534) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:115) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:185) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:128) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:55) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:107) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:756) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	... 26 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: relation "hibernate_sequence" does not exist
  Position: 17
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) ~[postgresql-42.3.6.jar:42.3.6]
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) ~[postgresql-42.3.6.jar:42.3.6]
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) ~[postgresql-42.3.6.jar:42.3.6]
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490) ~[postgresql-42.3.6.jar:42.3.6]
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408) ~[postgresql-42.3.6.jar:42.3.6]
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:167) ~[postgresql-42.3.6.jar:42.3.6]
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:119) ~[postgresql-42.3.6.jar:42.3.6]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-4.0.3.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-4.0.3.jar:na]
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
	... 35 common frames omitted

am I missing any configuration for saga implementation?

  1. I observed another side effect, new saga get instantiated for the same event(failed for hibernate_sequence issue) multiple times. How can I avoid such behaviour? In this case associated property is same

Well, as you can see from the exception, the database solution you’ve picked does not have a hibernate_sequence. When Axon Framework wants to insert a Saga, it needs to auto-generate an index for the Saga in question.

If you’d be using a plain Spring Boot application with Spring Data JPA on the classpath, I would not expect this exception.
Furthermore, this isn’t really a predicament with Axon Framework configuration as you might’ve guessed. It has more to do with how you have configured your data source.
Hence, I would take a look at that configuration bit.
Note that Axon Framework doesn’t directly make any changes in there.

Next to this, I’d recommend you upgrade the Spring Boot version to the latest, which is 2.7.4. Perhaps this older dependency has some impact on the (auto-)configuration of your database.

1 Like

Thanks for guiding me to understand the problem better
After further investigation, I see we are using flyway for setting up schema with the help of sql script from this link

So I added the sql script to create hibernate sequence
CREATE SEQUENCE IF NOT EXISTS hibernate_sequence;
This helped to move forward.
But now I see new error

insert into saga_entry (revision, saga_type, serialized_saga, saga_id) values (?, ?, ?, ?)
2022-10-14 00:32:34.712  WARN 83620 --- [agaProcessor]-0] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42804
2022-10-14 00:32:34.712 ERROR 83620 --- [agaProcessor]-0] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: column "serialized_saga" is of type bytea but expression is of type bigint
  Hint: You will need to rewrite or cast the expression.
  Position: 88

When I look into AbstractSagaEntry code

@Lob
    @Column(length = 10000)
    protected T serializedSaga;

I’m not sure what the T could be
So I attach my saga code for reference

@Saga
public class PlayerAnonymizingSaga {
    @Autowired
    private transient CommandGateway commandGateway;

    private Set<String> friends;

    @StartSaga
    @SagaEventHandler(associationProperty = "playerSagaId")
    public void handle(final AnonymizedPlayerEvent event) throws JsonProcessingException {
        String playerId = event.getPlayerId();
        String playerSagaId = event.getPlayerId();
        friends = new ObjectMapper().readValue(event.getFriendsJson(), Set.class);

        friends.forEach(friend -> {
            RemoveFriendshipWithMeCommand removeFriendshipWithMeCommand =
                    RemoveFriendshipWithMeCommand.builder()
                            .playerId(friend)
                            .playerSagaId(playerSagaId)
                            .friendId(playerId)
                            .build();
            commandGateway.send(removeFriendshipWithMeCommand);
        });
    }

    @SagaEventHandler(associationProperty = "playerSagaId")
    public void handle(final RemovedFriendshipWithMeEvent event) {
        String playerId = event.getPlayerId();
        friends.remove(playerId);
        if (friends.isEmpty()) {
            SagaLifecycle.end();
        }
    }

}

It is symptom of problem I guess, I am not sure how it is going to adapt new versions of axon framework with improvements in saga design.
So what do you recommend in setting up database schema for axon support.
Definitely we don’t want to auto generate schema(DDL) on the fly in production

forgot mention in last message that we are using spring boot jpa

       <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

You could try to let hibernate generate Schema scripts for you (e.g. like in 37.5 Database Schema Creation - Java Platform, Enterprise Edition: The Java EE Tutorial (Release 7) ), use these files as a reference and then switch schema generation off before checking in the code.

1 Like

Finally changing data type of serialized_saga from bytea to bigint resolved the problem for now.

1 Like

@JohT, his suggestion would’ve helped, too, of course.
Although whether that’s something you and your team want to move toward is another question.

Apart from this valuable addition, I wanted to state that Axon Framework defaults to byte[] for all serialized objects it stores. Hence, you’ve solved it as intended; great!

Let me also share a bit about how we deal with breaking changes.
We are not planning to adjust the schema any time soon.
Especially within patch releases or minor releases, we do not make any breaking changes.

When we start working on Axon Framework 5 (the next major release), there may be a chance we will make some breaking changes in the schemas and APIs.
But if we do, you should expect we have a migration guide ready.

I hope that clarifies Axon Framework’s approach to database adjustments.

1 Like

@Steven_van_Beelen thanks for clarifying about version upgrade strategy and migration guide tips.

1 Like