ClickHouse DB::Exception: Session not found

The SESSION_NOT_FOUND error appears when a query references a session ID that ClickHouse cannot locate. This typically means the session has expired due to inactivity, the session ID was never created on the target server, or the request was routed to a different node than the one holding the session. The full message usually reads something like DB::Exception: Session not found.

Impact

When this error fires, the current query fails and any session state -- including temporary tables, session-level settings, and in-progress transactions -- becomes inaccessible. Applications that chain multiple queries across a session will break mid-workflow. If sessions expire frequently, users may experience intermittent failures that are difficult to reproduce.

Common Causes

Session timeout exceeded -- ClickHouse sessions have a configurable timeout (default 60 seconds). If no query is sent within that window, the session is destroyed and subsequent requests referencing it will fail.
Mismatched session IDs -- A typo or programming error that sends a different session ID than the one originally created.
Load balancer routing to a different node -- Sessions are local to a single ClickHouse server. If a load balancer sends a follow-up request to a different node, that node has no record of the session.
Server restart -- Restarting ClickHouse clears all in-memory sessions. Any session IDs from before the restart become invalid.
Maximum session count reached -- If the server hit its session limit, older sessions may have been evicted to make room for new ones.

Troubleshooting and Resolution Steps

Check the session timeout setting: Verify the session_timeout you are using and consider increasing it if your workflow has long pauses between queries:
```
curl "http://localhost:8123/?session_id=my_session&session_timeout=3600" \
  --data-binary "SELECT 1"
```
The maximum allowed session timeout is controlled by the max_session_timeout server setting (default 3600 seconds).
Verify session ID consistency: Ensure that your application sends the exact same session_id value on every request within a workflow. Log the session ID at each step to confirm there are no mismatches.
Configure load balancer sticky sessions: If you use a load balancer, configure session affinity so all requests with the same session ID go to the same ClickHouse node. You can use the session_id query parameter as the affinity key.

Handle session expiry gracefully in application code: Wrap session-dependent operations in retry logic that re-creates the session if a SESSION_NOT_FOUND error is caught:

try:
    client.execute("SELECT * FROM tmp_table", settings={'session_id': sid})
except Exception as e:
    if 'SESSION_NOT_FOUND' in str(e):
        # Re-create session and temporary tables
        setup_session(sid)
        client.execute("SELECT * FROM tmp_table", settings={'session_id': sid})
    else:
        raise

Check server logs after a restart: If the error appeared after a server restart or failover, the root cause is simply that in-memory sessions were lost. Re-establish any required sessions.
Monitor active sessions: You can check currently active sessions in the system.sessions table (available in recent ClickHouse versions) or monitor session-related metrics to detect expiration patterns.

Best Practices

Set session_timeout to a value that matches your actual workflow duration, rather than relying on the default.
Generate unique session IDs using UUIDs or similar schemes to prevent accidental collisions.
Design applications to be resilient to session loss -- avoid storing critical state exclusively in sessions.
Use connection pooling libraries that support session affinity when connecting through a load balancer.
Log session IDs alongside query IDs in your application for easier debugging.

Frequently Asked Questions

Q: Can I extend a session timeout after the session is already created?
A: Yes. Each request that includes the session_id can also pass a new session_timeout value. The timeout resets with each request, so sending any query with the session ID effectively refreshes it.

Q: Is there a way to list all active sessions on a ClickHouse server?
A: In recent versions, you can query system.session_log to see session events. For real-time session data, check server metrics or the system.processes table to see which session IDs have active queries.

Q: Will a session survive a ClickHouse server restart?
A: No. Sessions are stored entirely in memory and are not persisted to disk. A restart, crash, or failover will destroy all active sessions.

Q: What happens to temporary tables when a session expires?
A: They are dropped automatically. Once a session is gone, all temporary tables and session-scoped settings associated with it are cleaned up.