Java SDK and Prepared Statement

Java SDK
1

Hi,

I was wondering how prepared statement were managed by the Java SDK and Server.

We managed to be in a funny state where some of our clients were returning the right answer to a prepared query and some others were not returning anything. We ended up in this situation in the following manner:

  • We had some indexes created and prepared statement using them
  • I deleted the indexes and recreated them with the same name but with the order of fields changed as I realised our queries will be quicker this way - all of this was done using the cbq tool with the client still running
  • After this change, we were in the situation described
  • We restarted all of our clients and all of them behave correctly now

So, is this normal behaviour or it should not have happen and somehow the clients detected the index change and recreated the prepared statement?

What should be the best way to manage this kind of modification in the indexes with a running application relying on them for some queries?

Many thanks.

2

How did you execute the prepared statements from the client SDK (including any N1qlParams)?
Since you created them from cbq, I’m guessing you ran a EXECUTE xxx statement in the SDK as well?
If so, this looks like a question purely related to query service, @geraldss any insight to provide?

3

Hi,

I am running the queries this way:
String statement = “SELECT …”;
JsonObject placeholderValues = …
N1qlQuery query = N1qlQuery.parameterized(statement, placeholderValues, N1qlParams.build().consistency(ScanConsistency.REQUEST_PLUS).adhoc(false));
bucket.query(query);

In cbq I only ran DROP INDEX and CREATE INDEX statement to replace my indexes… Did not run any queries, only ran queries through our client.

Regards,
Laurent.

4

Ah you replaced the index, my bad I thought you replaced the prepared statement. I don’t know why I thought that, because there is actually currently no real way of doing it.

So using adhoc(false) will roughly do the following:

  1. Create a prepared statement on the server side, with a uniquely generated name.
  2. Store the prepared statement name, but also a compressed version of the original statement (as returned by the server when preparing the statement)
  3. Execute that prepared statement

If you call the same N1QL query a second time (with adhoc still false), the SDK will find it in its cache and:

  1. Try to EXECUTE the corresponding prepared statement
  2. In case of certain errors on the server side (eg. that statement is now unknown on the node), it’ll reprepare it using the compressed version. Actually, IIRC it passes the compressed version with every query so that the server knows to reprepare if the prepared name is not found server side.

Did your query get any error, including the finalSuccess() method returning false and the errors() collection not being empty?

@geraldss could it be that the compressed plan or the plan stored server side for the prepared statement are dependent on the “shape” of the index?

5

I may have had an error but did not see it by the time I saw the errors on the index not returning any data, it was “too” late…

But I created a simple test to reproduce it and I can see an error on the first request after modifying the indexes and then empty responses:
(AFTER) Errors: [{“msg”:“Index Not Found - cause: queryport.indexNotFound”,“code”:12016}]
(AFTER) Expected doc:204 but nothing returned
(AFTER) Expected doc:285 but nothing returned
(AFTER) Expected doc:892 but nothing returned
(AFTER) Expected doc:777 but nothing returned
(AFTER) Expected doc:080 but nothing returned
(AFTER) Expected doc:846 but nothing returned
(AFTER) Expected doc:504 but nothing returned
(AFTER) Expected doc:188 but nothing returned
(AFTER) Expected doc:647 but nothing returned
(AFTER) Expected doc:000 but nothing returned

The question is, even if I catch the error, how can I make sure the prepared statement is recreated - can’t see in the SDK how to force this to happen as, as you said, it is cached in the SDK.

Thanks,
Laurent.Test.java.zip (2.1 KB)

6

What is cached is, for a given N1QL statement, the name of the prepared statement to use + the compressed execution plan to give as a fallback to the server.

There is a invalidateQueryCache method on the Bucket to clear that local cache.