Cassandra database Excepion when performing insert from quarkus application

Question

In our Project we are using Quarkus Version 2.8.0.FINAL and Datastax Version 1.1.1 accessing cassandra database Version 3.11

Wa are getting the following Exception when we perform an insert via Java.

com.datastax.oss.driver.api.core.servererrors.InvalidQueryException: PRIMARY KEY column "col_4" cannot be restricted as preceding column "col_3" is not restricted

We do not understand why that happens since the following statement executed via SQLTools from VSCode succeeds without any problem

INSERT INTO my_table  (
    col_1,
    col_2,
    col_3,
    col_4,
    col_5,
    col_6,
    col_7
  )
VALUES (
    'Some Value',
    '5ea20642-e9e7-44bd-b4f0-97b84ae97f2c',
    '1ed91e32-a7ba-4b5f-b4dd-9cfe3e522914',
    'Another Value',
    '2010-01-01',
    '24afc036-21e8-41c6-9f7a-1994fba71223',
    'Just one mor value'
  );

Our Table is as follows

CREATE TABLE IF NOT EXISTS my_table (
    col_1 text,
    col_2 text,
    col_3 text,
    col_4 text,
    col_5 text,
    col_6 text,
    col_7 date,
    PRIMARY KEY(col_1, col_2, col_3, col_4)
);

The Entity

@Entity
@CqlName("my_entity")
public class MyEntity {

  @PartitionKey
  private String col_1;
  @ClusterinColumn
  private String col_2;
  @ClusterinColumn(1)
  private String col_3;
  @ClusterinColumn(2)
  private String col_4;
  
  private String col_5;
  private String col_6;
  private LocalDate col_1;

}

The Dao

@Dao
public interface MyEntityDao {

  @Insert
  void insert (MyEntity myEntity)

}

Answer 1

The error you got is related to the Cassandra Driver object mapping, creating under the hood a CQL Statement that violates some constraints of your table. A mandatory attribute was probably not provided.

It happens because of a weak design and probably some misunderstanding of the Cassandra data modeling methodology. Let's improve this ! (working project on github at the end).

Comments about the table:

The creation table statement your provided is equivalent to

CREATE TABLE IF NOT EXISTS my_table (
   col_1 text,
   col_2 text,
   col_3 text,
   col_4 text,
   col_5 text,
   col_6 text,
   col_7 date,
PRIMARY KEY((col_1), col_2, col_3, col_4)
WITH CLUSTERING ORDER BY (col_2 DESC, col_3 DESC, col_4 DESC);

• col_1 is called your partition key. It does not ensure the unicity of the record and is absolutely mandatory in every request WHERE CLAUSE against the table. When double parenthesis are omited the first column is considered the partition key.

• Columns from col_2 to col_4 are called clustering columns and are used to complete the primary key to ensuring unicity and mark columns allowed in the clause WHERE. The order is important in the declaration. eg. If you want to filter on col_4 you need to provide col_1 (the partition key) but also col_2 et col_3

To know more about the methodology take the free course DS220 on https://academy.datastax.com

Comments about the entity:

Here is an updated version you got a few mistakes:

• The table name is my_table

• You provided the col_1 twice

• You forgot an offset in the first ClusteringColumn(0)

  @Entity
  @CqlName("my_table")
  public class MyEntity {
  
    @PartitionKey
    @CqlName("col_1")
    private String col1;
  
    @ClusteringColumn(0)
    @CqlName("col_2")
    private String col2;
  
    @ClusteringColumn(1)
    @CqlName("col_3")
    private String col3;
  
    @ClusteringColumn(2)
    @CqlName("col_4")
    private String col4;
  
    @CqlName("col_5")
    private String col5;
  
    @CqlName("col_6")
    private String col6;
  
    @CqlName("col_7")
    private LocalDate col7;
  
    // Constructors
    // Getters and Setters
  }
  

Comments about the Dao:

  @Dao
  @DefaultNullSavingStrategy(NullSavingStrategy.SET_TO_NULL)
  public interface MyEntityDao {

    @Delete(entityClass = MyEntity.class)
    void deleteByCol1(String col1);

    @Select
    PagingIterable<MyEntity> findByPkCol1(String col1);

    @Select
    Optional<MyEntity> findById(String col1, String col2, String col3, String col4);

    @Insert
    void save(MyEntity product);    

  }

I noticed you are using a fairly new Quarkus version but an old version of Cassandra. If you are starting a project jump to the 4.0 you will have. free perfromance boost.

Here I provide you the working Quarkus Application with your database schema and your entity => https://github.com/clun/quarkus_cassandra

Now, please NEVER, EVER use ALLOW FILTERING. It will do a full scan of your cluster and still be slow and then will return timeout (while working in dev). If you need multiple queries on the same data...duplicate the data in multiple tables, please.

Answer 2

So, I figured it out. Quarkus or DataStax in this case is really ugly

I had a second Method in my Dao

@Select (custimWhereClause = "col2 = :col_2_value and col4 = :col_4_value")
PagingIterable<MyEntity> findByCriteria(String col_2_value, String col_4_value)

I temporarily added allowFiltering=true to the Annotation and now it worked