question

msnaidu avatar image
msnaidu asked lukacs.gabriel.lg_161957 commented

Clustering key columns must exactly match columns in CLUSTERING ORDER BY directive

I am trying to create cluster column using the create query

CREATE TABLE videos_by_tag (
       tag text,
       video_id uuid,
       added_date timestamp,
       title text,
       PRIMARY KEY ((tag), added_date, video_id)
) WITH CLUSTERING ORDER BY(added_date DESC);

It's showing the following error. Am i missing something in the create table ?

InvalidRequest: Error from server: code=2200 [Invalid query] message="Clustering key columns must exactly match columns in CLUSTERING ORDER BY directive"

I am using the following version of cassandra on mac os

[cqlsh 5.0.1 | Cassandra 4.0.0.680 | CQL spec 3.4.5


cassandra
10 |1000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered lukacs.gabriel.lg_161957 commented

@sanyasinaidu.malla_37931 The issue is that you need to specify all the clustering columns in the CLUSTERING ORDER BY directive. In your case, you only specified the added_date column:

WITH CLUSTERING ORDER BY(added_date DESC);

Given that you have 2 clustering columns in your table:

    PRIMARY KEY ((tag), added_date, video_id)

you need to explicitly include those 2 clustering columns in the directive like this:

WITH CLUSTERING ORDER BY(added_date DESC, video_id ASC);

even though it is already implied that video_id will be in ascending order.

This was introduced in C* 4.0-alpha1 (CASSANDRA-13426) but has been reverted back in C* 4.0-alpha4 (CASSANDRA-15271) which will be included in a future release of DSE 6.8. Cheers!

2 comments Share
10 |1000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

kacert_160175 avatar image kacert_160175 commented ·

Are there any detail on the version on DSE that fixes this issue?

0 Likes 0 ·
lukacs.gabriel.lg_161957 avatar image lukacs.gabriel.lg_161957 commented ·

What is the recommendation how to fix already migrated schemas when we moving from dse 6.7 (where not all clustering columns were explicitly included in CLUSTERING ORDER BY directive) to dse 6.8.9 (where all clustering columns have to be explicitly mentioned in CLUSTERING ORDER BY directive)? Should be those schemas in DB corrected after upgrade to dse 6.8?

0 Likes 0 ·