Cassandra liquibase usage

[bjornph]

Hi, I tested this for use with Cassandra liquibase. It installs, but fails on a dummy project. See my post here

liquibase.exception.DatabaseException: Error executing SQL SELECT COUNT(*) FROM liquibase.DATABASECHANGELOGLOCK: Method was called on a closed Statement.

I did have the compliancemode turned on (the keyspace is also called liquibase: "jdbc:cassandra://<URL>/liquibase?localdatacenter=cassandra&compliancemode=Liquibase")

Is this used in production anywhere? I couldn't make sense of the databasechangeloglock error

[maximevw]

Hello @bjornph,

Do you only got this error message or do you have a full stack trace to provide? It could be helpful to better understand where it fails exactly (because it seems that Liquibase tries to perform an operation on a closed statement, what necessarily throws an exception in JDBC driver).

Also, could you specify the versions you used and if you made some specific configurations or changes on the Liquibase side (where did you override the Simba driver for example)?

I noticed liquibase-cassandra seems more or less nested with Simba JDBC driver and found this very recent merge request on this project but not merged: liquibase/liquibase-cassandra#205

Also, regarding the compliance mode, it has been introduced with the help of @mjok (see #8) to adapt some behaviours to be compatible with liquibase-cassandra requirements (as they are documented here: https://github.com/ing-bank/cassandra-jdbc-wrapper#compliance-modes) but I don't know if he performed some other changes in its configuration or in liquibase to make it work properly.

Finally, to answer your last question: yes, I know at least one project using it in production (but not with Liquibase). The JDBC wrapper as also been tested several times to connect to a Cassandra database with DBeaver Community Edition for example. Unfortunately, I don't know all the potential applications using it. 😉 This project is still in development but now implements a large part of JDBC API (at least for the specifications compatible with a NoSQL database such as Cassandra).

[maximevw]

Hello @bjornph,

I was able to reproduce the issue by rebuilding liquibase-cassandra with this JDBC wrapper.

I found the cause of the issue: closing the result set mistakenly was closing the statement. After fixing this (locally for now), I was able to successfully run the commands liquibase status and liquibase update-sql:

$> liquibase status --changelog-file=example-changelog.sql
[...]
1 changesets have not been applied to @jdbc:cassandra://localhost:9042/testkeyspace
     example-changelog.cql.sql::raw::includeAll
Logs saved to liquibase.log
Liquibase command 'status' was executed successfully.

$> liquibase update-sql --changelog-file=example-changelog.sql
[...]
-- Lock Database
UPDATE testkeyspace.DATABASECHANGELOGLOCK SET LOCKED = TRUE, LOCKEDBY = 'xxxx', LOCKGRANTED = 1693664071562 WHERE ID = 1 IF LOCKED = FALSE;

-- Create Database Change Log Table
CREATE TABLE IF NOT EXISTS testkeyspace.DATABASECHANGELOG( ID TEXT, AUTHOR TEXT, FILENAME TEXT, DATEEXECUTED timestamp, ORDEREXECUTED INT, EXECTYPE TEXT, MD5SUM TEXT, DESCRIPTION TEXT, COMMENTS TEXT, TAG TEXT, LIQUIBASE TEXT, CONTEXTS TEXT, LABELS TEXT, DEPLOYMENT_ID TEXT,PRIMARY KEY (ID, AUTHOR, FILENAME));

-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: example-changelog.sql
-- Ran at: 02/09/2023 16:14
-- Against: @jdbc:cassandra://localhost:9042/testkeyspace
-- Liquibase version: 4.23.1
-- *********************************************************************

-- Changeset example-changelog.cql.sql::raw::includeAll
CREATE KEYSPACE betterbotz
  WITH REPLICATION = { 
   'class' : 'SimpleStrategy', 
   'replication_factor' : 1 
  };
USE betterbotz;
[...]

INSERT INTO testkeyspace.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('raw', 'includeAll', 'example-changelog.sql', 1693664071816, 1, '9:5ebad445bb1f43735b61600c376827d6', 'sql', '', 'EXECUTED', NULL, NULL, '4.23.1', NULL);

-- Release Database Lock
UPDATE testkeyspace.DATABASECHANGELOGLOCK SET LOCKED = FALSE, LOCKEDBY = NULL WHERE ID = 1;

Logs saved to liquibase.log
Liquibase command 'update-sql' was executed successfully.

However, I'm still facing an issue when running the command liquibase update:

$> liquibase update-sql --changelog-file=example-changelog.sql
[...]
Unexpected error running Liquibase: class liquibase.exception.UnexpectedLiquibaseException cannot be cast to class liquibase.exception.LiquibaseException (liquibase.exception.UnexpectedLiquibaseException and liquibase.exception.LiquibaseException are in unnamed module of loader java.net.URLClassLoader @7a81197d)

Logs saved to liquibase.log

I identified the root cause of this new issue as an SQL exception "Method was called on a closed ResultSet." when the method setFetchSize(int) is called in the method liquibase.snapshot.ResultSetCache.extract(ResultSet, boolean).

I continue to investigate this.

[maximevw]

Hello @bjornph,

I finally fixed all the issues preventing to use this wrapper with Liquibase and I published a new version (4.9.1).

I was able to successfully execute the basic example provided in the liquibase-cassandra repository:

$> liquibase update --changelog-file=example-changelog.sql

[...]
Running Changeset: example-changelog.sql::raw::includeAll

UPDATE SUMMARY
Run:                          1
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            1

Liquibase: Update has been successful. Rows affected: 0
Logs saved to liquibase.log
Liquibase command 'update' was executed successfully.

And if I query my Cassandra database, the script is correctly executed:

cqlsh:testkeyspace> use betterbotz;
cqlsh:betterbotz> desc tables;

authors  posts

cqlsh:betterbotz> select * from authors;

 id | added                           | birthdate  | email                        | first_name | last_name
----+---------------------------------+------------+------------------------------+------------+-----------
  5 | 1990-02-04 02:32:00.000000+0000 | 2010-02-27 |        lillian66@example.com |     Jayden |    Walter
  1 | 2004-05-30 02:08:25.000000+0000 | 1991-03-04 |          ppaucek@example.org |     Eileen |  Lubowitz
  2 | 2014-03-21 02:52:00.000000+0000 | 2016-03-27 |          shansen@example.org |      Tamia |    Mayert
  4 | 2019-04-22 02:04:41.000000+0000 | 2017-02-03 |          xhoeger@example.net |    Nicolas | Buckridge
  3 | 2011-06-24 18:17:48.000000+0000 | 1988-04-21 | reynolds.godfrey@example.com |      Cyril |      Funk

(5 rows)
cqlsh:betterbotz> select * from posts;

 id | author_id | content                                                            | description | inserted_date | title
----+-----------+--------------------------------------------------------------------+-------------+---------------+------------
  5 |         5 |                  Rerum tempore quis ut nesciunt qui excepturi est. |   similique |    2006-10-09 |         ad
  1 |         1 |                                Fugit non et doloribus repudiandae. |  voluptatum |    2015-11-18 | temporibus
  2 |         2 | Tempora molestias maiores provident molestiae sint possimus quasi. |         aut |    1975-06-08 |         ea
  4 |         4 |                                     Magni nam optio id recusandae. |    deleniti |    2010-07-28 |     itaque
  3 |         3 |                            Delectus recusandae sit officiis dolor. |       rerum |    1975-02-25 |      illum

(5 rows)

Please note that this only works if the following prerequisites are met:

• use Cassandra JDBC wrapper 4.9.1
• the liquibase-cassandra jar must be modified to use this wrapper instead of Simba JDBC driver: see the changes in this pull request
• the compliance mode Liquibase must be set in the JDBC URL
• I also recommend to increase the value of the parameter requesttimeout in the JDBC URL (default value is 2 seconds),
  especially if you use a CQL script as Liquibase changelog: indeed, the script is considered as a single statement and if
  it contains a lot of queries, it could take more than 2 seconds in some circumstances.

[bjornph]

@maximevw Wow! I was about to post the setup to recreate it, but you beat me to it, and solved the root problem as well!

Thank you very much. I think this will be a good alternative to the simba driver due to the uncertainties wrt. licensing.