Inconsistent query results rows returned

Inconsistent query results. Below sql returns 3 or 8 records randomly. We have the same index user_data_quests, user_data_quests2, user_data_quests3 on all 3 nodes and all return different results sometimes ( 3 or 8 matches ). We get the same results using CBQ or the query Editor. These records are not being updated during the query.

OS: x86_64-unknown-linux-gnu
Version: Enterprise Edition 5.0.0 build 3519

  • Query 8 rows returned *
    SELECT
    u.quest_id,
    u.progress,
    u.status
    FROM
    user_data AS u
    WHERE
    u.user_uuid = “7f793ff4-3156-4f66-a52e-7bb6b01f92de”
    AND
    u.quest_id IS NOT NULL;

success | elapsed: 25.10ms | execution: 25.08ms | count: 8 | size: 9474

  • Same Query 3 rows returned *
    SELECT
    u.quest_id,
    u.progress,
    u.status
    FROM
    user_data AS u
    WHERE
    u.user_uuid = “7f793ff4-3156-4f66-a52e-7bb6b01f92de”
    AND
    u.quest_id IS NOT NULL;

success | elapsed: 39.66ms | execution: 39.62ms | count: 3 | size: 3148

EXPLAIN PLAN
{
“plan”: {
#operator”: “Sequence”,
“~children”: [
{
#operator”: “IndexScan2”,
“index”: “user_data_quests”,
“index_id”: “a12234e2541de0”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “user_data”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““7f793ff4-3156-4f66-a52e-7bb6b01f92de””,
“inclusion”: 3,
“low”: ““7f793ff4-3156-4f66-a52e-7bb6b01f92de””
}
]
}
],
“using”: “gsi”
},
{
#operator”: “Fetch”,
“as”: “u”,
“keyspace”: “user_data”,
“namespace”: “default”
},
{
#operator”: “Parallel”,
“~child”: {
#operator”: “Sequence”,
“~children”: [
{
#operator”: “Filter”,
“condition”: “(((u.user_uuid) = “7f793ff4-3156-4f66-a52e-7bb6b01f92de”) and ((u.quest_id) is not null))”
},
{
#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “(u.quest_id)”
},
{
“expr”: “(u.progress)”
},
{
“expr”: “(u.status)”
}
]
},
{
#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “SELECT \nu.quest_id,\nu.progress,\nu.status\nFROM \nuser_data AS u use index ( user_data_quests )\nWHERE \nu.user_uuid = “7f793ff4-3156-4f66-a52e-7bb6b01f92de”\nAND \nu.quest_id IS NOT NULL;”
}

  1. Enable profiling described here https://blog.couchbase.com/optimize-n1ql-performance-using-request-profiling/ and see which operator has issue by comparing ItemsIn/ItemsOut

  2. If IndexScan is giving 3 try with USE INDEX and see which index has issue. Try drop and create that index

Trying to drop the index on one node failed. Restarted couch on the node and the index then dropped as sql was in the queue. Recreated the index and works fine now.

A bigger question is how would we know that something was wrong if we didn’t catch the rows returned error? Seems the node was not healthy.

Thanks
Mark

Check any errors or skipped documents in indexer.log cc @deepkaran.salooja

@mark.hubbard, what is the index storage mode being used?

Working now. Heard back from the IT group that a bucket was full on one node.

Thanks
Mark