ClickHouse DB::Exception: Query was cancelled

The "DB::Exception: Query was cancelled" error in ClickHouse indicates that a running query was terminated server-side before it could complete. The QUERY_WAS_CANCELLED error code (code 394) is raised when a query is stopped by a KILL QUERY command, a timeout setting such as max_execution_time, or when a resource limit is exceeded with overflow_mode = 'throw'. This is distinct from the separate QUERY_WAS_CANCELLED_BY_CLIENT error (code 735), which is raised when the client application itself initiates the cancellation (for example, by closing the connection or calling a cancel method).

Impact

The cancelled query produces no results. For SELECT queries, any partial results are discarded. For INSERT operations, uncommitted data is rolled back. For ALTER or mutation operations, work already committed in previous parts remains, but the overall operation is marked as incomplete. Applications relying on the cancelled query will receive an error and need to retry or handle the cancellation gracefully.

Common Causes

1. An administrator or application issued a KILL QUERY command targeting the query
2. The query exceeded max_execution_time and was automatically cancelled
3. A query exceeded max_rows_to_read, max_bytes_to_read, or similar resource limits with overflow_mode = 'throw'
4. A distributed query was cancelled because a participating node failed
5. The connection pool or load balancer timed out and closed the connection

Troubleshooting and Resolution Steps

1. Check the query log to find details about the cancelled query:

   SELECT query_id, user, query, exception, event_time,
          query_duration_ms, read_rows, read_bytes
   FROM system.query_log
   WHERE type = 'ExceptionWhileProcessing'
     AND exception_code = 394
   ORDER BY event_time DESC
   LIMIT 10;
   

2. Determine if the query was killed manually by checking for KILL QUERY statements:

   SELECT event_time, user, query
   FROM system.query_log
   WHERE query LIKE 'KILL QUERY%'
   ORDER BY event_time DESC
   LIMIT 10;
   

3. Check timeout settings if the query was cancelled automatically:

   SELECT name, value
   FROM system.settings
   WHERE name IN ('max_execution_time', 'max_rows_to_read',
                   'max_bytes_to_read', 'timeout_before_checking_execution_speed');
   

4. If the query is legitimately long-running, increase the timeout:

   SET max_execution_time = 600;  -- 10 minutes
   SELECT ...;
   

5. For client-side timeout issues, increase the client timeout to match the expected query duration. For HTTP clients:

   curl --max-time 600 'http://localhost:8123/?query=SELECT...'
   

6. To prevent accidental cancellation, assign a unique query ID so you can track and manage the query. The query ID is not a session setting; pass it with the query instead. Over HTTP, use the query_id parameter:

   curl 'http://localhost:8123/?query_id=my-important-query-001' \
     --data-binary 'SELECT ...'
   

   In clickhouse-client, use the --query_id option:

   clickhouse-client --query_id 'my-important-query-001' --query 'SELECT ...'
   

7. If distributed queries are being cancelled due to slow replicas, check replica health:

   SELECT * FROM system.clusters;
   SELECT host_name, errors_count, estimated_recovery_time
   FROM system.clusters
   WHERE cluster = 'your_cluster';
   

Best Practices

• Set appropriate max_execution_time per user profile: short for ad-hoc users, longer for ETL and batch jobs.
• Use query IDs consistently so that cancelled queries can be traced back to the originating application or user.
• Implement retry logic with backoff in applications that may encounter cancelled queries due to transient issues.
• Monitor frequently cancelled queries in system.query_log to identify queries that need optimization.
• Set client-side timeouts that are at least as long as the server-side max_execution_time to avoid premature disconnections.
• Use KILL QUERY ... SYNC when you need to ensure the query is fully stopped before proceeding.

Frequently Asked Questions

Q: What is the difference between QUERY_WAS_CANCELLED and QUERY_WAS_CANCELLED_BY_CLIENT?
A: QUERY_WAS_CANCELLED (code 394) is a server-side cancellation triggered by a KILL QUERY command, a timeout such as max_execution_time, or an exceeded resource limit. QUERY_WAS_CANCELLED_BY_CLIENT (code 735) is raised when the client application itself initiates the cancellation, for example by closing the connection or invoking a cancel call. Both stop the query, but they indicate different origins of the cancellation.

Q: Is there a difference between QUERY_WAS_CANCELLED and ABORTED?
A: Yes. QUERY_WAS_CANCELLED specifically indicates that the query was stopped by a server-side cancellation action (KILL QUERY, timeout, or resource limit). ABORTED (code 236) is a broader error that can also cover server shutdowns and other interruption scenarios.

Q: Can I cancel a query without being an admin?
A: Users can cancel their own queries using KILL QUERY WHERE query_id = 'my_query'. To cancel other users' queries, you need the KILL QUERY privilege.

Q: What happens to INSERT data when a query is cancelled?
A: For MergeTree tables, inserts are atomic at the part level. If the insert was cancelled before any parts were committed, no data is visible. If some parts were already committed (in a large multi-block insert), those parts remain and the insert is partially complete.