Cassandra producing inconsistent results

Question

I have a Cassandra cluster with 3 nodes and a replication factor of 3. A colleague of mine moved the data for each of the nodes from one disk to the other.

It is possible that something wrong happened in the process.

When I run the query below:

select min(dttime),max(dttime),sum(odo)/1000.0 from module where dttime >= '2022-08-01 01:00' and dttime <= '2022-08-23 17:00' and imei=358899051143710 ;

I get different results at different times.

I got three different results at 3 different times, e.g

cassandra@cqlsh:gps> select min(dttime),max(dttime),sum(odo)/1000.0 from module where dttime >= '2022-08-01 01:00' and dttime <= '2022-08-23 17:00' and imei=358899051143710 ;

 system.min(dttime) | system.max(dttime) | system.sum(odo) / 1000.0
--------------------+--------------------+--------------------------
               null |               null |                        0


 system.min(dttime)              | system.max(dttime)              | system.sum(odo) / 1000.0
---------------------------------+---------------------------------+--------------------------
 2022-08-01 01:00:13.632000+0000 | 2022-08-01 06:11:13.163000+0000 |                   92.471


 system.min(dttime)              | system.max(dttime)              | system.sum(odo) / 1000.0
---------------------------------+---------------------------------+--------------------------
 2022-08-01 01:00:13.632000+0000 | 2022-08-23 16:54:13.686000+0000 |                 8712.734

A further observation is that if one particular node is down, I always get the last result.

What is likely to be the cause of the inconsistency, and if any remedy exists?

I have tried nodetool repair and scrub, but without success.

Answer 1

moved the data for each of the nodes from one disk to the other.

Was a nodetool refresh run afterward?

if one particular node is down, I always get the last result.

That sounds like there's an inconsistent replica.

I have tried nodetool repair and scrub, but without success.

Not sure which parameters were used for nodetool repair, but that's what needs to happen here. I would run a nodetool repair -pr for that table on all nodes.

The other thing is that you could try using a higher level of consistency when querying. Perhaps running at LOCAL_QUORUM would help with this.

Answer 2

Inconsistent results are due to replicas being out-of-sync. At some point, your cluster was overloaded and couldn't keep up with the writes so some nodes (replicas) were dropping mutations.

It is quite easy to verify if this is the case if you turn TRACING on:

cqlsh> TRACING ON;

and using a consistency level of ALL:

cqlsh> CONSISTENCY ALL;

then re-run your query. You should see activities that indicate a digest mismatch(es) and read-repair(s) kicking in.

As Aaron already pointed out, you can fix the inconsistencies by running a rolling repair on the table, one node at a time:

$ nodetool repair -pr -- ks_name table_name

Avoid overloading the cluster so the nodes don't drop mutations and you won't run into this issue. Otherwise, consider increasing the capacity of your cluster by adding more nodes. Cheers!