Hi,
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.
Hi!
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 
. 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.
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.
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âŚ
