'SHOW TABLES' and similar commands results in error when run from in -console SQL Shell/SQL CLI

[cvansicklecrdb]

Describe the problem

Several reports of being unable to run SHOW TABLES; (and similar commands) across several clusters in the in-console SQL shell. We have observed this behavior in the SQL CLI as well. Testing so far has show that in-situ shell is not effected.

Example error output:

show tables from search_prod;

ERROR: executing stmt 1: run-query-via-api: read-roles: batch timestamp 1736961131.075117398,0 must be after replica GC threshold 1736964071.150720255,0 (r8: /Table/{4-5})
SQLSTATE: XXUUU

Again, this doesn’t repro in the in-situ shell AFAICT.

To Reproduce
It seems like there are environmental aspects that need to be in place for the query to fail. Those are unknown as of yet, we do have live reproduction available internally per request.

On an affected cluster however:

1. Log in to the Admin Console
2. Go to the SQL SHELL
3. run SHOW DATABASES;
4. find a database and run SHOW TABLES FROM <database>
5. See error Expected behavior
   *Note: Even on affected clusters, this won't always fail. Usually waiting at least 5 mins will cause it to start to fail.

Additional data / screenshots
The error seems to indicate that the batch timestamp is earlier than the GC threshold - given the timestamps provided that is true. However the anomaly is that the batch timestamp is also (in our testing's case) 2 days older than the current time. For example, in one test case, we were running the statement on the 18th, but the batch timestamp was from the 16th and the GC Replica Threshold was on the 18th.

If a node in your cluster encountered a fatal error, supply the contents of the
log directories (at minimum of the affected node(s), but preferably all nodes).

Environment:

• CockroachDB version v24.2.8
• Host machines

1. AWS 4 vCPU
2. 1280 GiB
3. 16 GiB (memory)
4. 16000 IOPS
5. m6i.xlarge

• Client app:
  known failures in:

1. In-console SQL Shell
2. SQL CLI
3. dbbeaver

Jira issue: CRDB-46693

[blathers-crl]

Hi @cvansicklecrdb, please add branch-* labels to identify which branch(es) this C-bug affects.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[rafiss]

I made a PR that will address this error, and it will be backported: #139532

In the meantime, the way to workaround this issue is to perform an operation that causes a refresh of the role cache. Running a command like BEGIN; CREATE USER tmp; DROP USER tmp; COMMIT; (creating and dropping a user in the same transaction) would cause the cache to refresh and will result in CRDB picking a newer timestamp that is after the GC threshold.