Akka Persistence: java.sql.SQLSyntaxErrorException: ORA-04043: object "SOME_SCHEMA" does not exist

Akka Persistence / Event Sourcing
1

Hello,

I am trying to use Akka Persistence JDBC (“com.lightbend.akka” %% “akka-persistence-jdbc” % “5.0.1”) with Oracle and have created the schema and tables as mentioned at oracle-create-schema.sql. In my akka-persistence-jdbc.conf I have added the overriddes as mentioned in oracle-schema-overrides.conf.

But when I run the Akka app I am getting error while trying to persist the events.

java.sql.SQLSyntaxErrorException: ORA-04043: object "SOME_SCHEMA" does not exist

I have tried the query that gets printed in the logs and it works fine in SQL Developer.

[DEBUG] 2021-07-23 14:57:17.684+0000 [sample-akka.actor.default-dispatcher-12] s.b.B.action - #1: StartTransaction
[DEBUG] 2021-07-23 14:57:17.685+0000 [slick.db-5] s.b.B.action - #2: MultiInsertAction [insert into "SOME_SCHEMA"."EVENT_JOURNAL" ("DELETED","PERSISTENCE_ID","SEQUENCE_NUMBER","WRITER","WRITE_TIMESTAMP","ADAPTER_MANIFEST","EVENT_PAYLOAD","EVENT_SER_ID","EVENT_SER_MANIFEST","META_PAYLOAD","META_SER_ID","META_SER_MANIFEST")  values (?,?,?,?,?,?,?,?,?,?,?,?)]
[DEBUG] 2021-07-23 14:57:17.685+0000 [slick.db-5] s.j.J.statement - Preparing insert statement (returning: ORDERING): insert into "SOME_SCHEMA"."EVENT_JOURNAL" ("DELETED","PERSISTENCE_ID","SEQUENCE_NUMBER","WRITER","WRITE_TIMESTAMP","ADAPTER_MANIFEST","EVENT_PAYLOAD","EVENT_SER_ID","EVENT_SER_MANIFEST","META_PAYLOAD","META_SER_ID","META_SER_MANIFEST")  values (?,?,?,?,?,?,?,?,?,?,?,?)
[DEBUG] 2021-07-23 14:57:17.687+0000 [sample-akka.persistence.dispatchers.default-plugin-dispatcher-134] s.b.B.action - #3: Rollback

Also, the read queries before this seems to be working fine.

My akka-persistence-jdbc.conf looks like:

akka {
    persistence {
        journal {
            plugin = "jdbc-journal"
            // Enable the line below to automatically start the journal when the actorsystem is started
            // auto-start-journals = ["jdbc-journal"]
        }
        snapshot-store {
            plugin = "jdbc-snapshot-store"
            // Enable the line below to automatically start the snapshot-store when the actorsystem is started
            // auto-start-snapshot-stores = ["jdbc-snapshot-store"]
        }
    }
}

jdbc-journal {
    tables {
        event_journal {
            tableName = "EVENT_JOURNAL"
            schemaName = ${ORACLE_SCHEMA}
            columnNames {
                ordering = "ORDERING"
                deleted = "DELETED"
                persistenceId = "PERSISTENCE_ID"
                sequenceNumber = "SEQUENCE_NUMBER"
                writer = "WRITER"
                writeTimestamp = "WRITE_TIMESTAMP"
                adapterManifest = "ADAPTER_MANIFEST"
                eventPayload = "EVENT_PAYLOAD"
                eventSerId = "EVENT_SER_ID"
                eventSerManifest = "EVENT_SER_MANIFEST"
                metaPayload = "META_PAYLOAD"
                metaSerId = "META_SER_ID"
                metaSerManifest = "META_SER_MANIFEST"
            }
        }
        event_tag {
            tableName = "EVENT_TAG"
            schemaName = ${ORACLE_SCHEMA}

            columnNames {
                eventId = "EVENT_ID"
                tag = "TAG"
            }
        }
    }

    slick = ${slick}
}

# the akka-persistence-snapshot-store in use
jdbc-snapshot-store {
    tables {
        snapshot {
            tableName = "SNAPSHOT"
            schemaName = ${ORACLE_SCHEMA}
            columnNames {
                persistenceId = "PERSISTENCE_ID"
                sequenceNumber = "SEQUENCE_NUMBER"
                created = "CREATED"

                snapshotPayload = "SNAPSHOT_PAYLOAD"
                snapshotSerId = "SNAPSHOT_SER_ID"
                snapshotSerManifest = "SNAPSHOT_SER_MANIFEST"

                metaPayload = "META_PAYLOAD"
                metaSerId = "META_SER_ID"
                metaSerManifest = "META_SER_MANIFEST"
            }
        }
    }
    slick = ${slick}
}

# the akka-persistence-query provider in use
jdbc-read-journal {
    tables {
        event_journal {
            tableName = "EVENT_JOURNAL"
            schemaName = ${ORACLE_SCHEMA}
        }
    }
    slick = ${slick}
}

slick {
    profile = "slick.jdbc.OracleProfile$"
    db {
        host = ${ORACLE_HOST}
        url = ${?ORACLE_URL}
        user = ${ORACLE_USER}
        password = ${ORACLE_PASSWORD}
        driver = "oracle.jdbc.OracleDriver"
        numThreads = 5
        maxConnections = 5
        minConnections = 1
    }
}

Looking for some help here. Thank you so much.

Regards
Karan Khanna

2

It worked fine when I changed the schemaName to “”, but I am not sure if this should be the expected behaviour.

3

No, that’s not the expected behavior.

But I guess there is something wrong with your configuration because the tests run with the settings in akka-persistence-jdbc/oracle-schema-overrides.conf at master · akka/akka-persistence-jdbc · GitHub and the schemaName should work.

Have you created the schema before hand? I’m not Oracle expert, but it seems that it will work out-of-the-box if the schema is “SYSTEM”, but not if something else. In which case, the schema must be created first.

4

Hey @octonato,

Thank you for getting back. Yes, I had created the schema and the tables before running my application. The read queries worked fine too. I only had the issue when the insert query was made.