[FIXED] Why does a Cassandra query sometimes return one row and sometimes null?

Issue

So I am new to Cassandra but I am experimenting some strange issues:

I run this simple query from a cassandra client:

select * from emps where causality ='bb4148ef-e9ff-4794-9ab2-d0e55a005d59'

And I get one record. Now I have a java repository implementation that is running the same query without any exception or errors and I am getting "null" back, even if I can see that the generate query is correct

2022-07-28T18:55:19.122+0300 DEBUG Executing CQL Statement [select * from emps where causality = ?]

Even more if I remove the where statement everything is working: select * from empswhere limit 1

Here is my repository

@Repository
public interface CassandraUserSpinRepository extends CassandraRepository<Emps, String> {
    // works correctly 
    //@Query("select * from emps limit 1")
    // 
    // returning null even if a record exist in db
    // i check and causality parameter is sended correctly
    @Query("select * from emps where causality = :causality")
    SpinResult findByCausality(@Param("causality") String causality);
}

Has anyone some idea what could be wrong here?

Solution

Assuming that you have more than one replica configured on your keyspace, the most likely issue is that your replicas are out-of-sync. If your app is reading with a consistency level of ONE or LOCAL_ONE, it could be hitting a replica which doesn't have the data.

For replicas to be missing data, the nodes must get overloaded at times and drop mutations. You need to repair the nodes with a rolling nodetool repair -pr, one node at a time so as not to overload your cluster even more.

Additionally, we recommend that use a strong consistency of LOCAL_QUORUM for reads and writes. You can configure the default consistency on the Java driver with:

datastax-java-driver {
  basic.request {
    consistency = LOCAL_QUORUM
  }
}

Just add this to your application.conf or application.properties.

If your cluster is repeatedly getting overloaded, you should consider increasing the capacity by adding more nodes. Cheers!

Answered By - Erick Ramirez
Answer Checked By - Gilberto Lyons (JavaFixing Admin)