Syncgateway Error [4040] No such prepared statement

Hi,

We 're testing the new couchbase server 6.5 community with Syncgateway 2.6.1 and we have many errors at login

2020-03-10T14:57:06.336Z [INF] HTTP: #1464:     --> 401 Login required  (8.4 ms)
2020-03-10T14:57:06.377Z [INF] HTTP:  #1465: GET /db/_blipsync (as d255e05d0fa0b4ee607d89bdc3b827b335e1ea4b7bdbc9283559922129041e23)
2020-03-10T14:57:06.377Z [INF] HTTP+: #1465:     --> 101 [8e47274] Upgraded to BLIP+WebSocket protocol (as d255e05d0fa0b4ee607d89bdc3b827b335e1ea4b7bdbc9283559922129041e23)  (0.0 ms)
2020-03-10T14:57:06.516Z [INF] Sync: c:[8e47274] Sending changes since 0
2020-03-10T14:57:06.543Z [INF] Cache: c:[8e47274] GetCachedChanges("!", 0) --> 50 changes valid from #1932204
2020-03-10T14:57:06.543Z [INF] Cache:   Querying 'channels' for "!" (start=#1, end=#1932204, limit=0)
2020-03-10T14:57:06.544Z [WRN] Error when querying index using statement: [SELECT [op.name, LEAST(meta(`fleet-prod`).xattrs._sync.sequence, op.val.seq),IFMISSING(op.val.rev,null),IFMISSING(op.val.del,null)][1] AS seq, [op.name, LEAST(meta(`fleet-prod`).xattrs._sync.sequence, op.val.seq),IFMISSING(op.val.rev,null),IFMISSING(op.val.del,null)][2] AS rRev, [op.name, LEAST(meta(`fleet-prod`).xattrs._sync.sequence, op.val.seq),IFMISSING(op.val.rev,null),IFMISSING(op.val.del,null)][3] AS rDel, meta(`fleet-prod`).xattrs._sync.rev AS rev, meta(`fleet-prod`).xattrs._sync.flags AS flags, META(`fleet-prod`).id AS id FROM `fleet-prod` UNNEST OBJECT_PAIRS(meta(`fleet-prod`).xattrs._sync.channels) AS op WHERE [op.name, LEAST(meta(`fleet-prod`).xattrs._sync.sequence, op.val.seq),IFMISSING(op.val.rev,null),IFMISSING(op.val.del,null)]  BETWEEN  [$channelName, $startSeq] AND [$channelName, $endSeq]] parameters: [map[channelName:! startSeq:1 endSeq:1932205]] error:[4040] No such prepared statement: 909e1bd4-70bf-59da-8932-1d9180cc7213 -- base.(*CouchbaseBucketGoCB).Query() at bucket_n1ql.go:77
2020-03-10T14:57:06.545Z [WRN] c:[8e47274] MultiChangesFeed got error reading changes feed "!": [4040] No such prepared statement: 909e1bd4-70bf-59da-8932-1d9180cc7213 -- db.(*Database).SimpleMultiChangesFeed.func1() at changes.go:545

Thanks in advance.

I haven’t seen that issue, but it sounds similar to the upgrade issue MB-38046 for 6.5 described here:
https://docs.couchbase.com/server/current/release-notes/relnotes.html#install-and-upgrade

Have you restarted Sync Gateway since the cluster was upgraded to 6.5?

Note there is a documentation update coming for this issue as well. @kamini.jagtiani or @keshav_m may be able to update you on the details.

Thanks @ingenthr.
When performing an online upgrade from Couchbase Server 6.0.3 to Couchbase Server 6.5, active Couchbase Java SDK 2.x applications may give exception 4040: No such prepared statement .

Client-side workarounds for the Couchbase Java SDK 2.x:

  • Restart the application server after the server upgrade. This implicitly clears the client cache, leading it to prepare statements again.
  • Change the client code. If the client receives exception 4040 , clean the cache using invalidateQueryCache() and retry. (This only works for Java.)

Server-side workarounds:

  1. Upgrade the first node — this can be a Data service node, or any other node.
  2. Increase the prepared statement cache size. In the Query Workbench, go to Settings General Query Settings Advanced Query Settings and set Prepared Limit to 66560 .
  3. Make sure that encoded_plan will be honored. Also in Settings General Query Settings Advanced Query Settings , set N1QL Feature Controller to 8 .

These settings persist, and will be used as other query service nodes are upgraded.

Also, the new Java sdk will be released in a few days. That will auto retry on receiving 4040.

1 Like

New Java SDK is out now - 2.7.13: https://docs.couchbase.com/java-sdk/2.7/sdk-release-notes.html#version-2-7-13-3rd-march-2020

To provide better compatibility with cluster upgrades and with query prepared statements on Couchbase Server 6.5 and later, the SDK now transparently retries the 4040 response code in a similar way to with related error codes.

To clarify - Sync Gateway isn’t using the Java SDK, it’s internally using the Go SDK.

@kamini.jagtiani Given that, would the server-side workarounds still apply, or do you see this as an unrelated issue?

@adamf server side workarounds should apply.
Also, restarting the application server after the upgrade should also work.

Thanks for answer @adamf

Yesterday, we set Prepared Limit to 66560, N1QL Feature Controller to 8 and restarted Sync-gateway. Today, we have against Error.