Index Not Found - cause: queryport.indexNotFound (12016)

SQL++
1

I’m using 4.5.1-2844 Enterprise Edition (build-2844) on Windows Server 2012 and .NET client version 2.3.5.

I’ve created an index on my node with definition:

CREATE INDEX `Idx_Core_User_1`
ON `core_north-europe`(`docType`,`establishmentId`,`externalId`,`groupMemberAlias`,`identityProvider`,`name`,`role`)
WHERE (`docType` = "User") USING GSI;

I query using the SDK, as follows:

var request = QueryRequest.Create()
    .Statement("SELECT META().id FROM `core_north-europe` WHERE docType="User" AND establishmentId IN [$1] ORDER BY LOWER(name) OFFSET 0 LIMIT 20;")
    .AddPositionalParameter(new object[] { "FaS87F40jUWzPQtVFkFGaQ" })
    .ScanConsistency(ScanConsistency.NotBounded)
    .AdHoc(false);

var result = await _bucket.QueryAsync<JObject>(request).ConfigureAwait(false);

My understanding what’s going on when I pass AdHoc(false) is that the client is preparing the query and then executing that prepared version on the server by passing the prepared statement Id this and everytime after that I attempt to execute the same statement.

My issue is that if I delete and recreate the index with the same defintion, I get an Index Not Found - cause: queryport.indexNotFound (12016) error. I understand why that would happen in the first instance - the client’s attempting to execute a prepared statement that no longer exists - but once there’s a failure I was expecting the SDK to re-prepare the query. It doesn’t though, it just keeps returning that error until I recycle the application.

This example seems a bit contrived perhaps, but we’ve seen the same happen on production when an index/query node fails over.

2

I’m not an expert on the SDKs, but in this case $1 is a named parameter, not a positional parameter. Try replacing it with a question mark, which makes it a positional parameter.

1 Like
3

Hi @frasdav

On the N1QL side, you will need to re-prepare the statement. Once you drop the index, even if you recreate it, the prepared statement has an old plan so it will not pick up the new index. Im not sure if the SDK’s re-prepare. I think they do that for some errors and not all but I’m not sure. @ingenthr might be able to help.

Thanks
Isha

1 Like
4

Hi @frasdav -

Can you use Fiddler or Charles or some other HTTP debugger to get the raw HTTP response from the server and post it here? It will be helpful for debugging the issue in the SDK.

Thanks,

Jeff

1 Like
5

In the specific case of an error code 5000 and it contains the string “queryport.indexNotFound”, the .NET SDK should re-prepare and execute the statement with AdHoc(false). It’s possible that there’s an issue in the SDK or that the error code has changed unexpectedly.

Ah, @jmorris just beat me to the reply, but either from .NET or from some other debugger, if we can verify the response then we’ll know what to check for. I filed NCBC-1232 to track, but any help you can offer to get us what’s going on here would be great @frasdav.

As a possible workaround, at the application level you can invalidate the cache which will cause it to be re-prepared.

1 Like
6

Hi @frasdav

I have been able to reproduce the issue as described above and the server is indeed sending back error code 12016. The SDK is looking for an error code 5000 with the message containing “queryport.indexNotFound” so we are not recognising this as an index failure and do not eject the query plan or re-prepare further requests.

I’ve made the change to introduce the new error code and check for this in addition to the other error codes.

This should make it into our 2.3.10 maintenance release planned for December 6th 2016.

7

Hi @MikeGoldsmith

Thank you, that’s great.

Apologies, @jmorris and @ingenthr for not getting back to you, and thanks for the workaround!

Cheers,
Fraser

1 Like