axon 3.2 + oracle11: sequence not found

Imported from Google Groups
1

Hello,

I am getting the following error when trying to run a command on a Aggregate root:

o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 2289, SQLState: 42000 o.h.engine.jdbc.spi.SqlExceptionHelper : ORA-02289: sequence does not exist o.a.c.gateway.DefaultCommandGateway : Command '...' resulted in org.axonframework.eventsourcing.eventstore.EventStoreException(An event for aggregate [75ef9257c22e45fd90a62067eae9bffb] at sequence [0] could not be persisted)

I am using spring-boot starter, so axon is using Jpa as the event store.

I have been creating the oracle schema using the following code:


private Oracle11EventTableFactory tableFactory = new Oracle11EventTableFactory();
private EventSchema eventSchema = new EventSchema();

public void setupDatabase() throws SQLException {
 Connection connection = dataSource.getConnection();
 tableFactory.createDomainEventTable(connection, eventSchema);
 tableFactory.createSnapshotEventTable(connection, eventSchema);
 connection.close();
}

I see that the following is created:

  • table DOMAINEVENTENTRY
  • oracle sequence DOMAINEVENTENTRY_SEQ
  • a trigger DOMAINEVENTENTRY_ID
    But once this is created, I am still getting the errors listed above.

I have no idea why Oracle is complaining about the sequence not existing. Can you help me out?

2

Also not: I see the DOMAINEVENTENTRY being created, but not the snapshotevent table.

3

I have this issue when using Jpa (default spring boot configuration) for the EventStore (the Autoconfigured JpaTransactionManager, the Jpa StorageEngine etc).
When I configure the Axon evenstore with plain Jdbc (JdbcStorageEngine, DataSourceTransaction manager etc), I do not have issues with Oracle (same table structure). But as we will probably be using Jpa for the query model, I would like to use it as well for the EventStore.

4

Hi Stijn,

all tables should be automatically created when using default. However, if you specify any entities explicitly, you will also need to specify the Axon ones.

I’m wondering, are you letting JPA/Hibernate(?) generate the tables, or are you using the Oracle11EventTableFactory? If it’s the latter, it should show the SQL that’s being used to generate the tables. It creates a sequence (called tableName + “_seq”) and a trigger to set the globalSequence based on a sequence value when it’s inserted.

Can you confirm the sequence is created? Also check if the sequence really points to a value greater than what’s already inserted. If the sequence got recreated somehow, it may restart at 0, while there is already data in the table, causing conflicts.

Cheers,

Allard

5

Hi Allard,

thanks for your response.

We we are using Hibernate, but do not let hibernate generate the tables. Our application has typical no ddl permissions on the database, therefore, the database structure is always generated in advance in our deployment process.

We generally use flyway to create the database structure (during the deploy steps). For Axon, we were trying to use Oracle11EventTableFactory, to get a correct definition in the database. From there, we would take the necessary sql statements, and add it to our flyway scripts.

For mapping the axon entities, we have added the following to our spring boot application:

`

@EntityScan({"ourpackage.with.entities","org.axonframework.eventsourcing.eventstore.jpa","org.axonframework.eventhandling.saga.repository.jpa"})

`

What we see now is that Oracle11EventTableFactory is indeed generating tables and sequence:

  • table DOMAINEVENTENTRY
  • oracle sequence DOMAINEVENTENTRY_SEQ
  • a trigger DOMAINEVENTENTRY_ID
    We do not see the logging of DDL sql statements from Oracle11EventTableFactory (which logger should we activate for that?)

However, when running the application, we see now (after activating Hibernate logging) the following errors:

`
2018-03-26 10:42:03.547 DEBUG 8100 — [nio-8090-exec-8] org.hibernate.SQL : select our_schema.hibernate_sequence.nextval from dual
2018-03-26 10:42:03.547 TRACE 8100 — [nio-8090-exec-8] o.h.r.j.i.ResourceRegistryStandardImpl : Registering statement [oracle.jdbc.driver.OraclePreparedStatementWrapper@78d8a458]
2018-03-26 10:42:03.554 DEBUG 8100 — [nio-8090-exec-8] o.h.engine.jdbc.spi.SqlExceptionHelper : could not extract ResultSet [n/a]

`

So the sequence that Axon or hibernate tries to use, is not domainevententry_seq, as generated by Oracle11EventTableFactory, but hibernate_sequence.

When renaming the sequence, were are hitting problems with the Hibernate naming policy (Insert into DOMAIN_EVENT_ENTRY instead of DOMAINEVENTENTRY etc). We did not provide a custom EventSchema.

So I assume that the structure that Oracle11EventTableFactory generates, is suitable for the JdbcStorageEngine, but not for JpaStorageEngine?

Stijn

6

Hi Stijn,

the way the trigger works with the JDBC is probably not compatible with how Hibernate expects to work with a sequence. My recommendation, when using Hibernate, is to have Hibernate generate the tables once, and copy the creation scripts to flyway. From that point onwards, you can turn table generation off.

Hibernate uses the “hibernate_sequence” by default. We recommend overriding this, so that you do not share the DomainEventEntry’s sequence with any other tables. Unfortunately, we couldn’t configure this by default, as it would cause problems with some other database implementations.

Place a file called orm.xml in a META-INF folder on your classpath, with the following content.

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="[http://java.sun.com/xml/ns/persistence/orm](http://java.sun.com/xml/ns/persistence/orm)" version="2.0">
    <mapped-superclass class="org.axonframework.eventsourcing.eventstore.AbstractSequencedDomainEventEntry" access="FIELD">
        <attributes>
            <id name="globalIndex">
                <generated-value generator="domainEventGenerator" strategy="SEQUENCE"/>
            </id>
        </attributes>
    </mapped-superclass>
    <entity class="org.axonframework.eventsourcing.eventstore.jpa.DomainEventEntry" access="FIELD">
        <sequence-generator name="domainEventGenerator" sequence-name="domain_event_seq" allocation-size="1"/>
    </entity>
</entity-mappings>

You may want to tune the names for the sequences a bit.
Alternatively, you can leave the JDBC-generated trigger approach, but then instruct Hibernate (using the orm.xml) to not use a generated value at all. Removing the element from the XML might do that trick. Note that we haven’t tested this.

Hope this helps.
Cheers,

Allard

7

Hi Allard,

This most certainly helps! We did as you explained:

First, we have set the following property in spring boot’s application.yml (only temporarily)

spring.jpa.hibernate.ddl-auto: create-drop

This generated the tables in the database, from which we extracted our sql scripts.

We also customized the seqence for the AssociationVAlues in our orm.xml:

`

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm" version="2.0">
    <mapped-superclass class="org.axonframework.eventsourcing.eventstore.AbstractSequencedDomainEventEntry" access="FIELD">
        <attributes>
            <id name="globalIndex">
                <generated-value generator="domainEventGenerator" strategy="SEQUENCE"/>
            </id>
        </attributes>
    </mapped-superclass>
    <entity class="org.axonframework.eventsourcing.eventstore.jpa.DomainEventEntry" access="FIELD">
        <sequence-generator name="domainEventGenerator" sequence-name="DOMAIN_EVENT_ENTRY_SEQ" allocation-size="1"/>
    </entity>
    <entity class="org.axonframework.eventhandling.saga.repository.jpa.AssociationValueEntry" access="FIELD">
        <sequence-generator name="associationValueEntryGenerator"
                            sequence-name="ASSOCIATION_VALUE_ENTRY_SEQ"
                            allocation-size="1" />
        <attributes>
            <id name="id">
                <generated-value generator="associationValueEntryGenerator" strategy="SEQUENCE" />
            </id>
        </attributes>
    </entity>
</entity-mappings>

`

Thanks for your reply!

Stijn