Sync Gateway - [4040] No such prepared statement: 73234663-65e9-52b4-a2d2-e6a568375999

2020-08-03T07:50:48.960Z [WRN] Error when querying index using statement: [SELECT META(`my-db`).id FROM `my-db` USE INDEX(sg_syncDocs_1) WHERE META(`my-db`).id LIKE '\\_sync:%' AND META(`my-db`).id LIKE '\\_sync:session:%' AND username = $userName] parameters: [map[userName:29]] error:[4040] No such prepared statement: 73234663-65e9-52b4-a2d2-e6a568375999 -- base.(*CouchbaseBucketGoCB).Query() at bucket_n1ql.go:76
2020-08-03T07:50:48.961Z [ERR] c:#9904 #9904: [4040] No such prepared statement: 73234663-65e9-52b4-a2d2-e6a568375999 -- rest.(*handler).writeError() at handler.go:724
2020-08-03T07:50:48.961Z [INF] HTTP: #9904:     --> 500 Internal error: [4040] No such prepared statement: 73234663-65e9-52b4-a2d2-e6a568375999  (86.0 ms)

This error occurs periodically upon different admin api calls (PUT /_user, POST /_session, GET /<doc_Id>, PUT /<doc_id>). Usually all calls are successful, but sometimes (one in 10 calls) it fails.
What is the issue here?

Couchbase Cluster - 3-node cluster deployed on Kubernetes, version - community-6.5.1
Sync Gateway - deployed to Kuberenets, version - 2.7.3-community
Sync Gateway has created 5 indexes on Couchbase Server: sg_access_1, sg_allDocs_1, sg_channels_1, sg_roleAccess_1, sg_syncDocs_1.

@Ivanka_Bashmat, do you delete bucket and recreate bucket with same buckets? If yes, you need to add logic to wait until all indexes cleaned after deleting buckets. This is needed if your couchbase server is 6.5.1 and up.
If you create new buckets everytime, then you should not have any problem

Thanks for response!

I am not deleting buckets at all. I have created bucket once and now just creating users, docs and sessions.

There are known issues around prepared statements in 6.5:

You can try the server-side workarounds listed in the docs above.
The client-side workaround will be in future versions of Sync Gateway.

I have updated configuration on my cluster:
Query Settings.Prepared Limit - 66560 (used to be 16384)
Query Settings.N1QL Feature Controller - 8 (used to be 12)
Didn’t help

I am not upgrading my cluster from prev version, all nodes are up and running and they all have query service installed on them. Should I restart nodes for settings to apply?

I tried with those settings restarting nodes, not restarting, it does not fix the problem for me

Hi @Ivanka_Bashmat, did you get an answer to this request? Did you resolve the issue?

We’re experiencing the same issue with an upgrade of couchbase community from 6.0 to 6.6 with sync gateway 2.5.

Kind Regards,

Hi @morne, not really - any of fixes described in document didn’t help me.

I run test on community and enterprise versions of couchbase server (with identical configuration) and this error does not appear in enterprise version. So i guess the ‘fix’ for this problem is to upgrade to enterprise version…

Hi @Ivanka_Bashmat,

This is probably more for the benefit of anyone else that finds this post in search of a resolution to the issue.

It turns out that this issue has fixed in version 2.7.4 of Sync Gateway. I can confirm that upgrading sync gateway to 2.7.4 results in this error no longer occurring with couchbase community edition version 6.6.0 (after originally upgrading from 6.0.0 caused the error to start occurring)

Kind Regards,