"Mismatched input ROLLBACK" instead of original SQL error

[amotl]

Problem

Hi there. A canonical error people using CrateDB through client libraries may observe is this one.

Command result: 1
XX000: line 1:1: mismatched input 'ROLLBACK' expecting {'SELECT', 'DEALLOCATE', 'FETCH', 'END', 'WITH', 'CREATE', 'ALTER', 'KILL', 'CLOSE', 'BEGIN', 'START', 'COMMIT', 'ANALYZE', 'DISCARD', 'EXPLAIN', 'SHOW', 'OPTIMIZE', 'REFRESH', 'RESTORE', 'DROP', 'INSERT', 'VALUES', 'DELETE', 'UPDATE', 'SET', 'RESET', 'COPY', 'GRANT', 'DENY', 'REVOKE', 'DECLARE'}

The general feeling on this issue is that it’s some form of automatic rollback behaviour of the Npgsql driver ¹, but we can’t track down which setting this might be. It does seem that the original error is just being swallowed though, so it’s sometimes very difficult to diagnose what’s wrong with the query as a result.

Confirmation

I’ve verified that this happens with Spring Data in Java as well.

Footnotes

1. Could be any driver, see confirmation section. ↩

[amotl]

Discussion

Over here, we figured using and recommending the transaction isolation level READ UNCOMMITTED would be the right choice for CrateDB. However, SQLAlchemy has a special isolation level called AUTOCOMMIT, which we think is even more appropriate in this case.

• Dialect: Add methods concerned with isolation levels as no-ops sqlalchemy-cratedb#217

Resources

• https://www.postgresql.org/docs/current/transaction-iso.html
• https://www.psycopg.org/psycopg3/docs/api/objects.html#psycopg.IsolationLevel

Thoughts

It would be so sweet if we could figure out the right standard settings for the relevant driver regimes to optimally work with CrateDB on the details where it is a different kind of animal. All drivers and frameworks are of concerns here, and relevant settings should be documented well, optimally in cratedb-guide.

We can possibly start a little initiative about it, to provide information we actively validated beforehand? I think with Apache Spark / databricks, it has been validated already that using isolationLevel=NONE is the right choice?

[amotl]

We discovered with Npgsql, it is most likely the SupportsTransactions = false setting, which could be beneficial for CrateDB.

Maybe CrateDB folks never followed that suggestion by Shay Rojansky, the main author of Npgsql ¹:

Also, rather than checking IsCrateDB, please create a SupportsTransactions feature on the connector and check that.

/cc @karynzv, @zolbatar

Footnotes

1. Please note the comment is from 2017 already, on a patch that has not been merged at that time. ↩