Incompatibility with AWS Keyspaces

[maximevw]

Hello,

I recently proposed fixes (#292 and #296) for the issue #289. But, regarding the last messages posted in the mentioned issue, this fix is not sufficient to run Liquibase Cassandra on a AWS Keyspaces database.

Indeed, as documented here, some features of Cassandra like aggregate functions (like COUNT), TRUNCATE, ... are not supported.

After a quick analysis, it's not that simple to quick fix this like I did for the rows count and this need a little more work.

Proposal for remediation

In order to make Liquibase Cassandra compatible with AWS Keyspaces, I suggest to implement the following changes:

1. Introduce a new property liquibase.cassandra.awsKeyspacesCompatibilityModeEnabled in the liquibase.properties file to set to true when running against an AWS Keyspaces instance.
2. For each potentially incompatible statement in the different Liquibase services used by Liquibase Cassandra, check the property and execute the appropriate query: the original one or the alternative one:

• For TRUNCATE TABLE statements: replace by DROP TABLE + CREATE TABLE again
• For SELECT COUNT statements: same principle as for the fix #292, i.e. replacing by a simple SELECT and counting rows programmatically in Java
• For MAX(column) function (used in TagDatabaseGeneratorCassandra): select all values and calculate the max programmatically in Java

I didn't find any other incompatibilities, but still need to be confirmed.

I can help for implementation. 🙂

[filipelautert]

Hello @maximevw, as this issue has been open for some time and nothing else showed up I believe that the changes you listed are enough. We also need to document the new flag (I can help to handle that) . Do you want to start a PR then we can work on this implementation?

[maximevw]

Hello @filipelautert Sure, I should be able to start working on that during the next days. I'll keep you informed as soon as a first draft is ready.

[maximevw]

Hello @filipelautert

As discussed, I submitted PR #303 to improve the compatibility with AWS Keyspaces. Maybe @pasarbia could help for testing of the fix (following the discussions in #289). I let you review this.

[pasarbia]

hello @maximevw, I started implementing liquibase for our project and sometimes I get this error: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: Error executing SQL SELECT * FROM fsp_core.DATABASECHANGELOGLOCK: com.datastax.oss.driver.api.core.servererrors.InvalidQueryException: unconfigured table fsp_core.databasechangeloglock liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: com.datastax.oss.driver.api.core.servererrors.InvalidQueryException: fsp_core.databasechangeloglock is currently being created, altered or deleted. looks like it needs more time for some operations. If I run the second time, or third time the update command, it works fine. uncofigured_table.txt

[maximevw]

Hello @pasarbia,

According to the provided logs, it seems that the table DATABASECHANGELOGLOCK is not created/available yet when the next query on it (SELECT *) is executed. It also explains why it works the second or third time (the table is finally available at this moment). Note this would probably be the same thing if we could have used the SELECT COUNT(*) query.

This might come from the debouncing feature of the Java driver used by Liquibase as explained here: https://stackoverflow.com/questions/74033150/is-cassandra-table-creation-slow So, maybe you should specify a specific configuration file for your Cassandra client including the appropriate debouncing configuration.

[pasarbia]

Hi @maximevw, I have changed the configuration for debouncing and played also with other configuration, here is an example of my application.conf file: `datastax-java-driver {

basic.request { timeout = 30 seconds 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 = path to truststore truststore-password = truststore password } advanced.request { trace { interval = 30 seconds } }

advanced.metadata { schema { debouncer {

How long the driver waits to apply a refresh. If another refresh is requested within that

    # time, the window is reset and a single refresh will be triggered when it ends.
    #
    # Required: yes
    # Modifiable at runtime: no
    # Overridable in a profile: no
    window = 30 seconds

    # The maximum number of refreshes that can accumulate. If this count is reached, a refresh
    # is done immediately and the window is reset.
    #
    # Required: yes
    # Modifiable at runtime: no
    # Overridable in a profile: no
    max-events = 10
  }}}}`

I tested with different values, but nothing changed.

I can see that the error is: Failed to create or initialize the lock table, trying again, iteration 9 of 10 liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DatabaseException: Error executing SQL SELECT * FROM fsp_core.DATABASECHANGELOGLOCK: com.datastax.oss.driver.api.core.servererrors.InvalidQueryException: unconfigured table fsp_core.databasechangeloglock at liquibase.ext.cassandra.lockservice.LockServiceCassandra.isDatabaseChangeLogLockTableInitialized(LockServiceCassandra.java:158) but the standardLockService is using a sleep function here https://github.com/liquibase/liquibase/blob/039bb536fe0525111f2ea8a03307233e66e71aaf/liquibase-standard/src/main/java/liquibase/lockservice/StandardLockService.java#L158

I not good at Java, but could a sleep function help?

log.txt

[maximevw]

Hello @pasarbia

Thank you for your feedback! I found this interesting topic in AWS Keyspaces documentation about tables creation: https://docs.aws.amazon.com/keyspaces/latest/devguide/working-with-tables.html#tables-create

It seems that, for AWS (i.e. only when the compatibility mode is enabled), the best way to proceed would be to check the status of the table in system_schema_mcs.tables before querying it, instead of using the sleep method.

What do you think about this approach @filipelautert?

[filipelautert]

the table in system_schema_mcs.tables before querying it, instead of using the sleep method.

@maximevw we did something similar to the dynamodb extension (it's a pro only extension) . For dynamo we use AWS sdk so we used method waitForActive ( https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/AppendixSampleDataCodeJava.html ) .

I believe that querying to the table in system_schema_mcs.tables will have the same effect and will be beneficial for users.

[maximevw]

Hello @pasarbia,

Following the last discussions, I suggested a fix for the issue related to the unavailability of the databaseChangelogLock table in AWS Keyspaces, on this branch: https://github.com/maximevw/liquibase-cassandra/tree/fix/issue-297-complement

If it helps to solve your bug, I'll create a pull request to integrate it to liquibase-cassandra.

[pasarbia]

Hello @maximevw, I have tested your last PR and I get the error: ` Unexpected error running Liquibase: Waiting for databaseChangeLogLock table ready failed or timed out

liquibase.exception.CommandExecutionException: liquibase.exception.UnexpectedLiquibaseException: Waiting for databaseChangeLogLock table ready failed or timed out 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) 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) at picocli.CommandLine.execute(CommandLine.java:2170) at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$2(LiquibaseCommandLine.java:395) at liquibase.Scope.child(Scope.java:199) at liquibase.Scope.child(Scope.java:175) at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$3(LiquibaseCommandLine.java:370) at liquibase.Scope.child(Scope.java:199) at liquibase.Scope.child(Scope.java:175) at liquibase.integration.commandline.LiquibaseCommandLine.execute(LiquibaseCommandLine.java:367) at liquibase.integration.commandline.LiquibaseCommandLine.main(LiquibaseCommandLine.java:104) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:568) at liquibase.integration.commandline.LiquibaseLauncher.main(LiquibaseLauncher.java:116) Caused by: liquibase.exception.UnexpectedLiquibaseException: Waiting for databaseChangeLogLock table ready failed or timed out at liquibase.ext.cassandra.lockservice.LockServiceCassandra.isDatabaseChangeLogLockTableInitialized(LockServiceCassandra.java:158) 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) ... 23 more`

log.txt

[maximevw]

Thanks for testing @pasarbia!

It seems it failed to access the system_schema_mcs, but I don't know why exactly, so I'll try to add more logs when this specific query fails and let you know when it will be updated.

Meanwhile, could you please check the user used by Liquibase in your connection to AWS Keyspaces has the sufficient permissions to execute SELECT queries on system tables?

[maximevw]

Hello @pasarbia

I updated my branch https://github.com/maximevw/liquibase-cassandra/tree/fix/issue-297-complement. There was (at least) a SQL syntax error in my query. I also added more logs in case of error to ease debugging.

[pasarbia]

Hello @maximevw, I receive the same error. I have also checked the permissions, and everything looks fine. Empty_result_set_expected_one_row.txt

[maximevw]

Thank you for this new test @pasarbia!

With the added logs, I better understand what happened: the "databasechangeloglock" table is not even present in the system_schema_mcs.tables table when the check is executed. So, I'll adapt my fix in consequence and let you know when it's available.

But, it seems it takes a lot of time before the newly created table is available (in your test, it's still not available after 3 minutes)!

[pasarbia]

@maximevw I tried another run, and while it was at iteration 8 of 10, I search in AWS Keyspaces with the query: SELECT keyspace_name, table_name, status FROM system_schema_mcs.tables WHERE keyspace_name = 'fsp_core' AND table_name = 'databasechangeloglock'; and the table status was ACTIVE.

However, liquibase exited with the same error.

[maximevw]

@pasarbia, the difference between your query and the one performed in liquibase is the case of the table name. So, I think the fix will be to use lower case for the table name otherwise it doesn't return any row. Thanks for pointing this out!

[maximevw]

@pasarbia I implemented the discussed change in this commit: https://github.com/maximevw/liquibase-cassandra/commit/c751eceb64adf363fb23e9d314cd6bd4fa98bf9f

Hope this one finally returns the expected result 🤞🏻

[pasarbia]

@maximevw Thank you for waiting for this to be tested, I was out of office until today. I have tested your last commit everything works as expected!

[maximevw]

@pasarbia Great news! I will submit these changes in a new PR.