Timeout on N1QL DELETE query

Python SDK
,
1

Hi all,

I’m facing timeout issues when running a longer running DELETE query with N1QL via the Python SDK (v 3.2.6).

My query has the form akin to DELETE FROM bucket.scope.collection WHERE .... The statement is supposed to delete a few hundreds or thousands of documents. I run the query via:

cluster = Cluster('couchbase://...', options)
result = cluster.query(query)
result.execute()

The code always throws an exception like:

couchbase.exceptions.TimeoutException: <RC=0xC9[LCB_ERR_TIMEOUT (201)], HTTP Request failed. Examine 'objextra' for full result, Results=1, C Source=(src/pycbc_http.c,203), OBJ=ViewResult<rc=0xC9[LCB_ERR_TIMEOUT (201)], value=None, http_status=0, tracing_context=0, tracing_output=None>, Context={'first_error_code': 0, 'http_response_code': 0, 'first_error_message': '', 'statement': "...", 'client_context_id': 'f2ab3a97333ee2a1', 'query_params': '', 'http_response_body': '', 'endpoint': '', 'type': 'QueryErrorContext'}, Tracing Output={":nokey:0": null}>

When I run the same code in a query via the Couchbase web console, it runs without problems and finishes in less than a minute.

Do you have any thoughts?

Thanks for your help in advance :pray:t3:

2

Hello @0xef06b0 although you have mentioned about the querying taking less than a minute to execute via web console, did you try increasing the QueryTimeout value and see if that helps ?

1 Like
3

Hi
I suspect the issue might be that

  1. You have set the Query timeout too low for your request to complete. Or
  2. May be the default 75 isnt sufficient for your query.
    In any which case, please refer to below documentation and increase the time out and please let us know of your result so that we can take it further
4

Hi @praneeth.bokka and @AV25242 , thanks a lot for your input. I also though initially, that it was related to the timeout setting. But I have already incfeased the timeout to 3(!) minutes via:

timeout_options =ClusterTimeoutOptions(
    query_timeout=dt.timedelta(seconds=180))
options = ClusterOptions(
    PasswordAuthenticator(user, pwd),
    timeout_options=timeout_options
)

I assume that this is the way to increase the timeout also for DELETE queries, right? Or is there another option to be used?

Thanks!

5

Hi @0xef06b0 - I have provide a snippet that shows the two ways you can alter the query timeout. You can adjust the global query timeout and also set the timeout on a per-query basis. The timeout used will be the lower of the two.

Are you running the query on a collection? Could you provide actual query you are trying to run? Would be useful for us to see, especially if the same query works via the Web UI.

from datetime import timedelta

from couchbase.cluster import Cluster, ClusterOptions, ClusterTimeoutOptions, QueryOptions
from couchbase.auth import PasswordAuthenticator

def run_sample_code():
    try:
        host = "couchbase://localhost"
        username = "Administrator"
        pw = "password"
        # default query timeout = 75s
        options = ClusterTimeoutOptions(kv_timeout=timedelta(seconds=60),
                                        query_timeout=timedelta(seconds=120))

        cluster = Cluster(host, ClusterOptions(
            PasswordAuthenticator(username, pw), timeout_options=options))

        query_iter = cluster.query("SELECT * FROM `travel-sample` LIMIT 100")
        for r in query_iter.rows():
            print(f"Found row: {r}")

        # if the timeout is set the in QueryOoptions, the 
        # timeout will be the lower between the global timeout 
        # timeout provided in the QueryOptions
        q_opts = QueryOptions(timeout=timedelta(seconds=1))
        query_iter = cluster.query("SELECT * FROM `travel-sample` LIMIT 10000", q_opts)
        count = 0
        for r in query_iter.rows():
            count += 1
        print(f"Got {count} rows")

    except Exception:
        import traceback
        traceback.print_exc()


if __name__ == '__main__':
    run_sample_code()
6

Hi @jcasey ,

Thank you so much for providing me with the snippet.

Although the snippet was not the solution to my problem, I found it very useful. In particular, I didn’t know that it was possible to provide timeout values at query level.

Fortunately, I was able to solve the issue and it was a pretty stupid privileges issue. My application user had the “Data Writer” role but it was lacking the explicit right to run DELETE queries. The user which I was using the console with has admin privilges. After adjusting the roles, the queries now run.

The thing that was very misleading to me all the time, was the fact that I was getting a couchbase.exceptions.TimeoutException as opposed to some hint that my application user is missing necessary privileges.

Thnanks a lot again to everybody who contributed here. Highly appreciated :pray:t3:

1 Like