Unable to select /delete the document with ID like AAA

SQL++
1

Hi ,

We are Using the Couchbase community Edition 5.X ,
We have the the volume of 40M records in the bucket and we wanted delete the records which id starts with AAA.
we could not able to do this with NQ1L query from admin portal.
we have 5 nodes, in which 3 are query + data +index 2 are only data nodes.

the query is like delete from bucket_name where meta().id like ‘AAA%’;

Can you provide any suggestions.

2

Could you please post what the issue is?

CREATE primary index and issue the query.
You can also do this repeatedly
delete from bucket_name where meta().id like ‘AAA%’ LIMIT 10000;

3

I have a primary index on the bucket,
still I was not able to delete the records.
Initially i tried to delete with limit 100 it worked , after that i tried to delete with limit 100000 then query started running for long time and says indexer time out, after that i am not able to select also on the bucket with limit 1.

4

SELECT META().id FROM bucket_name LIMIT 1;
SELECT * FROM bucket_name LIMIT 1;

If none of them works restart couchbase server on the query node that has problem (or kill cbq-engine process, which will start automatically)

5

After 3 days of the issue , I tried to execute this query select * from bucket_name where meta().id like ‘AAA%’ limit 1 it working , then now i tried to delete the records it has deleted around 20k after that same issue query is running for long time , but no records are being deleted.

after this i have executed select meta(),* from system:active_requests; it has given count 80 -100 .
in general max my system will have 2-5 active requests .