ClickHouse DB::Exception: Query was cancelled by client

The "DB::Exception: Query was cancelled by client" error in ClickHouse appears when a running query is explicitly terminated by the client that issued it. The error code for this exception is QUERY_WAS_CANCELLED_BY_CLIENT. This is not a server-side failure -- it indicates the client sent a cancellation signal before the query completed.

Impact

When this error occurs, the query stops executing and partial results are discarded. Typical consequences include:

• Application-level errors if the cancellation was unintentional, such as a user navigating away from a dashboard
• Wasted server resources for queries that ran for a significant time before being cancelled
• Misleading error logs if automated retry logic treats this as a transient server failure

Common Causes

1. User-initiated cancellation -- A user presses Ctrl+C in clickhouse-client or clicks a cancel button in a GUI tool, sending a cancellation request to the server.
2. Client-side timeout -- The client application has its own query timeout configured (separate from ClickHouse server-side max_execution_time), and it cancels the query when that timeout expires.
3. Connection pool recycling -- Application connection pool managers may terminate long-running queries when reclaiming connections.
4. Load balancer or proxy timeout -- A reverse proxy (e.g., nginx, HAProxy) sitting between the client and ClickHouse closes the connection, and the client library interprets this as a cancellation.
5. Application shutdown -- The application process is restarted or stopped while queries are still in flight, triggering cleanup that cancels outstanding queries.
6. Driver-level cancellation -- Some ClickHouse client drivers (JDBC, Go, Python) support context cancellation or statement timeouts that automatically cancel queries.

Troubleshooting and Resolution Steps

1. Check whether the cancellation was intentional: Review your application logs to determine if the cancellation was triggered deliberately. In many cases this error is expected behavior (e.g., a user cancelled a slow dashboard query).

2. Inspect the query log for timing details:

   SELECT
       query_id,
       query,
       elapsed,
       exception_code,
       exception
   FROM system.query_log
   WHERE exception_code = 394  -- QUERY_WAS_CANCELLED_BY_CLIENT
   ORDER BY event_time DESC
   LIMIT 20;
   

   Look at the elapsed column to understand how long the query ran before cancellation.

3. Review client-side timeout settings: If you are using a client driver, check for timeout configurations:

   # Python clickhouse-driver example
   client = Client('localhost', send_receive_timeout=300)
   

   // JDBC example
   ClickHouseProperties props = new ClickHouseProperties();
   props.setSocketTimeout(300000); // milliseconds
   

4. Check for proxy or load balancer timeouts: If a reverse proxy sits in front of ClickHouse, verify its timeout settings:

   # nginx example
   proxy_read_timeout 300s;
   proxy_send_timeout 300s;
   

5. Optimize slow queries to avoid timeouts: If queries are being cancelled because they take too long, focus on query optimization:

   -- Check query execution plan
   EXPLAIN PIPELINE SELECT ...;
   
   -- Look at query profile
   SET log_queries = 1;
   SET log_query_threads = 1;
   

6. Distinguish from server-side cancellation: Server-side cancellations (via max_execution_time or KILL QUERY) produce different error codes. If you see QUERY_WAS_CANCELLED_BY_CLIENT specifically, the cancellation originated from the client side.

Best Practices

• Set appropriate client-side timeouts that account for your longest-running queries, rather than relying on defaults that may be too short.
• Use asynchronous query execution for long-running analytical queries so that client disconnections do not automatically cancel them.
• Log and monitor this error code separately from server errors, since it typically reflects client behavior rather than a server problem.
• If using a load balancer, align its timeout settings with your expected query durations.
• Consider using INSERT INTO ... SELECT for heavy transformations, which run server-side and are not affected by client disconnections.
• Implement proper connection management in your application to avoid accidental cancellations during connection pool cleanup.

Frequently Asked Questions

Q: Is QUERY_WAS_CANCELLED_BY_CLIENT the same as KILL QUERY?
A: No. KILL QUERY is a server-side operation initiated via SQL and produces a different exception. QUERY_WAS_CANCELLED_BY_CLIENT specifically means the client that submitted the query sent a cancellation signal.

Q: Does ClickHouse clean up resources immediately when a query is cancelled by the client?
A: ClickHouse will stop processing the query and release resources, but some cleanup (such as removing temporary data) may happen asynchronously. The cancellation is not instantaneous -- the server needs to reach a cancellation checkpoint in the execution pipeline.

Q: Can I prevent users from cancelling queries?
A: There is no server-side setting to ignore client cancellation requests. If you need queries to run to completion regardless of client behavior, consider submitting them as background tasks using INSERT INTO ... SELECT or by running them through a separate process.

Q: Why do I see this error in my logs even though nobody cancelled anything?
A: The most common cause is an implicit cancellation -- a client-side timeout expired, a connection was dropped by a proxy, or the application process was recycled. Check your full network path and client configuration for timeout settings.