var query_string = 'SELECT airportname FROM `travel-sample` WHERE country=$1';
var query = couchbase.N1qlQuery.fromString(query_string).adhoc(false);
I want to know If I have two same couchbase.N1qlQuery.fromString object like
var query_string = 'SELECT airportname FROM `travel-sample` WHERE country=$1';
var query1 = couchbase.N1qlQuery.fromString(query_string).adhoc(false);
var query2 = couchbase.N1qlQuery.fromString(query_string).adhoc(false);
What happens? each query that their own cached query plan? or SDK handle it , I ask it because I want to know in a function that I want to call it millions , inside function I have couchbase.N1qlQuery.fromString must I use global variable to keep a single query or not
The queries are hashed for the query cache and additionally, the query itself is verified at execution time (such that collisions would potentially cause a recache, but never an incorrect query execution, this obviously would be nearly impossible anyways). Utilizing the same query object or different query objects will no matter, as the cache is global to a particular bucket instance inside your application.
@brett19 I can run a query in nodeJS SDK but when I add .adhoc(false) , I have the following error
No index available on keyspace default that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.
I have optimized index and it is online just problem is .adhoc(false)
You can clear the cache using the Bucket.invalidateQueryCache method. The adhoc=false flag causes your prepared queries to be prepared early using the original indexes that are available, and new indexes that are created will not be taken into account. You may wish to ensure that invalidateQueryCache is invoke at some point to clear out the cache of any old entries if you are dynamically creating your indices. Additionally, it’s possible that your indexes are online but the query engine is not matching your index to the query, would you be able to elaborate a bit on which indexes you have available and what queries you are performing?
@brett19 thanks for your response
The query itself is not important here, and I’m sure I have correct index and it’s online, as I explain it and it works without prepared
I want to know , where is cache storage? Is it in memory? Or hard disk?
@brett19 , I call Bucket.invalidateQueryCache too , still get error
var query_string = 'SELECT name FROM default WHERE userId=$userId AND type="User"';
var query1 = couchbase.N1qlQuery.fromString(query_string).adhoc(false);
CREATE INDEX ON default (userId,name) WHERE type="User"
"No index available on keyspace bucket that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.
Cache is stored locally in the clients Bucket instance (in memory). I’ll look into the issue with adhoc affecting index selection, are you able to provide some information about your environment? Namely the server version and platform, the Node.js versions, SDK version and platform (along with any other relevant environmental information)?
@socketman2016 I’m not able to reproduce this issue. Neither on community edition nor on enterprise.
I noticed that you are querying two buckets, “default” and “travel-sample”, however the error message seems to indicate that the bucket name is actually “bucket”:
"No index available on keyspace bucket that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.
Normally the message would be "No index available on keyspace <BUCKET_NAME> … "
Can you please make sure that the “adhoc(false)” query is indeed the correct one?
If this is still an issue. Please provide the following:
Full code sample that reproduces the issue. (please don’t cut and paste sample/selective code, full code instead)
The RBAC roles of the user you authenticate with.
The list of indexes you have defined, you can attach the output of select * from system:indexes.
I tried to create a sample app using travel-sample bucket to reproduces it , I cannot reproduce it in travel-sample
But in my default bucket , It occur , Why!
I know that the query have index , because :
@brett19@davids I found that when I dont use Parameterized Queries the query works with .adhoc(false) too
In the following examples , without adhoc(false) both works
const couchbase = require('couchbase');
const cluster = new couchbase.Cluster('couchbase://localhost');
cluster.authenticate('Administrator', 'password');
const bucket = cluster.openBucket('default');
const N1qlQuery = couchbase.N1qlQuery;
const query = N1qlQuery.fromString('SELECT token FROM default WHERE type=$type AND userKey=$userKey').adhoc(false);
bucket.query(
query,
{
type:'Token',
userKey:'u|123456',
},
function(err, rows, meta) {
if (err) {
throw err;
// throws Error: No index available on keyspace default that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.
}
console.log('Done', JSON.stringify(rows));
});
const couchbase = require('couchbase');
const cluster = new couchbase.Cluster('couchbase://localhost');
cluster.authenticate('Administrator', 'password');
const bucket = cluster.openBucket('default');
const N1qlQuery = couchbase.N1qlQuery;
const query = N1qlQuery.fromString('SELECT token FROM default WHERE type="Token" AND userKey="u|123456"').adhoc(false);
bucket.query(
query,
function(err, rows, meta) {
if (err) {
throw err;
}
console.log('Done', JSON.stringify(rows));//It works
});
As you can see first example used Parameterized Queries but second not
For me , Parameterized Queries throws error
Error: No index available on keyspace default that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.
but second script works , I understand that when I have both Parameterized Queries and .adhoc(false) it occures