Bdtjtk

I have a 3 nodes Cassandra cluster(3.7), a keyspace

CREATE KEYSPACE demo WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '2'}  AND durable_writes = true;

a table

CREATE TABLE tradingdate (key text,tradingdate date,PRIMARY KEY (key, tradingdate));

one day when deleting one row like

delete from tradingdate 

where key='tradingDay'and tradingdate='2018-12-31'

then the deleted row become ghost, when the query

select * from tradingdate 

where key='tradingDay'and tradingdate>'2018-12-27' limit 2;



     key        | tradingdate

    ------------+-------------

     tradingDay |  2018-12-28

     tradingDay |  2019-01-02





select * from tradingdate 

where key='tradingDay'and tradingdate<'2019-01-03' 

order by tradingdate desc limit 2;



     key        | tradingdate

    ------------+-------------

     tradingDay |  2019-01-02

     tradingDay |  2018-12-31

So when use order by, the deleted row (tradingDay, 2018-12-31) come back.

I guess I only delete a row on one node, but it still exists on another node. So I execute:

nodetool repair demo tradingdate

on 3 nodes, then the deleted row totally disappears

So I want to know why use order by, I can see the ghost row.

This is some good reading about deletes in Cassandra (and other distributed systems as well):

http://thelastpickle.com/blog/2016/07/27/about-deletes-and-tombstones.html

As well as:

https://docs.datastax.com/en/cassandra/3.0/cassandra/dml/dmlAboutDeletes.html

You will need to run/schedule a routine repair at least once within gc_grace_seconds which defaults to ten days to prevent data from reappearing in your cluster.

Also you should look for dropped messages in case one of your nodes is missing deletes (and other messages):

# nodetool tpstats

Pool Name                    Active   Pending      Completed   Blocked  All time blocked

MutationStage                     0         0      787032744         0                 0

ReadStage                         0         0     1627843193         0                 0

RequestResponseStage              0         0     2257452312         0                 0

ReadRepairStage                   0         0       99910415         0                 0

CounterMutationStage              0         0              0         0                 0

HintedHandoff                     0         0           1582         0                 0

MiscStage                         0         0              0         0                 0

CompactionExecutor                0         0        6649458         0                 0

MemtableReclaimMemory             0         0          17987         0                 0

PendingRangeCalculator            0         0             46         0                 0

GossipStage                       0         0       22766295         0                 0

MigrationStage                    0         0              8         0                 0

MemtablePostFlush                 0         0         127844         0                 0

ValidationExecutor                0         0              0         0                 0

Sampler                           0         0              0         0                 0

MemtableFlushWriter               0         0          17851         0                 0

InternalResponseStage             0         0           8669         0                 0

AntiEntropyStage                  0         0              0         0                 0

CacheCleanupExecutor              0         0              0         0                 0

Native-Transport-Requests         0         0      631966060         0                19



Message type           Dropped

READ                         0

RANGE_SLICE                  0

_TRACE                       0

MUTATION                     0

COUNTER_MUTATION             0

REQUEST_RESPONSE             0

PAGED_RANGE                  0

READ_REPAIR                  0

Dropped messages indicate that there is something wrong.