Upsert with data value with 'update' or 'delete' causes internal error 3250

SQL++
1

In the couchbase UI (Query)
Doing an upsert via N1QL, and one of the fields happens to have text in it containing words like update or delete. This, when executed, is causing an internal error 3250…! I’ve checked for non-printable characters, and it seems to only do this with those specific terms in the text. What’s up with that?

When I run these two upserts, the error occurs…

upsert INTO devtn (KEY,VALUE) values ('53',{'kind':'role','kindVersion':'6.0.0','productPainPointId':'53','productPainPointTitle':'My company has some data','productPainPointAudienceType':'commercial','productPainPointAnswer':'some answer' });
upsert INTO devtn (KEY,VALUE) values ('54',{'kind':'role','kindVersion':'6.0.0','productPainPointId':'54','productPainPointTitle':'wwwwwwThe cost of privacy controls and law update','productPainPointAudienceType':'commercial','productPainPointAnswer':'some answer' });

but if I run the statements separately, the first followed by the second, both work fine.
The error when I run them both

> [
>   {
>     "_sequence_num": 1,
>     "_sequence_query": "upsert INTO devtn (KEY,VALUE) values ('53',{'kind':'role','kindVersion':'6.0.0','productPainPointId':'53','productPainPointTitle':'My company has some data','productPainPointAudienceType':'commercial','productPainPointAnswer':'some answer' });",
>     "_sequence_query_status": "success",
>     "_sequence_result": {
>       "results": []
>     }
>   },
>   {
>     "_sequence_num": 2,
>     "_sequence_query": "\nupsert INTO devtn (KEY,VALUE) values ('54',{'kind':'role','kindVersion':'6.0.0','productPainPointId':'54','productPainPointTitle':'wwwwwwThe cost of privacy controls and law update','productPainPointAudienceType':'commercial','productPainPointAnswer':'some answer' });",
>     "_sequence_query_status": "Internal Server Error",
>     "_sequence_result": [
>       {
>         "code": 3250,
>         "msg": "Advise supports SELECT, MERGE, UPDATE and DELETE statements only."
>       }
>     ]
>   }
> ]
2

In query work bench when ever query executed it also runs EXPLAIN, ADVISE and display in Plan, Advice tab.

As there is no Advise on UPSERT statement and it is multi statement , UI may not able to detect this and skip it. It sends the advise to query service. Query service returned error 3250 (Advise supports SELECT, MERGE, UPDATE and DELETE statements only.). This might have taken care by later releases. cc @eben

3

@ksully - that error looks familiar, I think I fixed it in the most recent release. Can you tell me which version you are running?

4

it’s 7.0.0-5302-enterprise

5

Thanks @ksully, I found the problem. There were missing parentheses in a regular expression used for detecting whether to run ‘advise’ or not, and the word ‘update’ in the data of your second record caused the regex to indicate that the query might be an ‘update’ query.

For tracking purposes I created this issue: https://issues.couchbase.com/browse/MB-49305

1 Like
6

Thanks for finding that :blush: