DB2, ReplayingCluster, and JdbcEventStore - "result set is closed"

Just wondering if anyone has successfully used this combination: DB2, ReplayingCluster, and JdbcEventStore? I am getting “Invalid operation: result set is closed” errors.

ReplayingCluster calls transactionManager.commitTransaction() after commitThreshold events. DB2 ResultSet does not have the HOLD_CURSORS_OVER_COMMIT flag set by default. Unlike JpaEventStore, JdbcEventStore’s Iterator implementation doesn’t pre-fetch all of the events in a batch. It also calls ResultSet.next() preemptively, instead of waiting until the next loop calls hasNext(). So it can’t deal with the ResultSet being closed prematurely.

So far I haven’t found how to set HOLD_CURSORS_OVER_COMMIT for these individual queries and it seems like a bad idea anyway. (You can use Connection.setHoldability(HOLD_CURSORS_OVER_COMMIT) but that affects the whole pool.)

For my immediate use case we can get away with a NoTransactionManager, so that’s what I’ve done for now.

Another option might be to create a second JdbcEventStore instance with a different DataSource for use by the ReplayingCluster – no need for the EventStoreManagement to be transactional, just the event handlers.

org.axonframework.eventstore.EventStoreException: Exception occurred while attempting to fetch data from ResultSet
at org.axonframework.eventstore.jdbc.DefaultEventEntryStore$ResultSetIterator.hasNext(DefaultEventEntryStore.java:481) ~[axon-core-2.4.1.jar:2.4.1]
at org.axonframework.eventstore.jdbc.DefaultEventEntryStore$PreparedStatementIterator.hasNext(DefaultEventEntryStore.java:442) ~[axon-core-2.4.1.jar:2.4.1]
at org.axonframework.eventstore.jdbc.DefaultEventEntryStore$FilteredBatchingIterator.next(DefaultEventEntryStore.java:402) ~[axon-core-2.4.1.jar:2.4.1]
at org.axonframework.eventstore.jdbc.DefaultEventEntryStore$FilteredBatchingIterator.next(DefaultEventEntryStore.java:311) ~[axon-core-2.4.1.jar:2.4.1]
at org.axonframework.eventstore.jdbc.DefaultEventEntryStore$ConnectionResourceManagingIterator.next(DefaultEventEntryStore.java:293) ~[axon-core-2.4.1.jar:2.4.1]
at org.axonframework.eventstore.jdbc.DefaultEventEntryStore$ConnectionResourceManagingIterator.next(DefaultEventEntryStore.java:275) ~[axon-core-2.4.1.jar:2.4.1]
at org.axonframework.eventstore.jdbc.JdbcEventStore$IteratorDomainEventStream.initializeNextItem(JdbcEventStore.java:391) ~[axon-core-2.4.1.jar:2.4.1]
at org.axonframework.eventstore.jdbc.JdbcEventStore$IteratorDomainEventStream.next(JdbcEventStore.java:385) ~[axon-core-2.4.1.jar:2.4.1]
at org.axonframework.eventstore.jdbc.JdbcEventStore.doVisitEvents(JdbcEventStore.java:296) ~[axon-core-2.4.1.jar:2.4.1]
at org.axonframework.eventstore.jdbc.JdbcEventStore.visitEvents(JdbcEventStore.java:280) ~[axon-core-2.4.1.jar:2.4.1]
at org.axonframework.eventhandling.replay.ReplayingCluster$ReplayEventsTask.run(ReplayingCluster.java:303) ~[axon-core-2.4.1.jar:2.4.1]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_25]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_25]
at org.axonframework.common.DirectExecutor.execute(DirectExecutor.java:42) [axon-core-2.4.1.jar:2.4.1]
at org.axonframework.eventhandling.replay.ReplayingCluster.startReplay(ReplayingCluster.java:172) [axon-core-2.4.1.jar:2.4.1]
at org.axonframework.eventhandling.replay.ReplayingCluster.startReplay(ReplayingCluster.java:136) [axon-core-2.4.1.jar:2.4.1]

Caused by: com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][4.19.26] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
at com.ibm.db2.jcc.am.kd.a(Unknown Source) ~[db2jcc-4.19.26.jar:na]
at com.ibm.db2.jcc.am.kd.a(Unknown Source) ~[db2jcc-4.19.26.jar:na]
at com.ibm.db2.jcc.am.kd.a(Unknown Source) ~[db2jcc-4.19.26.jar:na]
at com.ibm.db2.jcc.am.ResultSet.checkForClosedResultSet(Unknown Source) ~[db2jcc-4.19.26.jar:na]
at com.ibm.db2.jcc.am.ResultSet.nextX(Unknown Source) ~[db2jcc-4.19.26.jar:na]
at com.ibm.db2.jcc.am.ResultSet.next(Unknown Source) ~[db2jcc-4.19.26.jar:na]
at com.mchange.v2.c3p0.impl.NewProxyResultSet.next(NewProxyResultSet.java:2859) ~[c3p0-0.9.1.1.jar:0.9.1.1]
at org.axonframework.eventstore.jdbc.DefaultEventEntryStore$ResultSetIterator.establishNext(DefaultEventEntryStore.java:487) ~[axon-core-2.4.1.jar:2.4.1]
at org.axonframework.eventstore.jdbc.DefaultEventEntryStore$ResultSetIterator.hasNext(DefaultEventEntryStore.java:478) ~[axon-core-2.4.1.jar:2.4.1]
… 52 common frames omitted

-Peter

Hi Peter,

I think a timeout has occurred on this question :slight_smile:

I have been thinking about this one for a while, and think that the JdbcEventStore should check for the resultset status each time the next item is retrieved. If the resultset is closed, it should perform a new query. Perhaps it should also read ahead a bit, so that the batch size could be larger than the transaction size.

For your scenario, it would absolutely make sense to use a different connection the reading, because it doesn’t need to be part of the transaction anyway.

Cheers,

Allard

Revisiting this issue – finally had need for a transactional event handler, and discovered it is not so simple just to use a different connection for the event store management. Must use a DataSourceConnectionProvider instead of a SpringDataSourceConnectionProvider, lest Spring synchronize the EventStoreManagement’s Connection with the ReplayingCluster’s open transaction, which causes it to commit anyway!

P.S. If I had a dollar for every line of Spring transaction code I’ve stepped through…

-Peter

Glad you got it working. Sometimes ‘magic’ comes with undesired side-effects.

PS. I’d be rich too :wink: