Couchbase No Index Available

Question

We are having problems with a couchbase N1QL Query.

We have an index defined as follows:

CREATE INDEX `AppUser_SubjectId3` ON `Portal`(`SubjectId`) WHERE ((meta(self).`id`) like `AppUser%`)

We are then trying to run the following query:

SELECT RAW `Extent1` 
FROM `Portal` as `Extent1` 
USE INDEX (`AppUser_SubjectId3` USING GSI) 
WHERE (`Extent1`.`SubjectId` = 'c8ea08231c3a985a06342355b87d6e2d6290a985d5c3592e5b8e5e5f14608a08')

And get the following error:

No index available on keyspace Portal that matches your query.
Use CREATE INDEX or CREATE PRIMARY INDEX to create an index,
or check that your expected index is online.

We have confirmed the obvious that the index is online. The only item worth noting is that the Bucket does not actually contain any documents at the moment, but we would not expect this error in this instance, simply nothing to be returned.

Any ideas?

EDIT:

I have created another index without the WHERE clause and it does not return the error any longer.

CREATE INDEX `AppUser_SubjectId4` ON `Portal`(`SubjectId`)

The only problem is that the WHERE clause is required!

score 2 · Accepted Answer · answered Aug 08 '19 at 12:53

The Index You created is partial index (i.e Index has WHERE clause, only has entries that satisfies where condition). For query to use that index it must qualify (i.e query where clause must be subset of index where clause + query predicate must have leading index key) other wise by choosing that index it can result in wrong results and query optimizer will not choose that index.

Also like AppUser% is incorrect it must be single or double quotes not back-ticks.

CREATE INDEX `AppUser_SubjectId3` ON `test`(`SubjectId`) 
WHERE meta().`id` like "AppUser%";


SELECT RAW e 
FROM `Portal` AS e 
USE INDEX (`AppUser_SubjectId3` USING GSI) 
WHERE e.`SubjectId` = 'c8ea08231c3a985a06342355b87d6e2d6290a985d5c3592e5b8e5e5f14608a08'
      AND META(e).id LIKE "AppUser%";

Designing Index For Query In Couchbase N1QL https://blog.couchbase.com/wp-content/uploads/2017/10/N1QL-A-Practical-Guide-2nd-Edition.pdf

not the answer i was looking for as doesn't actually solve the overall problem i was working on, but it does solve this particular issue i ran into here. Thanks! — GreatSamps, Aug 08 '19 at 13:14

score 0 · Answer 2 · answered Aug 08 '19 at 11:03

I tried reproducing your problem successfully, with an empty bucket named test. I created the same index with the following query :

CREATE INDEX `AppUser_SubjectId3` ON `test`(`SubjectId`) WHERE ((meta(self).`id`) like `AppUser%`)

And checked that the index was online. I then tried your query, which resulted with the exact same error.

[
  {
    "code": 4000,
    "msg": "No index available on keyspace test that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.",
    "query_from_user": "SELECT RAW `Extent1` FROM `test` as `Extent1` USE INDEX (`AppUser_SubjectId3` USING GSI) WHERE (`Extent1`.`SubjectId` = 'c8ea08231c3a985a06342355b87d6e2d6290a985d5c3592e5b8e5e5f14608a08')"
  }
]

So I checked the bucket's schema with :

INFER `test`

This returned the following error :

[
  {
    "code": 0,
    "msg": "Keyspace test has no documents, schema inference not possible"
  }
]

However I also created a primary index, and querying it would just return an empty results array.

Now I don't really know where to go from this, but it does seem like querying an empty bucket using anything else than a primary index would return this error.

thanks for your help with this. I have edited my question with some additional tests i have done that appear to rule out empty bucket issues. — GreatSamps, Aug 08 '19 at 12:54

score 0 · Answer 3 · edited Dec 14 '22 at 13:11

0

This issue is resolved for me just creating an index over bucket without using any where clause.

edited Dec 14 '22 at 13:11

Adriaan

17,741
7
42
75

answered Dec 14 '22 at 13:03

yuvraj

181
1
12

Please read [answer]. Rather than having to prompt you for more information, the answer should stand on its own from the get go. If you think this answer needs clarification, please [edit] it as such. – Adriaan Dec 14 '22 at 13:11

Couchbase No Index Available

3 Answers3

Linked