Cassandra - can't delete row

Question

I would like to delete one specific row from Casandra table but I can't. I able to delete any others from table except this. I put fallowing delete query but nothing happen:

cqlsh> delete from sales.tbl where orderid=999999 and orderdate='2019/01/01';
cqlsh>
cqlsh> select * from sales.tbl where orderid=999999 and orderdate='2019/01/01';

 orderid | orderdate  | country | itemtype | orderpriority | region        | saleschannel | shipdate   | totalcost | totalprofit | totalrevenue | unitcost | unitprice | unitssold
---------+------------+---------+----------+---------------+---------------+--------------+------------+-----------+-------------+--------------+----------+-----------+-----------
  999999 | 2019/01/01 |  Canada |    Stuff |             N | North America |      Offline | 2019/01/02 |       100 |           0 |          100 |        0 |         1 |         1

(1 rows)
cqlsh>

Here is shema of this table:

    CREATE TABLE sales.tbl1 (
        orderid bigint,
        orderdate text,
        country text,
        itemtype text,
        orderpriority text,
        region text,
        saleschannel text,
        shipdate text,
        totalcost float,
        totalprofit float,
        totalrevenue float,
        unitcost float,
        unitprice float,
        unitssold int,
        PRIMARY KEY (orderid, orderdate) ) WITH CLUSTERING ORDER BY (orderdate ASC)
        AND bloom_filter_fp_chance = 0.01
        AND caching = {'keys': 'ALL', 'rows_per_partition': 'ALL'}
        AND comment = ''
        AND compaction = {'class': 'SizeTieredCompactionStrategy'}
        AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'}
        AND crc_check_chance = 1.0
        AND dclocal_read_repair_chance = 0.1
        AND default_time_to_live = 0
        AND gc_grace_seconds = 1
        AND max_index_interval = 2048
        AND memtable_flush_period_in_ms = 0
        AND min_index_interval = 128
        AND read_repair_chance = 0.0
        AND speculative_retry = '99.0PERCENTILE';

any suggestion?

Answer 1

This bizarre situation can happen if the row was created with a timetamp far in the future. In Cassandra and Scylla, clients may specify a timestamp for each write, and the latest timestamp wins - regardless of the real-world time ordering of the updates.

For example consider that one client writes a row with timestamp 1000, and sometime later another client sends a deletion at timestamp 900. That deletion will not delete anything - the write is considered to have happened after the deletion, so the deletion is simply ignored.

It is possible that this is exactly what happened to you: A client with a misconfigured clock used this clock and created a row with a timestamp far in the future. When you now try delete from sales.tbl where orderid=999999 and orderdate='2019/01/01'; the current time is used as this deletion's timestamp, and it is older than that future timestamp, so the deletion is just ignored.

To check if this is the case, try

select writetime(region) from sales.tbl where orderid=999999 and orderdate='2019/01/01';

This will show the writetime (i.e., timestamp) of the "region" column (for example) in your item. This time is in microseconds since the UNIX epoch (midnight GMT, Jan 1st, 1970). If it's in the future, then I correctly guessed your problem. If this is the case, then to really delete this row, you will then need to do something like

delete from sales.tbl using timestamp 111111111 where orderid=999999 and orderdate='2019/01/01';

Where the timestamp "111111111" is a number (at least) one higher than the timestamp that the select showed you.