oracle inlist over 1000 error

Justin_Hollander · August 29, 2017, 4:07pm

** Using Axon 3.05 with an Oracle backend **

Hi There,

I am wondering if this is a bug. Occasionally i get the error:

09:02:21,129 ERROR [org.axonframework.eventsourcing.eventstore.EmbeddedEventStore] (EmbeddedEventStore-0) Failed to read events from the underlying event storage: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)
at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:492)
at org.axonframework.eventsourcing.eventstore.jpa.JpaEventStorageEngine.lambda$fetchTrackedEvents$1(JpaEventStorageEngine.java:156)
at org.axonframework.common.transaction.TransactionManager.fetchInTransaction(TransactionManager.java:67)
at org.axonframework.eventsourcing.eventstore.jpa.JpaEventStorageEngine.fetchTrackedEvents(JpaEventStorageEngine.java:136)
at org.axonframework.eventsourcing.eventstore.BatchingEventStorageEngine.lambda$readEventData$1(BatchingEventStorageEngine.java:119)
at org.axonframework.eventsourcing.eventstore.BatchingEventStorageEngine$EventStreamSpliterator.tryAdvance(BatchingEventStorageEngine.java:157)
at java.util.Spliterator.forEachRemaining(Spliterator.java:326)
at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
at java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151)
at java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174)
at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
at java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:418)
at org.axonframework.eventsourcing.eventstore.EmbeddedEventStore$EventProducer.fetchData(EmbeddedEventStore.java:239)
at org.axonframework.eventsourcing.eventstore.EmbeddedEventStore$EventProducer.run(EmbeddedEventStore.java:205)
at org.axonframework.eventsourcing.eventstore.EmbeddedEventStore$EventProducer.access$2000(EmbeddedEventStore.java:184)
at org.axonframework.eventsourcing.eventstore.EmbeddedEventStore.lambda$ensureProducerStarted$0(EmbeddedEventStore.java:134)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2117)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876)
at org.hibernate.loader.Loader.doQuery(Loader.java:919)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)
at org.hibernate.loader.Loader.doList(Loader.java:2617)
at org.hibernate.loader.Loader.doList(Loader.java:2600)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429)
at org.hibernate.loader.Loader.list(Loader.java:2424)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1326)
at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)
at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606)
at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483)
… 17 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
… 33 more

I am wondering if there is a bug in the framework that is causing this. Oracle in-lists can only be a maximum of 1000 entries. We are currently doing stability testing with the framework and had been noticing this from time to time.

Justin.

Frans_van_Buul · August 29, 2017, 9:28pm

Hi Justin,

We’ve seen this issue before, related to the GapAwareTrackingToken. Until recently. gaps in sequence numbers were tracked indefinitely and that can hit IN-query limits at some point.

Issue 391 has been created about this topic and it was resolved in 3.0.6. So, upgrading from 3.0.5 to 3.0.6 may help fix this issue for you.

Kind regards,

Frans van Buul

Justin_Hollander · August 29, 2017, 10:27pm

That’s excellent! Thanks guys and great work by the way.

allardbz · August 30, 2017, 11:03am

Hi Justin,

some more information about this issue. It is caused by the fact that Hibernate, by default, uses a generic sequence generator if none is explicitly provided. This causes these gaps to appear in the sequence of events. While migrating to Axon 3.0.6 will most likely relieve the situation (Axon will attempt to clean up gaps), it doesn’t solve the root cause.

To fix the appearance of gaps in these sequences, you can override Axon’s JPA configuration using a orm.xml file (which needs to be placed in META-INF):

<?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>

This will tell JPA to use a specific generator for the DomainEventEntry table, while the rest still use the generic generator, created by Hibernate by default.

Cheers,

Allard