Incorrect N1QL results?

.NET SDK
, , ,
1

I’m getting incorrect N1QL results when using the .net sdk. It seems like the data that is returned is out of date.

I have 40 documents that have “type”: “TelemetryObject” and “parent”: “Event::1530” and my N1QL query looks like this:

{<url>:8093/query[{"statement":"SELECT * FROM `bar-data` WHERE type = $type AND parent = $parentEvent","timeout":"75000ms","$type":"TelemetryObject","$parentEvent":"Event::1530","client_context_id":"5::9"}]}

I have gone through all 40 and checked but this query only returns 21 results. I’m not sure where to start debugging this. I have two indices, a primary index on the bucket and a secondary index on ````bar-data(type`)``` using GSI. I’ve tried adding another index on parent.

I’ve had this issue in the past and it seemed to just go away eventually. Is the indexer just getting stuck?

2

@molexmat

You might try running your query with RequestPlus consistency. This ensures that the index is up-to-date with the data as of the time you execute the query. It has some definite performance penalties, but it could be a good test.

Brant

3

Thanks for that Brant. I tried setting RequestPlus like this:

query.ScanConsistency(ScanConsistency.RequestPlus);

but it took around a minute and then timed out. I guess then that the indexer is stuck? Is there any way I can find out the status of the indexer?

4

select * from system:indexes

returns status for indexes. you can also look at the web console under the index tab to see status of indexes.
Could you share the explain output for the query and the definitions of indexes? pls include the version of the product you are sugin as well.
thanks

5

I ran that statement:

cbq> SELECT * FROM system:indexes;
{
    "requestID": "c9cc8401-ab01-47e5-ba5f-9b403b038849",
    "signature": {
        "*": "*"
    },
    "results": [
        {
            "indexes": {
                "datastore_id": "http://127.0.0.1:8091",
                "id": "d86d4018f9ee380",
                "index_key": [
                    "`type`"
                ],
                "keyspace_id": "bar-data",
                "name": "def_bar_data_type",
                "namespace_id": "default",
                "state": "online",
                "using": "gsi"
            }
        },
        {
            "indexes": {
                "datastore_id": "http://127.0.0.1:8091",
                "id": "3ef11a37826b9a0f",
                "index_key": [
                    "`parent`"
                ],
                "keyspace_id": "bar-data",
                "name": "def_bar_data_parent",
                "namespace_id": "default",
                "state": "pending",
                "using": "gsi"
            }
        },
        {
            "indexes": {
                "datastore_id": "http://127.0.0.1:8091",
                "id": "de0f232be4a6c659",
                "index_key": [],
                "is_primary": true,
                "keyspace_id": "bar-data",
                "name": "bar-data-index",
                "namespace_id": "default",
                "state": "online",
                "using": "gsi"
            }
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "110.375358ms",
        "executionTime": "110.307525ms",
    "resultCount": 3,
    "resultSize": 1266
}

}

I created def_bar_data_parent as an attempt to speed things up.

The query I am running should return around 40 documents with the type: TelemetryObject and parentEvent: Event::1530.

I am running Version: 4.0.0-4051 Community Edition (build-4051) for the cluster and version 2.2.6 of the official Couchbase .NET SDK.

6

For anyone else that is having this issue, I think the indexer was just stuck. To find this out, I went to the stats page of the bucket that I was having trouble with (by clicking on it’s name under Buckets in the web interface) and expanded the sections that show stats about the indexes.

I found in there something my index (def_bar_data_type) had stopped at around 1m items remaining. I went to the indexes page, ssh’d into the machine that held this index and restarted the couchbase-server service (sudo service couchbase-server restart). This seemed to sort the problem, but I wish I’d done some more diagnosis to find out why it was happening.