Error "countRows is not yet supported." on AWS Keyspaces

[pasarbia]

Hello, We have the following error when integrating the extension to AWS Keyspaces. The AWS support responded that Keyspaces does not have a plan to support "count". Is there a way to manage this error on the Liquibase side?

[2024-04-08 10:56:16] FINE [liquibase.executor] Create Database Lock Table
[2024-04-08 10:56:16] FINE [liquibase.executor] CREATE TABLE IF NOT EXISTS fsp_core.DATABASECHANGELOGLOCK (ID INT, LOCKED BOOLEAN, LOCKGRANTED timestamp, LOCKEDBY TEXT, PRIMARY KEY (ID))
[2024-04-08 10:56:16] FINE [liquibase.executor] -1 row(s) affected
[2024-04-08 10:56:16] FINE [liquibase.executor] Changelog query completed.
[2024-04-08 10:56:16] FINE [liquibase.ext] Created database lock table with name: fsp_core.DATABASECHANGELOGLOCK
[2024-04-08 10:56:16] FINE [liquibase.ext] Failed to create or initialize the lock table, trying again, iteration 1 of 10
liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: Error executing SQL SELECT COUNT(*) FROM fsp_core.DATABASECHANGELOGLOCK: com.datastax.oss.driver.api.core.servererrors.InvalidQueryException: countRows is not yet supported.

[maximevw]

I submitted a proposal for a fix. It's not optimal because it requires to execute a SELECT * query in replacement of the SELECT COUNT(*) one, then counting the rows in the returned list. But I think this should solve the issue.

@filipelautert @kevin-atx Do not hesitate to have a look to the fix proposal #292.

[pasarbia]

Hello @maximevw ,
Thank you for your help!
I have tested your solution and I get the same error:
[2024-05-14 11:30:19] FINE [liquibase.configuration] No configuration value for liquibase.sql.showSqlWarnings found [2024-05-14 11:30:19] FINE [liquibase.configuration] Configuration liquibase.sql.showSqlWarnings is using the default value of true [2024-05-14 11:30:19] FINE [liquibase.executor] Create Database Lock Table [2024-05-14 11:30:19] FINE [liquibase.executor] CREATE TABLE IF NOT EXISTS fsp_core.DATABASECHANGELOGLOCK (ID INT, LOCKED BOOLEAN, LOCKGRANTED timestamp, LOCKEDBY TEXT, PRIMARY KEY (ID)) [2024-05-14 11:30:19] FINE [liquibase.executor] -1 row(s) affected [2024-05-14 11:30:19] FINE [liquibase.executor] Changelog query completed. [2024-05-14 11:30:19] FINE [liquibase.ext] Created database lock table with name: fsp_core.DATABASECHANGELOGLOCK [2024-05-14 11:30:19] FINE [liquibase.ext] Failed to create or initialize the lock table, trying again, iteration 1 of 10 liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: Error executing SQL SELECT COUNT(*) FROM fsp_core.DATABASECHANGELOGLOCK: com.datastax.oss.driver.api.core.servererrors.InvalidQueryException: countRows is not yet supported. at liquibase.lockservice.StandardLockService.isDatabaseChangeLogLockTableInitialized(StandardLockService.java:219) at liquibase.lockservice.StandardLockService.init(StandardLockService.java:135) at liquibase.command.core.helpers.DatabaseChangelogCommandStep.checkLiquibaseTables(DatabaseChangelogCommandStep.java:145) at liquibase.command.core.helpers.DatabaseChangelogCommandStep.run(DatabaseChangelogCommandStep.java:91) at liquibase.command.CommandScope.execute(CommandScope.java:219) at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:55) at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:24) at picocli.CommandLine.executeUserObject(CommandLine.java:2041) at picocli.CommandLine.access$1500(CommandLine.java:148) at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2461) at picocli.CommandLine$RunLast.handle(CommandLine.java:2453) at picocli.CommandLine$RunLast.handle(CommandLine.java:2415) at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2273) at picocli.CommandLine$RunLast.execute(CommandLine.java:2417)

[maximevw]

@pasarbia Based on the provided stacktrace, it seems it uses the standard implementation of Liquibase instead of the custom lock service for Cassandra.

at liquibase.lockservice.StandardLockService.isDatabaseChangeLogLockTableInitialized(StandardLockService.java:219) 
at liquibase.lockservice.StandardLockService.init(StandardLockService.java:135)

But I expected here a call to: liquibase.ext.cassandra.lockservice.LockServiceCassandra.isDatabaseChangeLogLockTableInitialized()
Are you sure liquibase-cassandra is correctly loaded in your classpath?

[pasarbia]

@maximevw I appreciate your help and time!
I have the following:
`root@ip-10-123-13-212:/home/ubuntu/liquibase# ll
total 476
drwxr-xr-x 7 root root 4096 May 15 09:35 ./
drwxr-xr-x 4 ubuntu ubuntu 4096 May 7 13:15 ../
-rw-r--r-- 1 ssm-user docker 50 Mar 25 17:52 ABOUT.txt
-rw-r--r-- 1 root root 56431 May 7 20:01 CassandraJDBC_connection_0.log
-rw-r--r-- 1 root root 11020 May 7 20:01 CassandraJDBC_driver.log
-rw-r--r-- 1 ssm-user docker 10388 Mar 25 17:52 GETTING_STARTED.txt
-rw-r--r-- 1 ssm-user docker 11345 Mar 25 17:52 LICENSE.txt
-rw-r--r-- 1 ssm-user docker 2721 Mar 25 17:52 README.txt
-rw-r--r-- 1 ssm-user docker 766 Mar 25 17:52 UNINSTALL.txt
-rw-r--r-- 1 root root 1469 May 7 19:44 cassandra.crt
-rw-r--r-- 1 root root 1124 May 7 19:46 cassandra_truststore.jks
-rw-r--r-- 1 ssm-user docker 327405 Mar 25 17:52 changelog.txt
-rw-r--r-- 1 ssm-user docker 832 Mar 25 17:23 example-changelog.sql
drwxr-xr-x 6 ssm-user docker 4096 Mar 25 17:52 examples/
drwxr-xr-x 3 ssm-user docker 4096 Mar 25 17:52 internal/
drwxr-xr-x 3 root root 4096 May 7 19:35 java/
drwxr-xr-x 2 ssm-user docker 4096 May 15 09:35 lib/
drwxr-xr-x 4 ssm-user docker 4096 Mar 25 17:52 licenses/
-rwxr-xr-x 1 ssm-user docker 1509 Mar 25 17:23 liquibase*
-rw-r--r-- 1 ssm-user docker 1134 Mar 25 17:23 liquibase.bat
-rw-r--r-- 1 root root 370 May 10 11:49 liquibase.properties
root@ip-10-123-13-212:/home/ubuntu/liquibase#
root@ip-10-123-13-212:/home/ubuntu/liquibase#
root@ip-10-123-13-212:/home/ubuntu/liquibase# ll lib/
total 22004
drwxr-xr-x 2 ssm-user docker 4096 May 15 09:35 ./
drwxr-xr-x 7 root root 4096 May 15 09:35 ../
-rw-r--r-- 1 ssm-user docker 429 Mar 25 17:52 README.txt
-rw-r--r-- 1 root root 688 May 10 11:22 application.conf
-rw-r--r-- 1 root root 22461260 May 10 11:17 cassandra-jdbc-wrapper-4.12.0-bundle.jar
-rw-r--r-- 1 root root 40404 May 10 11:07 liquibase-cassandra-4.28.0-SNAPSHOT.jar
-rw-r--r-- 1 ssm-user docker 3690 Mar 25 17:23 liquibase_autocomplete.sh
-rw-r--r-- 1 ssm-user docker 2387 Mar 25 17:23 liquibase_autocomplete.zsh
-rwxr-xr-x 1 ssm-user docker 2846 Mar 25 17:23 liquibase_autocomplete_mac.bash*
root@ip-10-123-13-212:/home/ubuntu/liquibase#
root@ip-10-123-13-212:/home/ubuntu/liquibase#
root@ip-10-123-13-212:/home/ubuntu/liquibase# cat lib/application.conf
datastax-java-driver {

basic.request {
consistency = LOCAL_QUORUM
}

basic.load-balancing-policy {
local-datacenter = eu-west-1
slow-replica-avoidance = false
}
advanced.auth-provider {
class = PlainTextAuthProvider
username = username
password = "password"
}
advanced.ssl-engine-factory {
class = DefaultSslEngineFactory
hostname-validation = false
truststore-path = /home/ubuntu/liquibase/cassandra_truststore.jks
truststore-password = password
}

}
root@ip-10-123-13-212:/home/ubuntu/liquibase# cat liquibase.properties
liquibase.command.url=jdbc:cassandra://cassandra.eu-west-1.amazonaws.com:9142/keyspace?compliancemode=Liquibase&&configfile=/home/ubuntu/liquibase/lib/application.conf
liquibase.command.username: username
liquibase.command.password: password
driver: com.ing.data.cassandra.jdbc.CassandraDriver`

I tried to move liquibase-cassandra-4.28.0-SNAPSHOT.jar from /lib directory to test without the extension and I got:

2024-05-15 09:26:45] SEVERE [liquibase.integration] line 1:42 no viable alternative at input '(' (CREATE TABLE DATABASECHANGELOG (ID [VARCHAR](...) [Failed SQL: (0) CREATE TABLE DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED datetime NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))] liquibase.exception.CommandExecutionException: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: com.datastax.oss.driver.api.core.servererrors.SyntaxError: line 1:42 no viable alternative at input '(' (CREATE TABLE DATABASECHANGELOG (ID [VARCHAR](...) [Failed SQL: (0) CREATE TABLE DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED datetime NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))] at liquibase.command.CommandScope.execute(CommandScope.java:257) at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:55) at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:24) at picocli.CommandLine.executeUserObject(CommandLine.java:2041) at picocli.CommandLine.access$1500(CommandLine.java:148) at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2461)

[pasarbia]

is it possible that the countRows query comes from the driver?

`Unexpected error running Liquibase: Error executing SQL SELECT COUNT(*) FROM fsp_core.DATABASECHANGELOGLOCK: com.datastax.oss.driver.api.core.servererrors.InvalidQueryException: countRows is not yet supported.

liquibase.exception.CommandExecutionException: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: Error executing SQL SELECT COUNT(*)FROM fsp_core.DATABASECHANGELOGLOCK: com.datastax.oss.driver.api.core.servererrors.InvalidQueryException: countRows is not yet supported.
at liquibase.command.CommandScope.execute(CommandScope.java:257)
at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:55)
at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:24)
at picocli.CommandLine.executeUserObject(CommandLine.java:2041)
at picocli.CommandLine.access$1500(CommandLine.java:148)`

[maximevw]

@pasarbia

I don't think it's related, but the JDBC URL here contains an error:
liquibase.command.url=jdbc:cassandra://cassandra.eu-west-1.amazonaws.com:9142/keyspace?compliancemode=Liquibase&&configfile=/home/ubuntu/liquibase/lib/application.conf
There are two & between Liquibase and configfile instead of only one. But I guess, this has no consequence.

I tried to move liquibase-cassandra-4.28.0-SNAPSHOT.jar from /lib directory

The result you obtained in this case seems consistent. It's correct to have this jar in the lib directory.

is it possible that the countRows query comes from the driver?

This query (SELECT COUNT(*) FROM fsp_core.DATABASECHANGELOGLOCK) comes from Liquibase, here in cassandra extension:

liquibase-cassandra/src/main/java/liquibase/ext/cassandra/lockservice/LockServiceCassandra.java

Line 156 in 07e6265

"SELECT COUNT(*) FROM " + getChangeLogLockTableName()) > 0;

The CQL query is sent as-is to the Cassandra (AWS keyspaces in your case) server by the driver.
The InvalidQueryException returned by the driver indicates a syntactically correct but invalid query, so it clearly comes from AWS keyspaces which does not support COUNT function.
Consequently, it seems unlikely that the issue comes from the driver.

I guess you built liquibase-cassandra-4.28.0-SNAPSHOT.jar by yourself. Are you sure the built jar is correct and includes the changes?

[pasarbia]

liquibase-cassandra.log

@maximevw
I modified the JDBC URL and the result is the same, as expected.
The build jar was created with mvn clean package from main branch. I also decompiled the artifact and checked that the changes are there.
I have no idea why it uses the standard implementation of Liquibase instead of the custom lock service for Cassandra.
I attached the whole log, hopefully can help you help me. :)

[maximevw]

@pasarbia

There is something surprising in the logs you provided, the loaded jar for liquibase-cassandra is : /home/ubuntu/liquibase/lib/liquibase-cassandra-4.27.0.jar but previously you indicated this path /home/ubuntu/liquibase/lib/liquibase-cassandra-4.28.0-SNAPSHOT.jar, but maybe you just renamed the jar.

Anyway, I think I finally found the root cause of the issue:
In StandardLockService, we have this line 135:
if (!isDatabaseChangeLogLockTableInitialized(createdTable, true)) {
But the problem is in LockServiceCassandra, only the method isDatabaseChangeLogLockTableInitialized(boolean tableJustCreated) is overridden. So, here, it still calls a method from StandardLockService including a COUNT(*) instead of a patched method in LockServiceCassandra.

Consequently, a complementary patch is needed to also override the method isDatabaseChangeLogLockTableInitialized(boolean tableJustCreated, boolean forceRecheck) in LockServiceCassandra. I'll try to provide this asap.

[maximevw]

@filipelautert @rberezen Could you have a look to the PR #296 please?

This should fix the issue described in the last messages. I locally tested and it's now walking through the expected code path (previous patch #292).

[pasarbia]

@maximevw I’m grateful for your help!!
The good news is that I don't have the count rows issue, so your code is working!!
Unfortunately, now I have:
[2024-05-17 14:19:48] FINE [liquibase.ext] Failed to create or initialize the lock table, trying again, iteration 1 of 10 liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: com.datastax.oss.driver.api.core.servererrors.InvalidQueryException: TRUNCATE TABLE is not yet supported. [Failed SQL: (0) TRUNCATE TABLE fsp_core.DATABASECHANGELOGLOCK] at liquibase.executor.jvm.ChangelogJdbcMdcListener.execute(ChangelogJdbcMdcListener.java:40) at liquibase.lockservice.StandardLockService.init(StandardLockService.java:138) at liquibase.command.core.helpers.DatabaseChangelogCommandStep.checkLiquibaseTables(DatabaseChangelogCommandStep.java:145) at liquibase.command.core.helpers.DatabaseChangelogCommandStep.run(DatabaseChangelogCommandStep.java:91) at liquibase.command.CommandScope.execute(CommandScope.java:219)

I hope you don't get bored with this.
liquibase-cassandra-truncate.log

[maximevw]

@pasarbia
Oh! It seems AWS keyspaces is not really friendly with Liquibase-Cassandra. 😄

There is here probably a little bit more work to do in order to be compliant with all the restrictions imposed by AWS Keyspaces.

To fix the TRUNCATE issue, it will be neccessary to develop another workaround (like for the rows count): replacing all the truncat queries by DROP TABLE + re-CREATE TABLE.

And ideally a deeper analysis of all the queries potentially not compatible with AWS keyspaces should be performed to prevent other similar issues.

[maximevw]

As this issue is closed and the incompatibility problem is large enough finally, I created a dedicated issue: #297