Hi @marcog, @prasad, @vsr1,
I tried debugging it on our 3 nodes cluster today using cbq, as our code already uses the workaround by setting adhoc=true which solves the problem for the time being. Thus I used CBQ to make a prepared statement.
I connect via ssh to one node of the cluster and started cbq.
/opt/couchbase/bin # ./cbq -u=myuser -p=xxxx
PREPARE folder2 FROM SELECT COUNT(*) from datastoreitems WHERE datastoreid = $ds AND folder = $folder;
{
"requestID": "328a87e5-69b8-4358-92f2-1a194731d1f7",
"signature": "json",
"results": [
{
"encoded_plan": "H4sIAAAAAAAA/7RUQW/TTBT8K6tpD3G1+qR+FyQjDlVxAKm0VhLEIYqSxfviLNi75u26SqnMb0cbp23S9ABB3Ozn8Xhm3njvQbZwmvS8qZRFCkhYVRNSLF2lif+HhGuIVXCM9B4nTzcY0/eWbEGQ+FmsTKWZLNLpPuiiDSvH5kdENWxuTUUl+Uh1ZXzYwCeKSwpIoWmp2iqkWgXlg2MygWoPiZzNLdLzTv4++FU36x50HSU8Z1MrvhsXykLCWE1rpDhp+jEkvtGdb1QRszrQEDN8fNgLhUTrjS2RovQG0cvu54YUitURrM95csWqqqjCX5kfmioQQ6JwVptgXOzGYDBY7Gta/Lcz0ItEvBGn2idCWS1eAved6nH9dZIcOPhgTTCqeseubSChypKpVCGWZorCtTYMzhLMJMqImMfAkE5n3aw7ZArENWmjAv0x3fP9GHuEpn+ynG1AObuvVMReMfm2CvNo1m/QtG4i8FFY97KdB4YY3WF648Ck6s278Ka0KrRMUfDpOVLYtv5CjE4i0Dr+j/koyy9GmdieG2I4uvkoxtlVdjkRlzefrieDs0Qs2dVivxbi8/tslO0Mdd8icXH9dkv2VJfX6H4FAAD//5sFtH2zBAAA",
"name": "folder2",
"operator": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Authorize",
"privileges": {
"List": [
{
"Priv": 1,
"Target": "default:datastoreitems"
},
{
"Priv": 7,
"Target": "default:datastoreitems"
}
]
},
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "PrimaryScan",
"index": "#primary",
"keyspace": "datastoreitems",
"namespace": "default",
"using": "gsi"
},
{
"#operator": "Fetch",
"keyspace": "datastoreitems",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((`datastoreitems`.`datastoreid`) = $ds) and ((`datastoreitems`.`folder`) = $folder))"
},
{
"#operator": "InitialGroup",
"aggregates": [
"count(*)"
],
"group_keys": []
}
]
}
},
{
"#operator": "IntermediateGroup",
"aggregates": [
"count(*)"
],
"group_keys": []
},
{
"#operator": "FinalGroup",
"aggregates": [
"count(*)"
],
"group_keys": []
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "count(*)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}
},
{
"#operator": "Stream"
}
]
},
"signature": {
"$1": "number"
},
"text": "PREPARE folder2 FROM SELECT COUNT(*) from datastoreitems WHERE datastoreid = $ds AND folder = $folder;"
}
],
"status": "success",
"metrics": {
"elapsedTime": "6.107127ms",
"executionTime": "6.065526ms",
"resultCount": 1,
"resultSize": 5312
}
}
cbq> \SET -$ds 'd4d9ff0c-4478-11e7-a06f-901b0ea49fee';
cbq> \SET -$folder 'default';
cbq> EXECUTE folder2;
{
"requestID": "ebd6544e-def5-4767-96df-a1562e676af2",
"signature": null,
"results": [
{
"$1": 0
}
],
"status": "success",
"metrics": {
"elapsedTime": "13.490432629s",
"executionTime": "13.49041341s",
"resultCount": 1,
"resultSize": 31
}
}
cbq> EXECUTE folder2;
{
"requestID": "598979f3-7196-42c6-a4d8-11487ad80610",
"errors": [
{
"code": 4040,
"msg": "No such prepared statement: folder2"
}
],
"status": "fatal",
"metrics": {
"elapsedTime": "379.164µs",
"executionTime": "347.507µs",
"resultCount": 0,
"resultSize": 0,
"errorCount": 1
}
}
cbq> EXECUTE folder2;
{
"requestID": "ae8a856b-0a7e-4088-b576-dd92117cf34d",
"errors": [
{
"code": 4040,
"msg": "No such prepared statement: folder2"
}
],
"status": "fatal",
"metrics": {
"elapsedTime": "412.432µs",
"executionTime": "383.518µs",
"resultCount": 0,
"resultSize": 0,
"errorCount": 1
}
}
cbq> EXECUTE folder2;
{
"requestID": "c4e1b67a-e3cb-4038-a8b4-a3aebe1b35d1",
"errors": [
{
"code": 4040,
"msg": "No such prepared statement: folder2"
}
],
"status": "fatal",
"metrics": {
"elapsedTime": "381.238µs",
"executionTime": "348.205µs",
"resultCount": 0,
"resultSize": 0,
"errorCount": 1
}
}
cbq> EXECUTE folder2;
{
"requestID": "e5fe8253-009f-4f5d-a14d-72784da1d6de",
"errors": [
{
"code": 4040,
"msg": "No such prepared statement: folder2"
}
],
"status": "fatal",
"metrics": {
"elapsedTime": "203.984µs",
"executionTime": "189.999µs",
"resultCount": 0,
"resultSize": 0,
"errorCount": 1
}
}
cbq> EXECUTE folder2;
{
"requestID": "e0d62082-1b96-42cf-a151-d6af3aca8778",
"signature": null,
"results": [
{
"$1": 0
}
],
"status": "success",
"metrics": {
"elapsedTime": "13.475725202s",
"executionTime": "13.475697058s",
"resultCount": 1,
"resultSize": 31
}
}
My observations
- Notice that at the end I execute the Prepared Statement 6x
- 1st time it is successful
- 2nd-4th is error (fatal, “No such prepared statement: folder2”)
- 6th successful again
Not sure why 4 out of 6 times the prepared statement was not found. Maybe that is also the reason what happened on the client which shows a different message, but could have this as the root cause.
SDK Java Error:
Error in query result: [{“msg”:"Unable to decode prepared statement - cause: Unrecognizable prepared statement - cause: JSON unmarshalling error: Keyspace not found keyspace - cause: No bucket named ",“code”:4070}] status: fatal
If I run the same query without Prepared Statement then it works every time.
Our Cluster Setup
- 3 nodes (index, data, query on all nodes)
- 5.0.0. AprilDB Community Edition (build Couchbase Server 5.0.0-2564 (EE))
- just 1 bucket which we use
- Memory Optimized Indexes
Just for sake of completeness the EXPLAIN for the non-prepared statement (which looks similar to me)
EXPLAIN SELECT COUNT(*) from datastoreitems WHERE datastoreid = 'd4d9ff0c-4478-11e7-a06f-901b0ea49fee' AND folder = 'default';
{
"requestID": "a4dd2df6-ccd7-40b4-94ba-060918a86e9f",
"signature": "json",
"results": [
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "PrimaryScan",
"index": "#primary",
"keyspace": "datastoreitems",
"namespace": "default",
"using": "gsi"
},
{
"#operator": "Fetch",
"keyspace": "datastoreitems",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((`datastoreitems`.`datastoreid`) = \"d4d9ff0c-4478-11e7-a06f-901b0ea49fee\") and ((`datastoreitems`.`folder`) = \"default\"))"
},
{
"#operator": "InitialGroup",
"aggregates": [
"count(*)"
],
"group_keys": []
}
]
}
},
{
"#operator": "IntermediateGroup",
"aggregates": [
"count(*)"
],
"group_keys": []
},
{
"#operator": "FinalGroup",
"aggregates": [
"count(*)"
],
"group_keys": []
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "count(*)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "SELECT COUNT(*) from datastoreitems WHERE datastoreid = 'd4d9ff0c-4478-11e7-a06f-901b0ea49fee' AND folder = 'default';"
}
],
"status": "success",
"metrics": {
"elapsedTime": "3.866902ms",
"executionTime": "3.851888ms",
"resultCount": 1,
"resultSize": 2952
}
}
Index is on datastoreid and folder.
CREATE INDEX Index_datastore ON datastoreitems(datastore_id, folder) USING GSI WITH {"num_replica":2}