Cassandra select query fails while executing filter in between timestamp

Question

I am trying to execute the query :

select key,date,time_stamp,value from archive_all where time_stamp > '2016-11-14 19:00:00+0000' and time_Stamp <= '2016-11-14 20:00:00+0000' allow filtering

But it fails saying error given below:

ReadFailure: Error from server: code=1300 [Replica(s) failed to execute read] message="Operation failed - received 0 responses and 1 failures" info={'failures': 1, 'received_responses': 0, 'required_responses': 1, 'consistency': 'ONE'}

Column time_stamp is type of timestamp.

Please help me to resolve this problem.

Answer 1

The error says that (practically) your query timed out. You are asking Cassandra to scan all your dataset, and this is taking a lot of time.

The way you usually solve these problems is by getting rid of the ALLOW FILTERING in the query. That is, getting rid of the query, completely.

In other terms, you should rethink your data model.

Answer 2

You must create your data model coherent with your queries in order to get rid of the ALLOW FILTERING option.

By this, your select queries will perform faster.

Please see:

Basic Rules of Cassandra Data Modeling

Answer 3

Could you paste the table schema ? In order to be able to use ">" and "<" operators on a column, you have to make sure it is a clustering column. (see documentation)

Answer 4

I was facing the same error. I have tried many things to overcome this. First of all, it increased the value of tombstone_failure_threshold.

And based on the solution here, I set GC_GRACE_SECONDS to 0. Remember this is only a viable solution on a single node Cassandra. But these did not solve my problem.

I have browsed and read many resources and ask for a timeout as it says in the above answer.

I increased the value of the read_request_timeout_in_ms parameter in my cassandra.yaml file.

Then I was able to run all my queries without errors. I wanted to add this answer, albeit late, as I thought it might help someone.