Prepared query in nodejs

@socketman2016 If you still have the cluster up, could you please provide the result of the query select * from system:indexes where keyspace_id='default'

@socketman2016 I’m not able to reproduce still, neither in community nor in enterprise. Results returning fine for me.
I used the exact script you provided, with the exact parameters passed.
I need to exactly match your environment. Please provide:

  • The output of select * from system:indexes where keyspace_id='default'
  • Please run cbc version on the node where you run the script and provide us the output.
  • Please run the following network capture command:
    tcpdump -i <interface> -s0 -W1 -C 500 -w filename.pcap port 8093,
    and then run the script (assuming the issue will be reproduced), and then provide us with the filename.pcap. I’m interested in seeing what the SDK is actually sending the Query Engine. Note this would contain query data. If this is confidential, please do not upload.

For simplicity , I created a fresh default bucket and added minimal index and data to reproduce it

CREATE INDEX tokexIdx1 ON default(userKey,token)  WHERE type="Token"
SELECT token FROM default WHERE type="Token" AND userKey="u|123456"
  • The output of select * from system:indexes where keyspace_id='default'
[
  {
    "indexes": {
      "condition": "(`type` = \"Token\")",
      "datastore_id": "http://127.0.0.1:8091",
      "id": "a8ee205918d4d3de",
      "index_key": [
        "`userKey`",
        "`token`"
      ],
      "keyspace_id": "default",
      "name": "tokexIdx1",
      "namespace_id": "default",
      "state": "online",
      "using": "gsi"
    }
  }
]
  • Please run cbc version on the node where you run the script and provide us the output.

not found cbc , where is cbc? (on centos 7)

tcpdump -i <interface> -s0 -W1 -C 500 -w filename.pcap port 8093 ,

As filename.pcap included sensitive data , currently I cannot send it , later I will send you if you need

But it send the following request

POST /query/service HTTP/1.1
Host: MyServerIP:8093
User-Agent: libcouchbase/2.9.5-njs couchnode/2.6.3 (node/10.15.3; v8/6.8.275.32-node.51; ssl/1.1.0j)
Accept: application/json
Authorization: Basic SECRET
Content-Length: 111
Content-Type: application/json

{"statement":"PREPARE SELECT token FROM default WHERE type=$type AND userKey=$userKey","timeout":"75000000us"}

When I remove .adhoc(false); body contains params

{"$type":"Token","$userKey":"u|123456","statement":"SELECT token FROM default WHERE type=$type AND userKey=$userKey","timeout":"75000000us"}

@socketman2016 Thanks.
I don’t see any issues with how the SDK sends the queries. The SDK automatically prepares the statement, so the first payload is the prepare statement, which would not have parameters. The actual query comes after it.

The issue is with the index you define. You have:

But when the SDK attempts to prepare the statement:

PREPARE SELECT token FROM default WHERE type=$type AND userKey=$userKey

There is no index to satisfy this. This is because in your index definition you limit the index to WHERE type="Token".

Can you try this index and let me know if it works:

CREATE INDEX `tokexIdx2` ON `default`(`type`,`userKey`,`token`)

Alternatively, modify your query to work properly with your index, so instead of

SELECT token FROM default WHERE type=$type AND userKey=$userKey

Do

SELECT token FROM default WHERE type="Token" AND userKey=$userKey