Unexpected internal client exception while running query on server, runs perfectly against local CB server

Hi there,

I’m getting very little information:
client internal error | {“statement”:“SELECT \n\t\t\tfirst cred.email for cred in l.credentials end as email, \n\t\t\te.subdomain as subdomain, \n\t\t\te.content.activity_type as activity_type, \n\t\t\te.content.user_agent as user_agent, \n\t\t\te.timestamp as timestamp\n\t\tFROM dev_events AS e\n\t\t\tJOIN loginapp AS l ON e.content.identity_id = META(l).id\n\t\tWHERE\n\t\t\tMETA(l).id = $identityID\n\t\tORDER BY e.timestamp DESC\n\t\tOFFSET $offset\n\t\tLIMIT $limit”,“client_context_id”:“1204d2a2-c73c-46f3-85dc-35e1d9bd901e”}

I’ve nicified the query for you:
SELECT
first cred.email for cred in l.credentials end as email,
e.subdomain as subdomain,
e.content.activity_type as activity_type,
e.content.user_agent as user_agent,
e.timestamp as timestamp
FROM dev_events AS e
JOIN loginapp AS l ON e.content.identity_id = META(l).id
WHERE
META(l).id = $identityID
ORDER BY e.timestamp DESC
OFFSET $offset
LIMIT $limit

The params provided look like this:
params: map[identityID:5bfd44c8-9936-11eb-9727-000d3a8356e9 limit:10 offset:0]

When I run the exact same query using exactly the same code on localhost agains community edition 6.6 it works. If I deploy the app to our test environment against a community edition 6.0 I get the error above. If I run the query in the workbench in the test environment it works.

What am I missing? Any help is highly appreciated.

You don’t mention which version of the Golang SDK you’re using.

Do other queries work, and just not this one? One guess would be that you need a bucket opened, but if other queries are working fine it wouldn’t be that. I’d perhaps recommend turning on some logging at both sides to see if the query is being received and processed and returned properly. @chvck may have some other guesses.

Thanks for the fast reply. Here is the section from go.mod:
github.com/couchbase/gocb/v2 v2.2.2
github.com/couchbase/gocbcore/v9 v9.1.3

Yes, other queries are working fine.
Can you elaborate a bit more on how to turn on logging on the couchbase side?

OK, I figured it out. It’s a bit embarrassing. My user had only access to one bucket and not to both involved in the query.

Would it be possible to improve the error message to be a bit more helpful than a generic: internal client error?

@Frank_Lyner ,

FYI: It looks like if id existing in login you want get all events. Not sure how much data result size.

Check out following query might be faster

CREATE INDEX ix1 ON dev_events(content.identity_id, timestamp DESC, content.user_agent, content.activity_type, subdomain);

6.5+

WITH loginId AS ((SELECT META().id, FIRST cred.email FOR cred IN credentials END AS email FROM loginapp USE KEYS $identityID)[0])
SELECT
      loginId.email,
      e.subdomain AS subdomain,
      e.content.activity_type AS activity_type,
      e.content.user_agent AS user_agent,
      e.timestamp AS timestamp
FROM dev_events AS e
WHERE e.content.identity_id = loginId.id
ORDER BY e.timestamp DESC
OFFSET $offset
LIMIT $limit

Pre 6.5.0 (Use following query or  FROM loginapp JOIN db_events so that if no entry JOIN stops)

SELECT
      loginId.email,
      e.subdomain AS subdomain,
      e.content.activity_type AS activity_type,
      e.content.user_agent AS user_agent,
      e.timestamp AS timestamp
FROM dev_events AS e
LET loginId = (SELECT META().id, FIRST cred.email FOR cred IN credentials END AS email FROM loginapp USE KEYS $identityID)[0]
WHERE e.content.identity_id = $identityID AND loginId.id = $identityID
ORDER BY e.timestamp DESC
OFFSET $offset
LIMIT $limit

Hi @Frank_Lyner what operation were you seeing this error from? We don’t report connection time errors (connections happen asynchronously, and will retry until success or shutdown - a connection can fail at more times than just inital startup). I would’ve expected the SDK to be reporting timeout errors rather than internal client errors.

Thank you very much! That’s fantastic! I tried it out.
Without any indexes, the query ran for 2min.
I had already added an index for content.itentity_id and brought it down to 800ms.
With your query it was 760ms.
With your index it came down to… 11.5ms!

Thanks a ton!

The issue hadn’t anything to do with connections. The only issue was that I did a join on two buckets and my user only had access to 1. So, I didn’t expect a client error but a simple authentication error would have been great in troubleshooting.