Cassandra TTL gets set to 0 on primary key if no TTL is specified on an update, but if it is, the TTL on the primary key does not change

Question

This behavior in Cassandra seems counter-intuitive and I want to know why this is happening, and possibly work around this.

---

Imagine I have a table with three columns: pk, the primary key, a text type, foo, a bigint, and bar, another text.

insert into keyspace.table (pk, foo, bar) values ('first', 1, 'test') using ttl 60;

This creates a row in my table that has a time-to-live of 60 seconds. Looking at it, it looks like this:

  pk  | foo | bar
------------------
first |  1  | test

Now I do:

update keyspace.table using ttl 10 set bar='change' where pk='first';

And then, watching the row, I see it undergo the following changes:

  pk  | foo | bar
--------------------
first |  1  | change
first |  1  | <<null>>  // after 10 seconds
   << deleted >>        // after the initial 60 seconds

All well and good. What I wanted was for bar's time-to-live to change, but nothing else, especially not the primary key. This behavior was expected.

---

However, if my update doesn't have a ttl in it, or it's set to 0:

update keyspace.table set bar='change' where pk='first';

Then I see this behavior over time instead.

  pk  | foo | bar
--------------------
first |  1  | change
first |  0  | change   // after the initial 60 seconds

In other words, the row is never deleted. foo hadn't been changed, so its time-to-live was still in effect and after it passed the value was deleted (set to 0). But pk did have its time-to-live changed. This is totally unexpected.

Why does the primary key's time-to-live change only if I don't specify the time-to-live in the update? And how can I work around this so that the primary key's time-to-live will only change if I explicitly say to do so?

Edit I also found that if I use a time-to-live that's higher than the initial one it also seems to change the time-to-live on the primary key.

update keyspace.table using ttl 70 set bar='change' where pk='first';

  pk  | foo | bar
--------------------
first |  1  | change
first |  0  | change   // after the initial 60 seconds
   << deleted >>       // after the 70 seconds

Witchcraft

Answer 1

The effect that you are experiencing is caused by the storage model used by Cassandra.

In your example, where you have a table that does not have any clustering columns, each row in the table maps to a row in the data store (often called a "Thrift row", because this is the storage model exposed through the Thrift API). Each of the columns in your table that are not part of the primary key (so in your example the foo and the bar columns) is mapped to a column in the Thrift row. In addition to that, an extra column that is not visible in the CQL row is created as a marker that the row exists.

TTL expiration happens on the level of Thrift columns, not CQL columns. When you INSERT a row, all the columns that you insert as well as the special marker for the row itself get the same TTL.

If you UPDATE a row, only the columns that you update get a new TTL. The row marker is not touched.

When running a query with SELECT all rows for which at least one column or the special row marker exists are returned. This means that the column with the highest TTL defines how long a CQL row is visible, unless the marker for the row itself (which is only touched when using an INSERT statement) has a longer TTL.

If you want to ensure that the row's primary key gets updated with the same TTL as the new column values, the workaround is simple: Use the INSERT statement when updating a row. This will have exactly the same effect as using UPDATE, but it will also update the TTL of the row marker.

The only downside of this workaround is that it does not work in combination with lightweight transactions (IF clause in INSERT or UPDATE statements). If you need these in combination with a TTL, you have to use a more complex workaround, but this would be a separate question, I suppose.

If you want to update some columns of a row, but still want the whole row to disappear once the TTL that you specified when it was inserted originally expires, this is not directly supported by Cassandra. The only way would be to find out the TTL left for the row by first querying the TTL of one of the columns and then using this TTL in the UPDATE operation. For example, you could use SELECT TTL(foo) FROM table1 WHERE pk = 'first';. However, this has performance implications because it increases the latency (you have to wait for the result of SELECT before you can run the UPDATE).

As an alternative, you could add a column that you only use as a "row exists" marker and that you only touch during the INSERT and never in an UPDATE. You could then simply ignore rows for which this column is null, but this filtering would need to be implemented on the client side and it will not help if you cannot specifiy a TTL in an UPDATE because the updated columns would never be deleted.

Hemline

If a composite primary key is in use, what changes? – Fourdimensional

The use of a composite primary key should not change anything. As far as the low-level (Thrift) storage model is concerned, a composite primary key is really just a tuple. The mapping on different columns on the CQL-level is just syntactic sugar. – Hemline

And if there's a clustering key? – Fourdimensional

Basically, the same applies even when there is a clustering key. However, some of the claims that I make in my answer are not valid any longer, For example, if there is a clustering key, all CQL rows having the same partition key will actually map to the same Thrift row. If you want to find out how a paticular set of CQL rows and columns map to the underlying data store, it is a good idea to look at the data with "cassandra_cli". This will expose the internal details not visible through the CQL interface. – Hemline

Thanks for your explanation. Could you please elaborate on the complex workaround regarding the lightweight transactions. In order to ensure that the row is deleted after a TTL is set with an UPDATE command, instead of using INSERT for the initial insertion of the data, I used UPDATE. Does this mean that my record has no row marker at all and can this create any issues? – Germicide

The problem with LWTs is that you have to use INSERT when the row does not exist yet and UPDATE when it already exists. When using INSERT or UPDATE without an IF clause, it is not a LWT. Mixing LWTs and non-LWT operations can cause a lot of problems and is therefore not recommended (see docs.datastax.com/en/cassandra/3.x/cassandra/dml/…). – Hemline

Answer 2

After some testing, those are the expected results. TTLs have the granularity of columns.

• When doing an update, if no TTL is specified, the column TTL is set to 0. This operation doesn't affect other column TTLs.
• We cannot update a column value and preserve the old column value TTL in a single cql command.
• A row (or primary/partition key) is deleted when ALL column TTLs are expired. The row will not be deleted if a column has a TTL or 0.

As of today (Cassandra 2.1), Here is how you can update a column value and preserve its TTL:

SELECT TTL(col1) FROM table1 where pk=1;
// read the ttl value fetched.
UPDATE table1 USING TTL <the_ttl_value> set col1='change' where pk=1;

Childs

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. You can also add a bounty to draw more attention to this question once you have enough reputation. – Retrogress

@Retrogress Here, I edited and undeleted my previous answer. Was that the right thing to do? – Childs

This does not answer my question as to why the row gets deleted sometimes and doesn't other times. I appreciate you taking the time to file the issue though, and I am watching it now. – Witchcraft