No upgrade is done here. As I said, there are different environments with different versions showing same issue intermittently.
Ah okay. And does the exact same query work from the UI? Trying to figure out if its a client or server thing to investigate first.
Query is returing result from both web console and cbq every time it is being executed.
Are you using prepared and/or parameterized queries?
How are you using it from the java SDK? prepared and/or parameterized queries?
Will have to contact java team for it’s answer. I’m not sure what makes a query prepared/parameterized.
Depending on how it is used, for example you need to make sure that when using prepared statements (.adhoc(false)) that the index selection is not part of the parameters since then it can’t be prepared properly. In general UI and CBQ do not use prepared statements and in the past we had issues in that area… so if you can share that part of the java code it would be helpful to diagnose.
We are using (.adhoc(false)) in the sdk for the query execution. I guess this makes the query prepared.
Any suggestion on what can be done here further?
It does … just for the sake of testing, can you set it to adhoc(true) (which would be the default) to make it not prepared and see if the issue persists?
@daschl, to be more clear, the query is used for Name search. When we are searching for ‘mar’, the results are shown (every time). but when the string is changed to ‘mark’, the query gives no result even after ensuring that a person with that name exists. (due to index not found error). Why would that prepared query will work in one case and fail in another?
P.S. as of now we will not be able to test adhoc(true) setting as development servers have this problem fixed (automatically). the problem persists on production environment.
Could I ask you to prepare the faulty statement on 4.6.4 and 4.6.3 and post the plans here?
I think the encoded plan prepared in 4.6.4 is not being unmarshalled or replaced correctly in 4.6.3.
Can you give me the steps for this? I mean I don’t know how to create prepared statement through web console.
Could you post the query and index that having issue also java code snippet.
Query:
SELECT r.* FROM (SELECT meta(rgm).id as id,CASE WHEN rgm.verificationStatusCode = "ICMCN" THEN 1 WHEN rgm.verificationStatusCode = "ICMO" THEN 2 WHEN rgm.verificationStatusCode = "ICM" THEN 3 WHEN rgm.verificationStatusCode = "PO" THEN 4 WHEN rgm.verificationStatusCode = "P" THEN 5 WHEN rgm.verificationStatusCode = "A" THEN 6 WHEN rgm.verificationStatusCode = "ONS" THEN 7 WHEN rgm.verificationStatusCode = "IC" THEN 8 ELSE 9 END as verificationStatusCodeNumber,CASE WHEN rgm.guestDocumentOverallModerationStatus = "SR" THEN 1 WHEN rgm.guestDocumentOverallModerationStatus = "P" THEN 2 WHEN rgm.guestDocumentOverallModerationStatus = "SA" THEN 3 WHEN rgm.guestDocumentOverallModerationStatus = "R" THEN 4 WHEN rgm.guestDocumentOverallModerationStatus = "A" THEN 5 ELSE 6 END as guestDocumentOverallModerationStatusNumber FROM reporting rgm WHERE rgm.type = "ReservationGuestModeration" AND meta(rgm).id NOT LIKE "_sync%" AND rgm.verificationStatusCode IN ["ICMCN","ICMO","ICM","PO","P","A","ONS","IC"] AND (ANY x IN [LOWER(rgm.fullName),LOWER(rgm.lastName)] SATISFIES x like "mar%" END) ORDER BY rgm.embarkDate ASC, rgm.isInternational DESC, rgm.isOcrDirty DESC, verificationStatusCodeNumber ASC, rgm.lastModifiedDocDate DESC, guestDocumentOverallModerationStatusNumber ASC LIMIT 10 OFFSET 0) AS rgm JOIN reporting r on keys rgm.id
Index:
CREATE INDEX IX_GlobalSearch_Name_ReservationGuestModeration ON reporting(verificationStatusCode, (distinct (array x for x in [lower(fullName), lower(lastName)] end)),guestDocumentOverallModerationStatus,embarkDate,isInternational,isOcrDirty,lastModifiedDocDate,firstName,lastName) WHERE ((type = "ReservationGuestModeration") and (not ((meta().id) like "_sync%")));
Do u have java code snippet. Also did u restart SDK application and see if that resolves after index recreation.
Literally,
PREPARE SELECT …
ie add PREPARE in front of the statement and collect the response.
Do it on nodes of different version.
@marco_greco, it started working after the upgrade of couchbase server from version 4.6.3 to 4.6.4.
My assumption is that before the upgrade, the query was being executed subsequently with faulty plan that was generated when the query was first executed. I dropped and recreated the indexes that are being used by this query few times but the index that was mentioned in the plan of the prepared query was not available anymore so it was throwing error ‘Index Not found’.
Now after the upgrade, the plan is destroyed and when the query is executed again for the first time, new plan with available indexes got stored which is working fine now.
Let me know if my assumption is correct or not.
If you drop and recreate indices, the plan that had been created before hand is no longer valid, because the index that the plan refers to no longer exists: this is why you get the error.
SDKs might be able to capture this and correct the situation by dropping and recreating the statement (this is all done internally and transparent to you), but we have had some problems in the past with this.
In the upcoming 5.5 version this sort of problem is handled directly inside the N1QL service, and the SDKs don’t have to do anything in particular to execute the prepared statement after the index change.
Does that answer the question?
The problems were due to changes in query’s public interface without prior notification of change of interface and client behavioral expectations. Marco is working on making this better in the future.
Pretty much yes. But this means that this issue may regenerate regardless of couchbase version (prior to 5.5). What can be done to clear the cache that stores prepared plan so that it creates a new plan accordingly to the available indexes?
Does the server requires a restart or there is some other way as well?