search

akka / akka-persistence-jdbc

Asynchronously writes journal and snapshot entries to configured JDBC databases so that Akka Actors can recover state
https://doc.akka.io/docs/akka-persistence-jdbc/
Other
308 stars 142 forks source link

oracle-create-schema.sql and default reference.conf files mismatch #523

Open rrevi opened 3 years ago

rrevi commented 3 years ago

Versions used

Akka version: 2.6.13 akka-persistence-jdbc version: 5.0.0

Expected Behavior

  1. Run the oracle-create-schema.sql script on a Oracle DB instance
  2. Run a Akka app with persistence without overwriting the schema table and column name values from reference.conf in your application.conf
  3. Success 🥳

Actual Behavior

  1. Run the oracle-create-schema.sql script on a Oracle DB instance
  2. Run a Akka app with persistence without overwriting the schema table and column name values from reference.conf in your application.conf
  3. Failure 😭 with log statements like:

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

Relevant logs

N/A

Reproducible Test Case

N/A

For more history on this bug, INCLUDING POSSIBLE FIX see this forum post https://discuss.lightbend.com/t/akka-persistence-ora-00942-table-or-view-does-not-exist/8085

octonato commented 3 years ago

Hi @rrevi,

Thanks for reporting this. You are correct on your comment in the discuss forum.

oracle-schema-overrides.conf is fixing this, but that only happens in our tests. Which also explains why we didn't see any issue.

I will check if we can use lowercase in the default create script. I think that's the best option. However I have a vague memory that @chbatey run into a issue with cases in Oracle and was forced to move it to uppercase.

chbatey commented 3 years ago

From memory the Oracle JDBC driver doesn't support a prepared statement that returns a value for a table with a case sensitive name and it manifests its self as a table not found error

On Mon, Apr 12, 2021 at 11:11 AM Renato Cavalcanti @.***> wrote:

Hi @rrevi https://github.com/rrevi,

Thanks for reporting this. You are correct on your comment in the discuss forum https://discuss.lightbend.com/t/akka-persistence-ora-00942-table-or-view-does-not-exist/8085/6?u=octonato .

oracle-schema-overrides.conf is fixing this, but that only happens in our tests. Which also explains why we didn't see any issue.

I will check if we can use lowercase in the default create script. I think that's the best option. However I have a vague memory that @chbatey https://github.com/chbatey run into a issue with cases in Oracle and was forced to move it to uppercase.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/akka/akka-persistence-jdbc/issues/523#issuecomment-817681236, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAOHYG7M6FJZH7YS2HQEGWDTILBO5ANCNFSM42RCNBUQ .

krnkhanna commented 3 years ago

Hi @octonato, @chbatey,

I am trying the same schema but with all the columns and table names in lower case. It works fine while reading from the tables but I am getting object "EVENT_JOURNAL" does not exist when it tries to insert.

Logs for the reference:

[DEBUG] 2021-07-23 11:11:50.962+0000 [slick.db-3] s.j.J.statement - Preparing insert statement (returning: ordering): insert into "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 11:11:50.973+0000 [cloud-poi-ha-akka.persistence.dispatchers.default-plugin-dispatcher-53] s.b.B.action - #3: Rollback [ERROR] 2021-07-23 11:11:51.066+0000 [cloud-poi-ha-akka.actor.default-dispatcher-25] i.f.c.s.SaleSystem - Failed to persist event type [*Event] with sequence number [1] for persistenceId [*some-persistence-id*]. java.sql.SQLSyntaxErrorException: ORA-04043: object "EVENT_JOURNAL" does not exist

Any help how to go about this?

I am using the following for my schema:

`CREATE SEQUENCE EVENT_JOURNAL_ORDERING_SEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE /

CREATE TABLE "event_journal" ( "ordering" NUMERIC UNIQUE, "deleted" CHAR(1) DEFAULT 0 NOT NULL check ("deleted" in (0, 1)), "persistence_id" VARCHAR(255) NOT NULL, "sequence_number" NUMERIC NOT NULL, "writer" VARCHAR(255) NOT NULL, "write_timestamp" NUMBER(19) NOT NULL, "adapter_manifest" VARCHAR(255), "event_payload" BLOB NOT NULL, "event_ser_id" NUMBER(10) NOT NULL, "event_ser_manifest" VARCHAR(255), "meta_payload" BLOB, "meta_ser_id" NUMBER(10), "meta_ser_manifest" VARCHAR(255), PRIMARY KEY("persistence_id", "sequence_number") ) /

CREATE OR REPLACE TRIGGER EVENT_JOURNAL_ORDERING_TRG before insert on "event_journal" REFERENCING NEW AS NEW FOR EACH ROW WHEN (new."ordering" is null) begin select EVENT_JOURNAL_ORDERING_seq.nextval into :new."ordering" from sys.dual; end; /

CREATE TABLE "event_tag" ( "event_id" NUMERIC NOT NULL, "tag" VARCHAR(255) NOT NULL, PRIMARY KEY("event_id", "tag"), FOREIGN KEY("event_id") REFERENCES "event_journal"("ordering") ON DELETE CASCADE ) /

CREATE TABLE "snapshot" ( "persistence_id" VARCHAR(255) NOT NULL, "sequence_number" NUMERIC NOT NULL, "created" NUMERIC NOT NULL, "snapshot_ser_id" NUMBER(10) NOT NULL, "snapshot_ser_manifest" VARCHAR(255), "snapshot_payload" BLOB NOT NULL, "meta_ser_id" NUMBER(10), "meta_ser_manifest" VARCHAR(255), "meta_payload" BLOB, PRIMARY KEY("persistence_id","sequence_number") ) /

CREATE OR REPLACE PROCEDURE "reset_sequence" IS l_value NUMBER; BEGIN EXECUTE IMMEDIATE 'SELECT EVENT_JOURNAL_ORDERING_SEQ.nextval FROM dual' INTO l_value; EXECUTE IMMEDIATE 'ALTER SEQUENCE EVENT_JOURNAL_ORDERING_SEQ INCREMENT BY -' || l_value || ' MINVALUE 0'; EXECUTE IMMEDIATE 'SELECT EVENT_JOURNAL_ORDERING_SEQ.nextval FROM dual' INTO l_value; EXECUTE IMMEDIATE 'ALTER SEQUENCE EVENT_JOURNAL_ORDERING_SEQ INCREMENT BY 1 MINVALUE 0'; END; /`