ing-bank / cassandra-jdbc-wrapper Goto Github PK

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

In TypeMetadataResultSetBuilder::buildUDTs, typeNamePattern needs a null-check. Most JDBC drivers support a null typeNamePattern even though the JDBC javadoc does not specifically call out this case.

It is easy enough to use:
if (typeNamePattern != null && typeNamePattern.contains(".")) { on line L119.

Discussed in #40

Technical Context

As of today to use the DBAAS component on this Cassandra JDBC wrapper you need to provide a Secure connect bundle ZIP. Now using a library like the astra-sdk-devops it is possible to download this library on the fly and simply the URL.

Astra JDBC Wrapper

The work has been done in the following repository
https://github.com/DataStax-Examples/astra-jdbc-connector/tree/main

Ending with url than looks like:

jdbc:astra://<db_name>/<keyspace>?token=<token>

Outcomes

This issue is open for multiple purposes:

• Bring awareness to the ING Team and have their acknowledgment that we explicit mentioned them everywhere and that the component is fully open source and free as the Apache License 2.

• Bring attention to awesome-astra.github.io to see all cool integrations available for Cassandra (/Astra)

• Asked the question should those repositories be merged (I do not see an need here).

Currently i'm using cassandra-jdbc-wrapper 4.12.0 and cassandra-liquibase 4.28.0. DefaultOptionSet always returns 0 in getSQLUpdateResponse() method and to avoid such behaviour i've been told to use compliancemode=Liquibase parameter in connection url. liquibase/liquibase-cassandra#212 (comment)
it would be helpful to have this, because it just doesnt work otherwise, if we configure liquibase inside java app

Does this driver supports SSL ?
if yes where I need to pass the keystore and truststore information and what is the connection for enabling the SSL ?

Hello,

I tried to use the latest version of this driver (4.10.2) with the latest version of liquibase-core and liquibase-cassandra (4.25.0). I use SBT (1.9.7) and Java 11.

I haven't managed to replace the old datastax driver with the new one.

Previously, settings were made as follows:

val database = DatabaseFactory.getInstance.findCorrectDatabaseImplementation(
   new JdbcConnection(
      new Driver().connect(
          s"jdbc:cassandra://localhost:${cqlPort.toString}/$keyspace;DefaultKeyspace=$keyspace",
          new Properties()
     )
  )
)
    
new Liquibase(
  changeLog.getAbsolutePath,
  new FileSystemResourceAccessor(new File("/")),
  database
)

After swapping with the ING driver, I updated the above code like this (https://docs.liquibase.com/workflows/liquibase-community/using-liquibase-java-api.html):

val connection: Connection = DriverManager.getConnection(
        s"jdbc:cassandra://localhost:${cqlPort.toString}/$keyspace?compliancemode=Liquibase&localdatacenter=dc1&requesttimeout=10000"
 )

new Liquibase(
  changeLog.getAbsolutePath,
  new FileSystemResourceAccessor(new File("/")),
  DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(connection))
)

And I'm getting the error:

java.lang.ClassCastException: class liquibase.database.jvm.JdbcConnection cannot be cast to class com.ing.data.cassandra.jdbc.CassandraConnection (liquibase.database.jvm.JdbcConnection and com.ing.data.cassandra.jdbc.CassandraConnection are in unnamed module of loader sbt.internal.LayeredClassLoader @20850fc2)

Let me know if you need more details.
Thanks in advance

The current implementation of executeBatch() returns a wrong number of effected rows given the underlying driver does not provide such information. In the line below, rowCounts[] is updated with 1 for each statement in batch:

Accordingly to executeBatch() javadoc it should be updated with SUCCESS_NO_INFO.

I am trying to use the new version of liquibase-cassandra which uses this driver. However I keep getting the com.datastax.oss.driver.api.core.NoNodeAvailableException: No node was available to execute the query So created a small snippet only with the driver which also results in the same error.

public class HelloCassandra {
    public static void main(final String[] args) throws SQLException {
        // Used driver: com.ing.data.cassandra.cassandra.jdbc.CassandraDriver
        final String url = "jdbc:cassandra://node1.dc1-llds-1-tst.cassandra.xx:9142/contacting?configfile=/Users/BO55NK/IdeaProjects/untitled/src/main/resources/test.conf";
       
        final Connection connection = DriverManager.getConnection(url);
        connection.prepareStatement("Select * from contacting.contacting_configs").executeQuery();
    }
}

My test.conf looks like this

datastax-java-driver {

  basic.load-balancing-policy {
    class = DefaultLoadBalancingPolicy
    local-datacenter: DC1
  }
  basic.session-keyspace = "contacting"
  basic.request {
    timeout = 20 seconds
    consistency = LOCAL_ONE

  }
  advanced.auth-provider {
    class = PlainTextAuthProvider
    username: xxx_owner
    password: "xxxxxxx"
  }
  advanced.ssl-engine-factory {
    class = DefaultSslEngineFactory
    hostname-validation = false

    truststore-path = /resources/http-trust.jks
    truststore-password = xxxx
  }
}

What could I be possibly missing ?

ResultSet.getInt throws NumberFormatException if the field is null. It should return 0 instead as part the JDBC javadocs.

Hello,
I have a configuration with two nodes with replication factor 2; they are behind a router with NAT. From the outside, I want to access to one of the two nodes only (for now), therefore I configured the DNS to point to the router and the router NATs it to node 1:

When from the outside I try to access the node, it seems the driver tries to use the internal IP (node1_local_ip) and therefore it fails:

using store jdbc:cassandra://cassandra.mydm.com:9042/uzzstore?localdatacenter=datacenter1
DataStax Java driver for Apache Cassandra(R) (com.datastax.oss:java-driver-core) version 4.17.0
Adding handler to invalidate cached prepared statements on type changes
Using native clock for microsecond precision
[s0|/node1_local_ip:9042  Error while opening new channel (ConnectionInitException: [s0|connecting...] Protocol initialization request, step 1 (STARTUP {CQL_VERSION=3.0.0, DRIVER_NAME=DataStax Java driver for Apache Cassandra(R), DRIVER_VERSION=4.17.0, CLIENT_ID=58789f9d-9a41-4104-af90-5d545a7eea8d}): failed to send request (java.nio.channels.NotYetConnectedException))
Connected to cluster: Uzz, with session: s0
Datacenter: datacenter1; Host: cassandra.mydm.com/<unresolved>:9042; Rack: rack1
Datacenter: datacenter1; Host: /node2_local_ip:9042; Rack: rack1
Node: cassandra.mydm.com/<unresolved>:9042 runs Cassandra v.3.0.8
[s0|/node1_local_ip Error while opening new channel (ConnectionInitException: [s0|connecting...] Protocol initialization request, step 1 (STARTUP {CQL_VERSION=3.0.0, DRIVER_NAME=DataStax Java driver for Apache Cassandra(R), DRIVER_VERSION=4.17.0, CLIENT_ID=58789f9d-9a41-4104-af90-5d545a7eea8d}): failed to send request (java.nio.channels.NotYetConnectedException))

As I see, it tries to use the local ip of the second node

Is that the intended behaviour? Is there any workaround?

ERRATA: the second node is listening to the standard port 9042, not 9142 as in the picture...

Hello, usually a JDBC driver is loaded from its URL and should not be specified. I am using ORMLite in my project and trying to use the cassandra jdbc I get this error:

 Caused by: java.lang.IllegalArgumentException: Unknown database-type url part 'cassandra' in: jdbc:cassandra://192.168.1.100:9042/keyspacetest?localdatacenter=DC1
        at com.j256.ormlite.jdbc.db.DatabaseTypeUtils.createDatabaseType(DatabaseTypeUtils.java:55)
        at com.j256.ormlite.jdbc.BaseJdbcConnectionSource.initialize(BaseJdbcConnectionSource.java:101)
        at com.j256.ormlite.jdbc.JdbcConnectionSource.<init>(JdbcConnectionSource.java:104)
        at com.j256.ormlite.jdbc.JdbcConnectionSource.<init>(JdbcConnectionSource.java:47)

Same thing if I use it in DBEaver without explicitly providing the driver.

Am I missing something?

@maximevw Firstly Thanks for the release 4.10.2 which fixed my previous issue.

However I now encounter a new one . When I run it I now get

Unexpected error running Liquibase: line 1:53 no viable alternative at input '(' (... TABLE contacting.DATABASECHANGELOG (ID [VARCHAR](...) [Failed SQL: (0) CREATE TABLE contacting.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))]

I did read about the compliance mode and #25 . I am not sure if all liquibase users should use that compliance mode. I tried with that as well but no luck. Any pointers ?

Hi,

I'm using the latest JDBC driver and trying to connect to Astra. I was trying to connect to Vector and general ones.

URL:

jdbc:cassandra://dbaas/another_datagrip?user=token&password=AstraCS:$$TOKEN$$&secureconnectbundle=/Users/vasilii.chernov/Projects/secure-connect-another-datagrip.zip

Test Connection

Log chunk

2024-06-27 18:27:29,131 [14160048]   INFO - #c.i.e.r.RemoteProcessSupport - SSL MODE is REQUIRE
2024-06-27 18:27:29,131 [14160048]   INFO - #c.i.e.r.RemoteProcessSupport - Setting `verifyServerCertificate` to `false`
2024-06-27 18:27:29,136 [14160053]   WARN - #c.i.e.r.RemoteProcessSupport - SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
2024-06-27 18:27:29,136 [14160053]   WARN - #c.i.e.r.RemoteProcessSupport - SLF4J: Defaulting to no-operation (NOP) logger implementation
2024-06-27 18:27:29,136 [14160053]   WARN - #c.i.e.r.RemoteProcessSupport - SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
2024-06-27 18:27:32,029 [14162946]   INFO - #c.i.e.r.RemoteProcessSupport - Driver: Cassandra JDBC Driver 4.12.0-DataGrip-patch-2
2024-06-27 18:27:32,031 [14162948]   INFO - #c.i.e.r.RemoteProcessSupport - getDatabaseProductName: Cassandra
2024-06-27 18:27:32,171 [14163088]   INFO - #c.i.e.r.RemoteProcessSupport - release_version: 4.0.0.6816
2024-06-27 18:27:32,174 [14163091]   INFO - #c.i.e.r.RemoteProcessSupport - Detected: CASSANDRA 4.0.0.6816
2024-06-27 18:27:32,261 [14163178]   INFO - #c.i.d.c.JdbcEngineUtils - Connection is invalid

Is there any way to get more details to identify the root cause?

QStudio is a Free SQL IDE that tries to support many databases via JDBC.
Users have asked for databases or connections methods not currently supported. One user mentioned Cassandra so far.

If you would benefit from an IDE and are willing to do some testing please let me know or post here:
timeseries/qstudio#67

DB tools like DataGrip use Driver.getPropertyInfo to display all possible connection parameters to user. Without this it's tricky for users to figure out what parameters are supported for driver.
There is a number of supported parameters in SessionHolder.createSession but they are not returned from Driver.getPropertyInfo

In the code, the query is split using a semicolon (;). Therefore, if a column value contains a semicolon, it could potentially break the query.
This issue was identified in the ticket below.
liquibase/liquibase-cassandra#258

Liquibase inserts strings separated by semicolons in the description into DATABASECHANGELOG table when there are multiple changes within a changeset.
However, it can't process correctly since query is split in the middle.

error messages

Unexpected error running Liquibase: line 1:282 mismatched character '<EOF>' expecting ''' [Failed SQL: (0) INSERT INTO test.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('create-tables', 'test', 'changelog.yml', 1703112425259, 1, '9:abd6cfa9eea355da8b0d16523bcc5115', 'sql; sql', '', 'EXECUTED', NULL, NULL, '4.25.0', '3112424168')]

Caused by: java.sql.SQLTransientException: com.datastax.oss.driver.api.core.servererrors.SyntaxError: line 1:282 mismatched character '<EOF>' expecting '''

cc @maximevw 

Table Structure : testkeyspace.productInfo (productID, productName, timestamp, price) - Partition key column in the above table is “productId”. - table -> testkeyspace.productInfo consists of 10k records

Test Case

Test consists of two Java applications
Application 1 -> Writes data to Cassandra table (testkeyspace.productInfo)
Application 2 -> Reads from Cassandra table (testkeyspace.productInfo) continuously with 5 seconds interval
Partition Key (productID) is unique for all records. Hence, Number of partitions = Number of records in table

How data is read from Cassandra table? (Read Pattern)

1. Initially, "SELECT *" is issued to Cassandra table with the help of Cassandra JDBC
2. Latest Timestamp (lastNotedTimestamp) of the fetched records is noted from the timestamp column of the table
3. The subsequent "SELECT *" are issued with a WHERE condition of timestamp > lastNotedTimestamp
4. Step 2 and 3 are repeated until application kill.

Issue

1. While Step 4 is executed in parallel with the write application, few random records are missing in the ResultSet returned from the JDBC call.
2. While Step 4 is executed after write application has completed its task, records are not missed and the fetching is successful.

Note: Above mentioned issue is applicable even when using a numeric-based column in the WHERE clause of the select query.

It's tested and observed in the single node cluster.

Hello,

the following case does not work with cassandra-jdbc-driver:

// create table atable(id blob primary key)
Connection c = DriverManager.getConnection(DB, null, null);
PreparedStatement s = c.prepareStatement("INSERT INTO atable (id) VALUES (?)");
final byte[] ID1 = new byte[] {
    1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
    1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1
};
s.setObject(1, ID1, Types.VARBINARY);
s.executeUpdate();

throwing the errror:

java.sql.SQLException: Unsupported SQL type: -3
	at com.ing.data.cassandra.jdbc.CassandraPreparedStatement.setObject(CassandraPreparedStatement.java:662)
	at com.ing.data.cassandra.jdbc.CassandraPreparedStatement.setObject(CassandraPreparedStatement.java:538)

I think the issue is in setObject()

In particular, in the block handling Types.BINARYY

As per the JDBC documentation, the method should perform a conversion taking into account multiple cases for the input type. However I agree to take a simpler approach at least to start with, but I see to main issues with the current implementation:

1. it does not handle Types.VARBINARY (therefore the exception); this can be easily fixed handling it as a synonym of BINARY
2. it assumes the object to be an InpuStream while instead it can be any type. Leaving apart handling Ref, Blob, Clob, NClob, Struct, java.net.URL, and Array, it should support at least the case the object is a byte array.

I would propose something like this:

case Types.BINARY:
case Types.VARBINARY:
    byte[] array = null;
    if (x instanceof ByteArrayInputStream) {
        array = new byte[((ByteArrayInputStream) x).available()];
        try {
            ((ByteArrayInputStream) x).read(array);
        } catch (final IOException e) {
            LOG.warn("Exception while setting object of BINARY type.", e);
        }
    } else if (x instanceof byte[]) {
        array = (byte[])x;
    } else {
        throw new SQLException("Unsupported parameter type: " + x.getClass());
    }
    this.boundStatement = this.boundStatement.setByteBuffer(parameterIndex - 1, ByteBuffer.wrap(array));
    break;

I won't be able to provide a PR due to the need of a dockered Cassandra... :(

Cassandra JDBC driver version has not been upgraded since v4.4.0.

See CassandraDriver

java.sql.Wrapper methods not implemented for Cassandra connections. It should be relatively straight-forward to implement these to get access to Cassandra specific methods.

CResultSetMetaData makes an assumption that all table names are CQL identifiers. However, for some metadata resultset, table names may be empty strings. This can cause an assertion error in CResultSetMetaData::isSearchable. Perhaps the flatmap can check for empty table names, and do something different to prevent the assertion error? The error comes from KeyspaceMetadata::getTable → CqlIdentifier::fromCql → Strings::needsDoubleQuotes.

Hi team,
I've developed this project and obtained the cassandra-jdbc-wrapper-4.11.0.jar. With this JAR, I've successfully executed a Java JDBC program. Currently, I'm attempting to establish a connection between Cassandra and JNDI (using WildFly). Could you provide guidance on achieving this use case if it's possible?

Update .gitignore using https://www.toptal.com/developers/gitignore/

Given a simple table like size(id varchar, size bigint), run the following code (with proper connectionstring)

Connection c = DriverManager.getConnection("...", null, null);
Statement s = c.createStatement();
ResultSet rs = s.executeQuery("SELECT \"id\" FROM \"size\" WHERE \"id\" IN ('100')");
System.out.println(rs.findColumn("id"));

Expected behaviour: 1
Current behaviour: 0

As far as I understand from java.sql.ResultSet javadoc, fields in ResultSet are indexed starting from 1, not 0.