ClickHouse DB::Exception: Cannot kill query

The "DB::Exception: Cannot kill query" error in ClickHouse occurs when a KILL QUERY or KILL MUTATION statement fails to terminate the targeted operation. The CANNOT_KILL error means ClickHouse was unable to cancel the specified query, either because the query does not exist, the user lacks the necessary permissions, or the query is in a state that prevents cancellation.

Impact

When you cannot kill a problematic query, it will continue consuming resources -- CPU, memory, disk I/O -- until it completes or fails on its own. This can be especially problematic for runaway queries that are blocking other operations, consuming excessive memory, or holding locks. The inability to cancel such queries may require more drastic measures like restarting the ClickHouse server.

Common Causes

The query ID specified in the KILL QUERY statement does not match any running query
The user issuing KILL QUERY does not have permission to kill another user's query
The query already finished or was cancelled before the KILL statement was processed
The query is in a non-cancellable state during certain low-level operations
A race condition where the query completed between listing it and attempting to kill it
The query is running on a different replica or shard in a distributed setup

Troubleshooting and Resolution Steps

List currently running queries to find the correct query ID:

SELECT query_id, user, elapsed, query
FROM system.processes
ORDER BY elapsed DESC;

Attempt to kill the query by ID:

KILL QUERY WHERE query_id = 'your-query-id-here';

Kill by user or query pattern if you do not know the exact query ID:

KILL QUERY WHERE user = 'problematic_user' AND elapsed > 300;
KILL QUERY WHERE query LIKE '%large_table%' SYNC;

Check permissions -- ensure your user has the KILL QUERY privilege:
```
SHOW GRANTS FOR CURRENT_USER;
```
The user must have the KILL QUERY grant, or be an admin user.
Use SYNC mode to wait for confirmation:
```
KILL QUERY WHERE query_id = 'your-query-id' SYNC;
```
The SYNC keyword makes the statement wait until the query is actually terminated.

For mutations, use KILL MUTATION:

SELECT mutation_id, command, is_done
FROM system.mutations
WHERE is_done = 0;

KILL MUTATION WHERE mutation_id = 'mutation_id_here';

As a last resort, restart the ClickHouse server to terminate all running queries:
```
sudo systemctl restart clickhouse-server
```

Best Practices

Use the SYNC keyword with KILL QUERY to confirm that the query was actually terminated.
Grant the KILL QUERY privilege to operations or DBA roles so they can respond quickly to runaway queries.
Set max_execution_time on queries or user profiles to prevent queries from running indefinitely.
Configure max_memory_usage per query to limit the damage a single query can cause.
In distributed setups, remember that you need to kill queries on the specific node where they are running.

Frequently Asked Questions

Q: Why does KILL QUERY return success but the query is still running?
A: Without the SYNC keyword, KILL QUERY only sends a cancellation signal and returns immediately. The query may take some time to respond to the cancellation. Use KILL QUERY ... SYNC to wait for actual termination.

Q: Can I kill queries from other users?
A: Only if you have the KILL QUERY privilege or are an admin user. Regular users can only kill their own queries by default.

Q: What is the difference between KILL QUERY and KILL MUTATION?
A: KILL QUERY targets active SELECT, INSERT, or other queries visible in system.processes. KILL MUTATION targets ongoing ALTER TABLE mutations visible in system.mutations. They are separate commands for separate types of operations.

Q: Can a killed query leave data in an inconsistent state?
A: Killed SELECT queries have no side effects. Killed INSERT queries may leave partial data in some cases, though ClickHouse generally handles this atomically. Killed mutations will stop processing but already-applied changes to parts will remain.