Paramterized query failing for NODE SDK v3.0.4 with adhoc:false

N1QL
,
#1

Hi,
We have used prepared statements with adhoc=false. The queries are paramterized. The query fails to work sometimes with error code 12016 “Index Not Found - cause: queryport.indexNotFound”.
We are using CB EE v 6.5.1.
This query fails intermittently and then we need to delete the prepared statements from system:prepareds.
What could be the reason for this behavior and a potential fix.
eg query
…
Update default as d use keys
(select raw meta().id as i from default s
where s.key1=$Val1
and s.key2=$Val2
and docType=‘testUpdate’)
set d.key3 = $Val3
returning d.key3;
…
CREATE INDEX idx_test on default(key1,key2) where docType=‘testUpdate’
…
Could this be because of not putting alias ‘s’ inside meta().id to meta(s).id

The failing query from SDK works fine in workbench without doing any cache clean or adding s to meta().id. The workbench query picks up the expected index as well

#2

Hey @bora.mohan,
I’m going to move this over to the Query forum instead. While you are using the SDK to perform the query, the underlying issue here is related to the behaviour of prepared statements and how they interact with index changes.
Cheers, Brett

#3

It looks like underneath index is dropped or altered or not available.
If nothing is changed it should work.

The following is simplest format of query, you can try and see if it resolves.

UPDATE default AS  d
SET d.key3 = $Val3
WHERE d.key1 = $Val1 AND d.key2 = $Val2 AND d.docType = "testUpdate"
RETURNING d.key3;

cc @Marco_Greco

#4

@vsr1 , I used this query instead of the one you suggested to get unique documents each time. on the set part I am backing up the fields that needs to be updated, so that any downstream failures, I have the previous state to rollback to.

And, about the original issue, I tried the below in different environments,

  1. In one environment A, I deleted the system:prepareds data.
  2. In other environment B, I cleared the system:prepareds data and also redeployed updating the query to use meta(s).id. This also worked. But when I check the system:prepareds entry, it prepared the statement without ‘s’ → meta().id instead of meta(s).id.
  3. And in the last environment C, I didn’t touch the system:prepareds but pushed a new build (the same of env B). It also worked.
  4. There is another environment D (the lowest env), where nothing is breaking. Everything works and the error couldn’t be replicated.

Also, nothing had changed, when the error was persistent, the same query in its parameterized form was working from workbench when I pass the exact parameters from the settings of workbench (In all A,B and C envs).

With the above, I am not sure what could be the RC.