Debugging Blocked ClickHouse DDL with ON CLUSTER

The symptom

A migration run was adding a column to a table replicated across three nodes, via ALTER TABLE ... ON CLUSTER. The query never finished on the client side: timeout after several minutes. Looking at the metrics, one of the three nodes showed a growing replication lag, and the others were silently waiting.

How ON CLUSTER works

When you run a DDL statement with ON CLUSTER <cluster_name>, ClickHouse doesn't execute it directly: it pushes an entry into a distributed task queue, stored in ClickHouse Keeper (or ZooKeeper depending on the configuration), under the path /clickhouse/task_queue/ddl (the exact path depends on the distributed_ddl.path config). Every node in the cluster watches this queue and executes the DDL locally as soon as it sees it, then marks its status (finished or error) in a sub-node dedicated to that host.

The node that initiated the query waits for all hosts in the cluster to confirm execution, up to distributed_ddl_task_timeout (180 seconds by default before ClickHouse 22.x, -1, meaning an infinite wait, in some older configurations we'd inherited). If even a single node never responds, the entire query stays blocked until the timeout, or indefinitely.

Where to look to diagnose it

Three places to check, in order:

-- 1. État de la tâche DDL elle-même : quels hosts ont répondu, lesquels pas
SELECT host, port, status, exception_code
FROM system.distributed_ddl_queue
WHERE entry = 'query-0000012345'
ORDER BY host;

-- 2. Le nœud "manquant" a-t-il du retard de réplication ?
SELECT database, table, replica_is_active, absolute_delay
FROM system.replicas
WHERE absolute_delay > 60
ORDER BY absolute_delay DESC;

And as a last resort, the ClickHouse Keeper logs (/var/log/clickhouse-keeper/clickhouse-keeper.log on our hosts) to check there's no quorum or connectivity issue between nodes: a Keeper that loses quorum silently blocks the entire coordination mechanism.

Cases we've hit

• Node unreachable during execution. The most common case: a node in the middle of a restart (deployment, upgrade) right when the DDL is launched. It never sees the queue entry until it's reconnected to Keeper.
• Replication timeout. A node that's alive but far behind on replication (often after a large partition purge) takes a disproportionate amount of time to process the DDL, because it's queued behind replication operations already in progress.

How to unblock it

1. Identify the blocking node via system.distributed_ddl_queue (empty or missing status for that host).
2. Check whether it's simply lagging or actually dead: system.replicas.replica_is_active and absolute_delay. If the node responds to normal queries, waiting is usually enough. The task will eventually be processed. If it's down, it needs to be restarted.
3. If the node won't come back in time (extended outage, instance replacement), you can manually delete the task entry for that host in Keeper to unblock the other nodes. Use this only as a last resort, and only after confirming the schema on the remaining nodes is consistent, since it leaves the faulty node out of sync until it comes back (you'll need to replay the DDL on it manually, or let it resync via a DETACH/ATTACH of the table if the divergence is too large).
4. Post-incident check: query system.distributed_ddl_queue on the entry in question to confirm every host shows status = 'Finished', and compare the table schema (DESCRIBE TABLE) across each node.

How to avoid it next time

• Never run an ON CLUSTER DDL during a deployment or planned restart window. We added a cluster health check (every node replica_is_active = 1 and absolute_delay close to zero) before any schema migration in our CI pipeline.
• Lower distributed_ddl_task_timeout to a sane value (we moved to 300s) rather than leaving an infinite wait that hides the problem instead of failing it cleanly.
• Monitor system.distributed_ddl_queue with an alert on entries whose status stays incomplete for more than a few minutes: this lets you react before it blocks a downstream deployment.