Cassandra: Insert with older timestamp

Question

(Cassandra 2.0.9, using CQL)

I've accidentally updated a row in a table which was managing its own timestamp (100 * a specific sequence number). Now, because my timestamp is the current time, none of the updates are working. I understand why this is, but I'm trying to recover from it. I'm fortunate that I can delete these rows.

I've set gc_grace_seconds to 0 and run delete from table where key=primarykey to remove the rows. After, I've used nodetool flush and nodetool compact on every node to cause the deletion to go through and get the resulting tombstones compacted and erased. I've then bumped gc_grace_seconds back up to 10 days and tried to insert into a row with the same key but using timestamp 1 .

This doesn't work. Just wondering if anyone has done a similar mistake and worked around it?

Answer 1

I thought I would give this exercise a try.

aploetz@cqlsh:presentation> SELECT * FROm bladerunners WHERE id='B26354';
 id     | data                | name         | ts                       | type
--------+---------------------+--------------+--------------------------+--------------
 B26354 | Filed and monitored | Rick Deckard | 2015-02-16 12:00:03-0600 | Blade Runner

(1 rows)

Here is a look at how the data is stored, using the cassandra-cli:

[default@presentation] get bladerunners[B26354];
=> (name=, value=, timestamp=1427744637894310)
=> (name=data, value=46696c656420616e64206d6f6e69746f7265642e, timestamp=1427744637894310)
=> (name=name, value=5269636b204465636b617264, timestamp=1427744637894310)
=> (name=ts, value=0000014b938c09a2, timestamp=1427744637894310)
=> (name=type, value=426c6164652052756e6e6572, timestamp=1427744637894310)
Returned 5 results.
Elapsed time: 7.67 msec(s).

I will now delete the data column for this row, generating a tombstone:

DELETE data FROM bladerunners WHERE id='B26354';

When I SELECT with tracing on I can see that the column shows "null" and I have a tombstone out there.

aploetz@cqlsh:presentation> SELECT * FROM bladerunners WHERe id='B26354';

 id     | data | name         | ts                       | type
--------+------+--------------+--------------------------+--------------
 B26354 | null | Rick Deckard | 2015-02-16 12:00:03-0600 | Blade Runner

...

Read 1 live and 1 tombstoned cells [SharedPool-Worker-2] | 2015-06-10 08:42:25.858000 | 192.168.23.129 |           2173

So I will set the bladerunners table's gc_grace_seconds to zero:

ALTER TABLE bladerunners WITH gc_grace_seconds=0;

From the (Linux) command line, I will flush and compact my presentation keyspace:

aploetz@dockingBay94:/local/dsc-cassandra-2.1.4$ bin/nodetool flush
aploetz@dockingBay94:/local/dsc-cassandra-2.1.4$ bin/nodetool compact presentation

When I SELECT with tracing on, I can see that the data column is still "null," but now the tombstone is gone.

I will now re-INSERT the data column with a timestamp of 1:

INSERT INTO bladerunners (id, data) VALUES ('B26354','Filed and monitored') USING TIMESTAMP 1;

When querying with the cassandra-cli, this is now what I see:

[default@presentation] get bladerunners[B26354];
=> (name=, value=, timestamp=1427744637894310)
=> (name=data, value=46696c656420616e64206d6f6e69746f726564, timestamp=1)
=> (name=name, value=5269636b204465636b617264, timestamp=1427744637894310)
=> (name=ts, value=0000014b938c09a2, timestamp=1427744637894310)
=> (name=type, value=426c6164652052756e6e6572, timestamp=1427744637894310)
Returned 5 results.
Elapsed time: 4.7 msec(s).

Note that the data column now has a timestamp of 1.

Try running your query with tracing on and see if your tombstones are really gone. Also, check your table via the cassandra-cli to see how the timestamps are coming through. Let me know if you need clarification on any of these steps.

NOTE: I was just showing the flush/compact as part of the example or exercise. I feel compelled to mention that DataStax recommends that users avoid manually running nodetool compact if at all possible.

Answer 2

BryceAtNetwork23's answer is likely the "most correct", it does come with a caveat that you must run nodetool flush and nodetool compact on every node (or so it seems from my testing of 3 node Cassandra cluster). This can take a prohibitively long time.

As another solution (for those visiting here in the future), you can acquire the rows you ultimately want to delete.

cqlsh> select id from example_table where some_field = -1 allow filtering;

Then dump that to a file used to delete the rows in question.

cat cassandra-output | sort | uniq | grep '^ ' | grep -v id | gawk '{ print $1 }' >just-ids.txt
cat just-ids.txt | gawk '{ print "delete from example_table where id='\''"$1"'\'' and some_field = -1;" } >remove.cql
cqlsh ... -f remove.cql

I found Cassandra Dump which I then use to dump the remaining data. After that I can drop and recreate the table (and indexes if needed), then reload the data.

Lastly, I can insert rows I had, but with new timestamps:

cat just-ids.txt | gawk '{ print "insert into example_table (id,some_field) values('\''"$1"'\'', -1) using timestamp 0;" }' >repair.cql
cqlsh ... -f repair.cql

If you're doing this repeatedly you can of course just drop the table and reload it with the post-repair data. It's a faster solution when you're repeatedly reloading than doing flush/compact.