Liquibase fails when username/password are enabled. #110

szandany · 2021-10-21T23:39:30Z

Context

Liquibase with Cassandra Extension seems to work fine unless user/password are enabled.

It should work with user/password enabled.

Steps to reproduce

Spin up a Cassandra environment that uses user and password. For example:

docker run --name cassandra -e CASSANDRA_PASSWORD=Password1 -e CASSANDRA_PASSWORD_SEEDER=yes -p 9042:9042 -d bitnami/cassandra

Create a keyspace. For example (with cqlsh or other tool):

myIp=$(docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}')script ```
docker exec -ti cassandra cqlsh -u cassandra -p $CASSANDRA_PASSWORD $myIp -e "CREATE KEYSPACE liquibase WITH REPLICATION = { 'class' : 'SimpleStrategy','replication_factor' : 1}"

Configure liquibase.properties like the following:

changeLogFile: changelog.sql
url: jdbc:cassandra://localhost:9042;AuthMech=1;DefaultKeyspace=liquibase
username: cassandra
password: Password1
classpath: ../../Drivers/Cassandra/CassandraJDBC42.jar:../../Drivers/Cassandra/liquibase-cassandra-4.5.0.jar
defaultSchemaName: liquibase

Create a changelog file changelog.sql:

--liquibase formatted sql

--changeset SteveZ:765
CREATE TABLE immatable (foo VARCHAR, PRIMARY KEY (foo))
--rollback DROP TABLE immatable;
--changeset SteveZ:766
CREATE TABLE immatable2 (foo2 VARCHAR, PRIMARY KEY (foo2))
--rollback DROP TABLE immatable2;

--changeset AdeelM:898
CREATE TABLE orders (foo2 VARCHAR, PRIMARY KEY (foo2))
--rollback DROP TABLE orders;

Run the following commands in the following order:

liquibase status --verbose
    liquibase update
    liquibase status --verbose
    liquibase update

Actual results in the console output

liquibase status --verbose
3 change sets have not been applied to cassandra@jdbc:cassandra://localhost:9042;AuthMech=1;DefaultKeyspace=liquibase
     changelog.sql::765::SteveZ
     changelog.sql::766::SteveZ
     changelog.sql::898::AdeelM
Liquibase command 'status' was executed successfully.

liquibase update
    Liquibase command 'update' was executed successfully.

Changes are showing in the database as expected including tracking tables with the proper entries.

liquibase status --verbose
3 change sets have not been applied to cassandra@jdbc:cassandra://localhost:9042;AuthMech=1;DefaultKeyspace=liquibase
     changelog.sql::765::SteveZ
     changelog.sql::766::SteveZ
     changelog.sql::898::AdeelM
Liquibase command 'status' was executed successfully.

It appears that Liquibase is unable to query the tracking table correctly and considers the changes as "undeployed".

liquibase update

The command hangs indefinitely.

It appears that after the first update the DATBASECHANGELOGLOCK table was left locked.

Expected Result

Command completes
local DBCL is updated
DBCL lock released
so update doesnt hang
Repeat running of liquibase status doesnt show undeployed changesets, which have in fact been deployed.

Test Requirements (Liquibase Internal QA)

SETUP

Download the latest Liquibase-Cassandra extension

Release v4.5.0 · liquibase/liquibase-cassandra · GitHub
Copy the extension to liquibase/lib

Download Cassandra Simba JDBC Driver

https://downloads.datastax.com/#odbc-jdbc-drivers
On this page, users should select Simba JDBC Driver for Apache Cassandra. Unless there are reasons not to, take the default package option. The driver downloads as a zip file named SambacassandraJDBC43-x.x.x.zip. Extract the CassandraJDBCxx.jar and place it in the liquibase/lib directory.

Pull cassandra docker image : https://hub.docker.com/_/cassandra

docker pull cassandra

Run cassandra:

docker run --name cassandra -e CASSANDRA_PASSWORD=Password1 -e CASSANDRA_PASSWORD_SEEDER=yes ~~p9042:9042~~ d cassandra:latest

Verify Cassandra status is healthy

CD /bin
Run nodetool/status
nodetool status
Sample Output:

Datacenter: datacenter1 
======================= 

Status=Up/Down 

|/ State=Normal/Leaving/Joining/Moving 

—  Address     Load       Tokens       Owns (effective)  Host ID                               Rack 

UN  172.17.0.6  128.61 KiB  256          100.0%            53d7e4d1-237f-44c0-88eb-728b3bd0a8f7  rack1

Connect to Cassandra Using csqlsh and Create keySpace

Run SQL to create Keyspace
CREATE KEYSPACE liquibase WITH REPLICATION = {‘class’ : ‘SimpleStrategy’, ‘replication_factor’ : 1 }
Sample Output

cd /opt/cassandra/bin

cqlsh

Connected to Test Cluster at 127.0.0.1:9042.

[cqlsh 5.0.1 | Cassandra 3.11.10 | CQL spec 3.4.4 | Native protocol v4]

Use HELP for help.

cqlsh> CREATE KEYSPACE liquibase WITH REPLICATION = {‘class’ : ‘SimpleStrategy’, ‘replication_factor’ : 1 };




Useful Links 

[How to Create Keyspace in Cassandra (phoenixnap.com)](https://phoenixnap.com/kb/cassandra-create-keyspace)

[http://blog.ditullio.fr/2016/06/10/docker-docker-basics-cassandra](https://href.li/?http://blog.ditullio.fr/2016/06/10/docker-docker-basics-cassandra) 

- - -
**Manual Test Requirements: Cassandra with Username and Password**

_Verify liquibase update creates the three tables in changelog.sql._

* immatable
* immatable2
* orders

Query to see all tables in cassandra keyspace in Docker Cassandra:

cd /opt/cassandra/bin

cqlsh

Connected to Test Cluster at 127.0.0.1:9042.

cqlsh> USE liquibase;
csqlsh> DESCRIBE TABLES;


_Verify DATABASECHANGELOG includes rows for the three tables._

* immatable, status EXECUTED
* immatable2, status EXECUTED
* orders, status EXECUTED

cd /opt/cassandra/bin

cqlsh

Connected to Test Cluster at 127.0.0.1:9042.

cqlsh> USE liquibase;
csqlsh> DESCRIBE TABLE DATABASECHANGELOG;


_Verify DATABASECHANGELOGLOCK is unlocked after update._

* locked = 0 (unlocked)

cd /opt/cassandra/bin

cqlsh

Connected to Test Cluster at 127.0.0.1:9042.

cqlsh> USE liquibase;
csqlsh> DESCRIBE TABLE DATABASECHANGELOGLOCK;


_Verify liquibase status shows that the database is up to date._

`liquibase status --changelog-file changelog.sql`

_Verify a second Liquibase update is successful._

`liquibase update --changelog-file changelog.sql`

* There is no error
* The update does not hang
* No additional changes deploy (all three tables are already on the database)

**Manual Test Requirements: Cassandra without Authentication**

* Stop the current Cassandra Docker instance
  * `docker stop cassandra`
  * `docker remove cassandra`
* Edit your liquibase.properties file to specify a connection string without a username or password. 
  * `url: jdbc:cassandra://localhost:9042/liquibase;DefaultKeyspace=liquibase`
* Start a new Cassandra Docker instance that does not require authentication.
  * `docker run --name cassandra -p9042:9042 -d cassandra:latest`
* Re-create the liquibase keyspace.
  * `CREATE KEYSPACE liquibase WITH REPLICATION = {'class' : 'SimpleStrategy', 'replication_factor' : 1 };`

_Verify liquibase update without authentication creates three tables specified in changelog.sql._

* immatable
* immatable2
* orders

_Verify a second liquibase update using changelog.sql is successful._

* No errors
* Update does not hang waiting for a lock
* No changesets are executed (all are were deployed in the first update).

- - -
**Automated Test Requirements**

* None. Neither the functional test framework nor the test-harness support Cassandra at this time.
* Co-ordinate with development to add a unit or integration test.

This issue could have been fixed [https://github.com/liquibase/liquibase-cassandra/pull/108](https://github.com/liquibase/liquibase-cassandra/pull/108|smart-link)



┆Issue is synchronized with this [Jira Bug](https://datical.atlassian.net/browse/LB-2115) by [Unito](https://www.unito.io)

The text was updated successfully, but these errors were encountered:

sync-by-unito · 2021-10-27T17:55:25Z

➤ Surya Aki commented:

Related issue
#108 ( https://github.com/liquibase/liquibase-cassandra/pull/108|smart-link )

sync-by-unito · 2021-10-27T17:56:27Z

➤ Surya Aki commented:

Mario Champion I am not sure how cassandra works, that’s where we should start investigating this issue..

sync-by-unito · 2021-11-17T22:29:05Z

➤ Nehal Dixit commented:

please ping me when this is ready for UAT cc Steven Massaro Erzsebet Carmean Surya Aki

StevenMassaro · 2021-11-18T18:59:14Z

While I was making the fix for this ticket, I discovered that there was an additional issue with the way that the Cassandra extension handled the databasechangelog table (not the lock table). The fix was identical to the fix for the databasechangeloglock table, so I implemented the fix as well.

This issue presents itself as a stack trace in the console output from running liquibase update using the above steps. The changes still appear to apply successfully, but this stacktrace is still printed out. I believe (but did not verify) that this issue was preventing the entries from being logged in the databasechangelog table correctly. In any case, the issue is resolved with my fix.

java.sql.SQLNonTransientConnectionException: [Simba][JDBC](10100) Connection Refused: [Simba][JDBC](11640) Required Connection Key(s): PWD, UID; [Simba][JDBC](11480) Optional Connection Key(s): BatchLimit, BinaryColumnLength, ConcurrentRequestsLimit, ConnectTimeoutMillis, DecimalColumnPrecision, DecimalColumnScale, EnableAsynchronousWrites, EnableCaseSensitive, EnableLatencyAware, EnablePaging, EnableTokenAware, FlattenUDTColumn, LoadBalancingPolicy, QueryMode, ReadTimeoutMillis, RowsPerPage, SSLMode, StringColumnLength, TunableConsistency, VarintColumnPrecision, VTTableNameSeparator
        at com.simba.cassandra.exceptions.ExceptionConverter.toSQLException(Unknown Source)
        at com.simba.cassandra.jdbc.common.BaseConnectionFactory.checkResponseMap(Unknown Source)
        at com.simba.cassandra.jdbc.common.BaseConnectionFactory.doConnect(Unknown Source)
        at com.simba.cassandra.jdbc.common.AbstractDriver.connect(Unknown Source)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:270)
        at liquibase.ext.cassandra.database.CassandraDatabase.getStatement(CassandraDatabase.java:134)
        at liquibase.ext.cassandra.changelog.CassandraChangeLogHistoryService.getNextSequenceValue(CassandraChangeLogHistoryService.java:56)
        at liquibase.sqlgenerator.core.MarkChangeSetRanGenerator.generateSql(MarkChangeSetRanGenerator.java:92)
        at liquibase.sqlgenerator.core.MarkChangeSetRanGenerator.generateSql(MarkChangeSetRanGenerator.java:25)
        at liquibase.sqlgenerator.SqlGeneratorChain.generateSql(SqlGeneratorChain.java:30)
        at liquibase.sqlgenerator.SqlGeneratorFactory.generateSql(SqlGeneratorFactory.java:220)
        at liquibase.executor.AbstractExecutor.applyVisitors(AbstractExecutor.java:105)
        at liquibase.executor.jvm.JdbcExecutor.access$400(JdbcExecutor.java:39)
        at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:371)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:81)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:149)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:133)
        at liquibase.changelog.StandardChangeLogHistoryService.setExecType(StandardChangeLogHistoryService.java:391)
        at liquibase.database.AbstractJdbcDatabase.markChangeSetExecStatus(AbstractJdbcDatabase.java:1136)
        at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:60)
        at liquibase.changelog.ChangeLogIterator$2.lambda$null$0(ChangeLogIterator.java:111)
        at liquibase.Scope.lambda$child$0(Scope.java:177)
        at liquibase.Scope.child(Scope.java:186)
        at liquibase.Scope.child(Scope.java:176)
        at liquibase.Scope.child(Scope.java:155)
        at liquibase.changelog.ChangeLogIterator$2.lambda$run$1(ChangeLogIterator.java:110)
        at liquibase.Scope.lambda$child$0(Scope.java:177)
        at liquibase.Scope.child(Scope.java:186)
        at liquibase.Scope.child(Scope.java:176)
        at liquibase.Scope.child(Scope.java:155)
        at liquibase.Scope.child(Scope.java:240)
        at liquibase.changelog.ChangeLogIterator$2.run(ChangeLogIterator.java:94)
        at liquibase.Scope.lambda$child$0(Scope.java:177)
        at liquibase.Scope.child(Scope.java:186)
        at liquibase.Scope.child(Scope.java:176)
        at liquibase.Scope.child(Scope.java:155)
        at liquibase.Scope.child(Scope.java:240)
        at liquibase.Scope.child(Scope.java:244)
        at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:66)
        at liquibase.Liquibase.lambda$null$0(Liquibase.java:265)
        at liquibase.Scope.lambda$child$0(Scope.java:177)
        at liquibase.Scope.child(Scope.java:186)
        at liquibase.Scope.child(Scope.java:176)
        at liquibase.Scope.child(Scope.java:155)
        at liquibase.Scope.child(Scope.java:240)
        at liquibase.Liquibase.lambda$update$1(Liquibase.java:264)
        at liquibase.Scope.lambda$child$0(Scope.java:177)
        at liquibase.Scope.child(Scope.java:186)
        at liquibase.Scope.child(Scope.java:176)
        at liquibase.Scope.child(Scope.java:155)
        at liquibase.Liquibase.runInScope(Liquibase.java:2404)
        at liquibase.Liquibase.update(Liquibase.java:211)
        at liquibase.Liquibase.update(Liquibase.java:197)
        at liquibase.integration.commandline.Main.doMigration(Main.java:1875)
        at liquibase.integration.commandline.Main$1.lambda$run$0(Main.java:397)
        at liquibase.Scope.lambda$child$0(Scope.java:177)
        at liquibase.Scope.child(Scope.java:186)
        at liquibase.Scope.child(Scope.java:176)
        at liquibase.Scope.child(Scope.java:155)
        at liquibase.integration.commandline.Main$1.run(Main.java:396)
        at liquibase.integration.commandline.Main$1.run(Main.java:220)
        at liquibase.Scope.child(Scope.java:186)
        at liquibase.Scope.child(Scope.java:162)
        at liquibase.integration.commandline.Main.run(Main.java:220)
        at liquibase.command.AbstractCliWrapperCommandStep.run(AbstractCliWrapperCommandStep.java:32)
        at liquibase.command.CommandScope.execute(CommandScope.java:150)
        at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:45)
        at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:15)
        at picocli.CommandLine.executeUserObject(CommandLine.java:1953)
        at picocli.CommandLine.access$1300(CommandLine.java:145)
        at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2352)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2346)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2311)
        at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2179)
        at picocli.CommandLine.execute(CommandLine.java:2078)
        at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$1(LiquibaseCommandLine.java:325)
        at liquibase.Scope.child(Scope.java:186)
        at liquibase.Scope.child(Scope.java:162)
        at liquibase.integration.commandline.LiquibaseCommandLine.execute(LiquibaseCommandLine.java:291)
        at liquibase.integration.commandline.LiquibaseCommandLine.main(LiquibaseCommandLine.java:80)

sync-by-unito bot assigned StevenMassaro Nov 16, 2021

StevenMassaro linked a pull request Nov 17, 2021 that will close this issue

Obtain database connection to Cassandra with username and password (LB-2115) #115

Merged

sync-by-unito bot assigned obovsunivskyii Nov 18, 2021

suryaaki2 closed this as completed in #115 Nov 23, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Liquibase fails when username/password are enabled. #110

Liquibase fails when username/password are enabled. #110

szandany commented Oct 21, 2021 •

edited by sync-by-unito bot

Loading

nodetool status

sync-by-unito bot commented Oct 27, 2021

sync-by-unito bot commented Oct 27, 2021

sync-by-unito bot commented Nov 17, 2021

StevenMassaro commented Nov 18, 2021

Liquibase fails when username/password are enabled. #110

Liquibase fails when username/password are enabled. #110

Comments

szandany commented Oct 21, 2021 • edited by sync-by-unito bot Loading

Context

Steps to reproduce

Actual results in the console output

Expected Result

Test Requirements (Liquibase Internal QA)

SETUP

nodetool status

cd /opt/cassandra/bin

cqlsh

cd /opt/cassandra/bin

cqlsh

cd /opt/cassandra/bin

cqlsh

cd /opt/cassandra/bin

cqlsh

sync-by-unito bot commented Oct 27, 2021

sync-by-unito bot commented Oct 27, 2021

sync-by-unito bot commented Nov 17, 2021

StevenMassaro commented Nov 18, 2021

szandany commented Oct 21, 2021 •

edited by sync-by-unito bot

Loading