N1QL Async return empty rows alternatively

SQL++
1

I am using java sdk 2.4.2 and Couchbase 4.5.1 Enterprise edition.
Below is the code snippet.
N1qlParams params = N1qlParams.build().adhoc(false);
ParameterizedN1qlQuery query = N1qlQuery.parameterized(stmt, placeholderValues, params);
List list =bucket.async().query(query)
.flatMap(AsyncN1qlQueryResult::rows)
.map(result -> result.value().toMap())
.toList()
.timeout(30, TimeUnit.SECONDS)
.toBlocking()
.single();

The code was working fine till we created HA index.
After creating HA Index, we alternatively started getting empty response. Exactly every alternate request returns empty result.
If I change adhoc=true then everything works fine but performance degrades.
Please let me know how to fix this issue.

2

@hemant_dhakate that sounds like a server side concern to me - bringing in @keshav_m and @geraldss and moving it into the N1QL section.

3

Hi @hemant_dhakate, can you try this via cbq shell, and then post the full response when you get empty results. Also post the full CREATE INDEX statements for both indexes.

4

@geraldss
From command line cbq I never get empty response. I get empty when I use Java SDK. Exactly alternate request gets the empty response.
In cluster of 5 nodes, 2 nodes are query and index nodes.

5

Can you run the cbq shell from a script, run it a few hundred times in a loop, and verify that you never see the problem. If you confirm that it is not a cbq shell, then @daschl and @ingenthr can help with client-side issues. Sorry for the ping pong. We’re just trying to narrow down the issue.

6

This sounds similar to what’s being actively discussed in MB-22273. The difference there is that issue was identified where there were problems when a statement spans indexes, not a situation where there are multiple indexes that satisfy the query. The root cause issue could be the same though. Note the fix there is in the 5.0 release of Couchbase Server, for which a developer build is available now.

One thing that may speed identification of the root cause is capturing the query traffic on port 8093 from the client’s perspective as it prepares then executes the statement across the nodes on the cluster. A probe in the middle like that should make it easier to narrow down problem determination. If you’re able to post that in a place we can get to it, that’d be useful.

For instance, with tcpdump, something like this should capture what’s needed to inspect:

shell> tcpdump -C 500 -W 10 -w /path/to/save/file/(filename).pcap -s 0 port 8093
1 Like
7

Hi @hemant_dhakate - could we have a look at the actual statement as well as , as Gerald says, the relevant CREATE INDEX statements?

Thanks,
Marco

8

Hi @marcog , I worked with the product support team and got this issue resolved. Just added System.setProperty(N1qlQueryExecutor.ENCODED_PLAN_ENABLED_PROPERTY,“false”); to my code to make it work. Thanks for your help and support.

1 Like
9

Hi @hemant_dhakate - that’s fine.
Seeing the N1QL and the indexes would have allowed me to identify a possible defect, but glad that you had a workaround.

HTH,
Marco