Akka Persistence - ORA-00942: table or view does not exist

Hello,

I am new to everything Akka. I’ve setup a POC project/app with Akka Persistence with an Oracle DB instance.

When I run the Akka app, I get the following error:

akka.persistence.typed.internal.JournalFailureException: Exception during recovery from snapshot. PersistenceId [SomeActor|some-actor-01]. ORA-00942: table or view does not exist

Can someone help understand how to have Akka P auto generate the tables? or point me to some documentation :pray:t4:

Thanks in advance,
Rafael

Use SchemaUtils
https://doc.akka.io/docs/akka-persistence-jdbc/current/configuration.html#database-schema

Note that it is not recommended to auto-generate tables for production environments.

1 Like

Thanks @johanandren !

Once I ran the oracle-create-schemal.sql script, I kept getting the same error. Weird. So I decided to turn on DEBUG level log in logback.xml which made me realize this is the prepared statement which is causing the error:

[2021-04-07 09:49:16,897] [DEBUG] [slick.jdbc.JdbcBackend.statement] [slick.db-1] [] - Preparing statement: select s94.s68, s94.s64, s94.s67, s94.s65, s94.s69, s94.s66, s94.s62, s94.s63, s94.s70 from (select “meta_ser_id” as s62, “meta_ser_manifest” as s63, “sequence_number” as s64, “snapshot_ser_id” as s65, “snapshot_payload” as s66, “created” as s67, “persistence_id” as s68, “snapshot_ser_manifest” as s69, “meta_payload” as s70 from “snapshot” where “persistence_id” = ? order by “sequence_number” desc) s94 where rownum <= 1

If you compare the oracle-create-schema.sql file to the prepared statement above, it seems the create schema script is using SNAPSHOT as the name of the table/view where as the Akka app is referencing snapshot. I am wondering if the difference in case has something to do with this error?

Perhaps the “incorrect table names” mentioned by @ning-nan here have something to do with this error/issue I am having (in which case it should obviously get fixed; either in the oracle-create-schema.sql script or in the reference.conf file)

Not sure if name case has anything to do with it, can’t say I have any Oracle specific knowledge here, but, there was at least one recent oracle related fix recently (5.0.1) Oracle schema bug: wrong attribute name · Issue #502 · akka/akka-persistence-jdbc · GitHub so I’m guessing once that was fixed it worked at least for that PR author (it’s possible he didn’t use snapshots though).

Thanks again @johanandren !

Yeah, I just confirmed that the table names and columns are case sensitive in Oracle DB; I overwrote the jdbc-snashot-store.tables.snaphot.tableName field in my application.conf file and it got me passed the issue; perhaps the reference to

include "oracle-schema-overrides.conf"

in Configuration • Akka Persistence JDBC does that exactly. Now if that file would be available somewhere for future developers to avoid running into this issue.

Please open up an issue in the akka-persistence-jdbc issue tracker about this, thanks!

1 Like