Recently we’ve decided to use the JPA Read-Side with the Oracle database and Hibernate. We preferred to manually create the table to store offsets. So we created the script based on https://www.lagomframework.com/documentation/1.4.x/java/ReadSideJPA.html#Building-the-read-side-handler
CREATE TABLE "read_side_offsets" (
"read_side_id" VARCHAR(255),
"tag" VARCHAR(255),
"sequence_offset" NUMBER, /* it was BIGINT in the original script */
"time_uuid_offset" CHAR(36),
PRIMARY KEY ("read_side_id", "tag")
);
So far so good. But when the first event of a Persistent Entity arrives to the read-side handler then the error occurs
2019-03-07T13:32:54.862+0300 [e[33mwarne[0m] org.hibernate.engine.jdbc.spi.SqlExceptionHelper [] - SQL Error: 932, SQLState: 42000
2019-03-07T13:32:54.862+0300 [e[31merrore[0m] org.hibernate.engine.jdbc.spi.SqlExceptionHelper [] - ORA-00932: inconsistent datatypes: expected NUMBER got BINARY
2019-03-07T13:32:54.865+0300 [e[33mwarne[0m] akka.stream.scaladsl.RestartWithBackoffSource [sourceThread=oms-delivery-service-akka.actor.default-dispatcher-14, akkaTimestamp=10:32:54.865UTC, akkaSource=RestartWithBackoffSource(akka://oms-delivery-service), sourceActorSystem=oms-delivery-service] - Restarting graph due to failure. stack_trace:
java.util.concurrent.CompletionException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement
at java.util.concurrent.CompletableFuture.encodeRelay(CompletableFuture.java:326)
at java.util.concurrent.CompletableFuture.completeRelay(CompletableFuture.java:338)
at java.util.concurrent.CompletableFuture.uniRelay(CompletableFuture.java:911)
at java.util.concurrent.CompletableFuture$UniRelay.tryFire(CompletableFuture.java:899)
at java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:474)
at java.util.concurrent.CompletableFuture.completeExceptionally(CompletableFuture.java:1977)
at scala.concurrent.java8.FuturesConvertersImpl$CF.apply(FutureConvertersImpl.scala:21)
at scala.concurrent.java8.FuturesConvertersImpl$CF.apply(FutureConvertersImpl.scala:18)
at scala.concurrent.impl.CallbackRunnable.run(Promise.scala:60)
at scala.concurrent.BatchingExecutor$Batch.processBatch$1(BatchingExecutor.scala:63)
at scala.concurrent.BatchingExecutor$Batch.$anonfun$run$1(BatchingExecutor.scala:78)
at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:12)
at scala.concurrent.BlockContext$.withBlockContext(BlockContext.scala:81)
at scala.concurrent.BatchingExecutor$Batch.run(BatchingExecutor.scala:55)
at scala.concurrent.Future$InternalCallbackExecutor$.unbatchedExecute(Future.scala:866)
at scala.concurrent.BatchingExecutor.execute(BatchingExecutor.scala:106)
at scala.concurrent.BatchingExecutor.execute$(BatchingExecutor.scala:103)
at scala.concurrent.Future$InternalCallbackExecutor$.execute(Future.scala:864)
at scala.concurrent.impl.CallbackRunnable.executeWithValue(Promise.scala:68)
at scala.concurrent.impl.Promise$DefaultPromise.$anonfun$tryComplete$1(Promise.scala:284)
at scala.concurrent.impl.Promise$DefaultPromise.$anonfun$tryComplete$1$adapted(Promise.scala:284)
at scala.concurrent.impl.Promise$DefaultPromise.tryComplete(Promise.scala:284)
at scala.concurrent.Promise.complete(Promise.scala:49)
at scala.concurrent.Promise.complete$(Promise.scala:48)
at scala.concurrent.impl.Promise$DefaultPromise.complete(Promise.scala:183)
at scala.concurrent.impl.Promise.$anonfun$transform$1(Promise.scala:29)
at scala.concurrent.impl.CallbackRunnable.run(Promise.scala:60)
at slick.util.AsyncExecutor$$anon$2$$anon$3$$anon$4.run(AsyncExecutor.scala:165)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1604)
at com.lightbend.lagom.internal.javadsl.persistence.jpa.JpaReadSideImpl$JpaReadSideHandler.updateOffset(JpaReadSideImpl.java:187)
at com.lightbend.lagom.internal.javadsl.persistence.jpa.JpaReadSideImpl$JpaReadSideHandler.lambda$null$3(JpaReadSideImpl.java:149)
at com.lightbend.lagom.internal.javadsl.persistence.jpa.JpaSessionImpl.lambda$withTransaction$0(JpaSessionImpl.java:64)
at com.lightbend.lagom.internal.javadsl.persistence.jpa.JpaSessionImpl.lambda$null$cb7af80$1(JpaSessionImpl.java:103)
at play.utils.Threads$.withContextClassLoader(Threads.scala:22)
at play.utils.Threads.withContextClassLoader(Threads.scala)
at com.lightbend.lagom.internal.javadsl.persistence.jpa.JpaSessionImpl.lambda$wrapWithContextClassLoader$100ad7b7$1(JpaSessionImpl.java:115)
at scala.concurrent.Future$.$anonfun$apply$1(Future.scala:655)
at scala.util.Success.$anonfun$map$1(Try.scala:251)
at scala.util.Success.map(Try.scala:209)
at scala.concurrent.Future.$anonfun$map$1(Future.scala:289)
at scala.concurrent.impl.Promise.liftedTree1$1(Promise.scala:29)
... 6 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
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:113)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
at org.hibernate.engine.query.spi.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:107)
at org.hibernate.internal.SessionImpl.executeNativeUpdate(SessionImpl.java:1593)
at org.hibernate.query.internal.NativeQueryImpl.doExecuteUpdate(NativeQueryImpl.java:292)
at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1594)
... 18 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY
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.executeForRows(T4CPreparedStatement.java:943)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:4875)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1361)
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:175)
... 22 more
It seems the reason of the error is the null value (I found it with debug) of the sequenceOffset variable here https://github.com/lagom/lagom/blob/454ecfcea53fdf63e203b412c1354b4d9a2144f6/persistence-jpa/javadsl/src/main/java/com/lightbend/lagom/internal/javadsl/persistence/jpa/JpaReadSideImpl.java#L228
Also I found this issue https://hibernate.atlassian.net/browse/HHH-10161
Is it legal for the sequenceOffset to have a null value? The corresponding field of the table is declared as nullable. Are there any workarounds for the Oracle DB ?