marcopaggioro commented on issue #174: URL: https://github.com/apache/pekko-persistence-jdbc/issues/174#issuecomment-3523685364
I was just starting to implement it, but I ran into issues I couldn't handle, and I imagine that's why Akka didn't implement it. The first issues resolved: - `VARCHAR tag` in the Postgres schema of the `durable_state` table. `VARCHAR` without a fixed length is not available in MySQL, but it could become `VARCHAR(255)`, as specified in the Oracle and SQLServer schema. - `state_payload BYTEA` in the Postgres schema of the `durable_state` table. `BYTEA` is not available in MySQL, but it could become LONGBLOB, as specified in the Oracle and SQLServer schema. - `global_offset BIGSERIAL` in the Postgres schema of the `durable_state` table. `BIGSERIAL` (used as` BIGINT` with auto-increment) is not available in MySQL, but it could become `BIGINT AUTO_INCREMENT` and add a `UNIQUE KEY` to `global_offset`. This could resolve the schema issues. Moving on to the code, I came across the `SequenceNextValUpdater` classes that require the `getSequenceNextValueExpr` method to extract the next sequence number. There's no way to get this information in MySQL. The `LAST_INSERT_ID()` function is only valid within the same session and after an `INSERT`. Reading the value from `information_schema.TABLES` isn't safe. That said, I'm not sure what the best approach is. Currently, updating the durable state requires manually inserting the `global_offset` obtained from `getSequenceNextValueExpr`. One solution could be to use `REPLACE` instead of `UPDATE` (and `INSERT` too?), which under the hood does `DELETE` (if necessary) + INSERT, and the `INSERT` would trigger the auto-increment. Perhaps I'm missing the point of obtaining the `global_offset` programmatically and not using DBMS increments. Perhaps for legacy versions? Another alternative is to develop a MariaDB connector that supports sequences and does not have the getSequenceNextValueExpr issue. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
