MariaDB connector cannot update or delete

[prrvchr]

If I use Trino with PR #27324 and #27321, I can successfully build a CachedRowSet from a ResultSet produced by a query from the HsqlDB connector and this CachedRowSet can write (insert, update or delete) into the underlying table.

However, if I use the MariaDB connector, I can perform inserts, but I get the following error message for updates and deletes:

Error code: 13

Query failed (#20251122_091825_00021_jej77): This connector does not support modifying table rows

Does anyone have any idea what's going on?

[ebyhr]

At the moment, MySQL, PostgreSQL, and Ignite are the only JDBC-based connectors that support row-level updates.

[prrvchr]

At the moment, MySQL, PostgreSQL, and Ignite are the only JDBC-based connectors that support row-level updates.

I was pretty sure the message came from Trino. What's missing from the MariDB connector to enable writing?

I'm going to propose a connector for Firebird that I hope will be able to write...

[ebyhr]

We need to implement supportsMerge and getPrimaryKeys methods in MariaDbClient, and add some tests. See #24428
Preview for more details.

[prrvchr]

If I use the MySQL connector I can perform inserts, but I get the following error message for updates and deletes:

Error code: 13

Query failed (#20251123_075846_00023_7jivt): This connector does not support MERGE with transactional execution

[prrvchr]

If I use the PostgreSQL connector I can perform inserts, updates and deletes, but if I try to insert a row with an auto-incrementing column, I get the following error message:

Error code: 67108864

Query failed (#20251123_082948_00102_f7m7p): ERROR: cannot insert a non-DEFAULT value into column "ID"
  Detail: Column "ID" is an identity column defined as GENERATED ALWAYS.
  Hint: Use OVERRIDING SYSTEM VALUE to override.

But this stems from the fact that my CachedRowSet doesn't recognize auto increment columns, and therefore LibreOffice Base doesn't prevent them from being edited, at least not yet.

[prrvchr]

I'm going to propose a connector for Firebird that I hope will be able to write...

Well, it turns out that Firebird doesn't support schemas, so I can't get anything to work, at least not with what I've tried.

Perhaps I could try with H2 or Derby. Which of the two is preferable?