XDCR issue on query


I currently have 2 clusters installed on Linux. After I do XDCR from one bucket to another, if I query on the destination bucket, it always returns 0 result even though I can see the number of data on the bucket. I have secondary indexes placed before I did XDCR.


Are you able to see the actual documents in the Documents page of the UI?

Have you configured anything non-default for the XDCR stream?

And are these Community Edition or Enterprise Edition clusters?

Yes I can see the actual documents but I cannot query using where clause.

I just used XDCR on its default settings.

Okay, good that you can see the documents so we know they’re there.

Can you send over the query that you’re using? And can you try creating a primary index and doing just a generic “select * from limit 1;” ?

I’m using Community Edition on both clusters.

That’s exactly my query “SELECT * from bucket where type=‘Person’ limit 10;”

I actually dropped my primary index since my bucket has millions of data on it.

Okay, and can you send over your secondary index definition and a sample document?

Here’s a sample document:
“javaClass”: “com.xc.apps.core.model.sales.Payment”,
“dueDate”: 1570579200000,
“level”: “A”,
“discount”: 0,
“balance”: 0,
“interest”: 0,
“amount”: 0

My indexes:
CREATE INDEX adpt_payment ON sales((distinct (pairs(self)))) WHERE (javaClass = “com.xc.apps.core.model.sales.Payment”)

CREATE INDEX idx_java_class ON sales(javaClass)

If I query SELECT * from sales where javaClass=‘com.xc.apps.core.model.sales.Payment’ limit 10, it doesn’t return anything

Hmm okay, on the face of it all that looks good…did you copy/paste those directly from your cluster and command-line? Keep in mind that even the smallest typo or mis-capitalization in the query will result in it not finding the document.

You can also look at the stats for each of those indexes (go to buckets->statistics and scroll down) to make sure that some items have actually been indexed.

I’m sure this is a simple problem, it’s pretty straightforward functionality we just need to find the missing link.

When you say “doesn’t return anything”…does it return "results": [] or does it give you an error? Are you running from within the SDK or via the command-line?

Wait, I already created the indexes before doing the XDCR. Is it the other way around?

Yes it returns “results”: [ ]

I’m just querying from the Documents page on the web console.

That shouldn’t matter, the indexes will keep themselves up to date either way.

I’m a bit turned around now…if you query through the Documents page in the Web Console, it should say “No Results” if it can’t find anything…but if you query through the Query Editor or the command-line, you should receive "results":[]…can you maybe send a screenshot of what you’re running and what the output is?

Which version of Couchbase Server are you using?

I’m using Community Edition 6.0.0 build 1693 ‧ IPv4

Here is a sample screenshot

The top image I used document ID and it returned the document.
The bottom imaged I used where clause but didn’t returned the document.

Well this is certainly getting interesting :slight_smile: . All that looks reasonable and looks like it should be working.

Can you also try javaClass is not null in the WHERE clause?

Can you try manually adding a new document with the field "javaClass":"com.xc.apps.core.model.sales.Payment" ?

To be fair, I’m testing this out along with you but I’m using the Enterprise Edition 6.5 Beta so I suppose there could be a bug in that version of the Community Edition…I can try with that next but seems like a pretty big thing for us not to have picked up on.

Here you go Perry.

Hmm okay, all very strange. I just tried it out on the same version you’re using and it all worked fine.

We can rule out anything w.r.t. XDCR since you just created a new document, so the issue must be somewhere within that cluster and would seem to be related to the query or indexer.

Can you go over to the Query link and try running the same commands from there? You can try:
select * from sales where javaClass is not null
explain select * from sales where javaClass is not null <- to see the index it’s using
SELECT * from sales USE KEYS ("PAYMENT_1234"); <-to bypass any index scan
select * from system:indexes; <- to make sure your indexes are online (although the query should fail anyway if it can’t use an index)

let’s also confirm that there are items in the index(es), can you take a look at the statistics for those indexes: https://docs.couchbase.com/server/6.0/manage/monitor/ui-monitoring-statistics.html#index_stats ?

The more I look at it the more I think there’s a hidden issue with the indexer.

How many nodes do you have in this cluster?
You can try to manually kill the indexer process, it will be automatically restarted.
If that still doesn’t work, can you try dropping and recreating the indexes?

I’m going to be presenting on a webinar for the next hour or so, my responses will be a bit delayed.

SELECT * from sales USE KEYS (“PAYMENT_1234”); <-- This one returns the document.

I also checked the stats and the items scanned is 0.

Well we can see that the index has 38.5k “total indexed items” which means it has actually captured those items. The “0 items scanned” is showing you the throughput…and right now there are no requests so that makes sense.

Let’s also confirm that the query is using the right index:
explain select * from sales where javaClass is not null

And you can also try forcing it to:
select * from sales use index (idx_java_class) where javaClass is not null;

Hmm, I have more than 10 million documents with the key javaClass. Shouldn’t the indexed items also show more than 10 million?

I’ll just have to wait for the indexes to warm up, I followed your advice and killed the indexer. :slight_smile:

Yes…I would expect to see more in there if that’s how many you have. So that may also point to an issue with the indexer. We may need to take a look deeper in the logs.

If this is just for your development at the moment, you could also try spinning up an Enterprise Edition cluster with the 6.5 beta which will give us a lot more insight and error handling over 6.0…