You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@pekko.apache.org by "Roiocam (via GitHub)" <gi...@apache.org> on 2024/04/09 01:37:35 UTC
[I] MySQL support for DurableState [pekko-persistence-jdbc]
Roiocam opened a new issue, #174:
URL: https://github.com/apache/pekko-persistence-jdbc/issues/174
## Motivation
supplement #158, continue solves #56
In #158, we notice that other databases support sequence but MySQL does not, but MySQL does have [auto-increment coumn](https://dev.mysql.com/doc/refman/8.3/en/example-auto-increment.html).
I used `SELECT AUTO_INCREMENT FROM information_schema.TABLES` for retrieving recent automatically generated AUTO_INCREMENT value, but the bigger problem is the sequence number only assigned from MySQL, which can not increase them via `SequenceNextValUpdater`.
--
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: notifications-unsubscribe@pekko.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] MySQL support for DurableState [pekko-persistence-jdbc]
Posted by "pjfanning (via GitHub)" <gi...@apache.org>.
pjfanning commented on issue #174:
URL: https://github.com/apache/pekko-persistence-jdbc/issues/174#issuecomment-3523707324
@marcopaggioro Yes the issue highlighted in the issue description is the SequenceNextValUpdater that you mentioned.
--
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: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] MySQL support for DurableState [pekko-persistence-jdbc]
Posted by "marcopaggioro (via GitHub)" <gi...@apache.org>.
marcopaggioro commented on issue #174:
URL: https://github.com/apache/pekko-persistence-jdbc/issues/174#issuecomment-3522601742
It would be great if it could work with MySql/MariaDb
--
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: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] MySQL support for DurableState [pekko-persistence-jdbc]
Posted by "marcopaggioro (via GitHub)" <gi...@apache.org>.
marcopaggioro commented on issue #174:
URL: https://github.com/apache/pekko-persistence-jdbc/issues/174#issuecomment-3523732880
@pjfanning Of course, once I determined that the problem was `SequenceNextValUpdater`, I did some analysis to understand the issues in detail. Digging around, I started wondering why the `global_offset` is manually extracted and then used in the `UPDATE` and `INSERT` queries.
So I came to the following question:
- Isn't it possible to rely on the DBMS' auto-increment feature and eliminate that query?
- If it's really necessary, could a MySQL fix be the `REPLACE` query and not the `UPDATE/INSERT` query?
I was wondering what you thought.
--
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: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] MySQL support for DurableState [pekko-persistence-jdbc]
Posted by "pjfanning (via GitHub)" <gi...@apache.org>.
pjfanning commented on issue #174:
URL: https://github.com/apache/pekko-persistence-jdbc/issues/174#issuecomment-3522682819
@marcopaggioro any interest in taking this on yourself? This is a volunteer project with limited volunteer bandwidth.
--
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: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] MySQL support for DurableState [pekko-persistence-jdbc]
Posted by "marcopaggioro (via GitHub)" <gi...@apache.org>.
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: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] MySQL support for DurableState [pekko-persistence-jdbc]
Posted by "pjfanning (via GitHub)" <gi...@apache.org>.
pjfanning closed issue #174: MySQL support for DurableState
URL: https://github.com/apache/pekko-persistence-jdbc/issues/174
--
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: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] MySQL support for DurableState [pekko-persistence-jdbc]
Posted by "pjfanning (via GitHub)" <gi...@apache.org>.
pjfanning commented on issue #174:
URL: https://github.com/apache/pekko-persistence-jdbc/issues/174#issuecomment-3523789082
@marcopaggioro I'm not an expert on this code but if it was possible to achieve what we need by bypassing the SequenceNextValUpdater - so that the MySQL code path used a completely different approach - that would be ok. The key thing is that we would have test coverage.
--
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: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org