I'm using a wide row schema in Cassandra. My table definition is as follows:
CREATE TABLE usertopics (
key text,
topic text,
score counter,
PRIMARY KEY (key, topic)
)
I'm inserting entries using:
UPDATE usertopics SET score = score + ? WHERE key=? AND topic=?
such that if key does not exist it will insert and if it exists it will update.
I'm deleting entries from using:
Delete form usertopics where key in ?
But after deletion when I'm trying to update again, it's not updating. It's not giving any error, but it's not reflecting in db as well.
It's inserting perfectly again when I'm truncating the table. I'm using Datastax java driver for accessing Cassandra. Any suggestions?
From cassandra documentation -
Counter removal is intrinsically limited. For instance, if you issue very quickly the sequence "increment, remove, increment" it is possible for the removal to be lost (if for some reason the remove happens to be the last received messages). Hence, removal of counters is provided for definitive removal only, that is when the deleted counter is not increment afterwards. This holds for row deletion too: if you delete a row of counters, incrementing any counter in that row (that existed before the deletion) will result in an undetermined behavior. Note that if you need to reset a counter, one option (that is unfortunately not concurrent safe) could be to read its value and add -value.
Once deleted, a counter with same key cannot/should not be used. Please use the below links for further info -
https://docs.datastax.com/en/cql/3.1/cql/cql_using/use_counter_t.html https://wiki.apache.org/cassandra/Counters