ClickHouse DB::Exception: Cannot assign ALTER

The "DB::Exception: Cannot assign ALTER" error in ClickHouse occurs when an ALTER TABLE statement cannot be executed. This is typically caused by the table being in readonly mode, conflicting DDL operations, or the replica not being the leader. The error code is CANNOT_ASSIGN_ALTER.

Impact

The ALTER operation is rejected, and no schema changes or mutations are applied. This can block deployments, schema migrations, and data management workflows. Depending on the environment, this may require manual intervention to resolve the underlying blocker.

Common Causes

1. Table is in readonly mode -- replicated tables become readonly when they lose their ZooKeeper/ClickHouse Keeper connection, preventing any DDL operations.
2. Another ALTER is already in progress -- certain ALTER operations take metadata locks that prevent concurrent modifications.
3. Replica is not the leader -- in some cases, only the leader replica can initiate ALTER operations on replicated tables.
4. ZooKeeper/Keeper unavailability -- replicated DDL requires coordination through the keeper, and if it is down, ALTERs cannot be assigned.
5. Ongoing mutations blocking the ALTER -- active mutations can conflict with certain schema changes.

Troubleshooting and Resolution Steps

1. Check the table's readonly and leadership status:

   SELECT database, table, is_readonly, is_leader, zookeeper_exception
   FROM system.replicas
   WHERE table = 'your_table';
   

2. Check for ongoing mutations and ALTER operations:

   -- Active mutations
   SELECT * FROM system.mutations
   WHERE database = 'default' AND table = 'your_table' AND is_done = 0;
   
   -- Recent DDL queries
   SELECT query, status, exception FROM system.query_log
   WHERE query LIKE 'ALTER TABLE%your_table%' AND type = 'QueryFinish'
   ORDER BY event_time DESC LIMIT 10;
   

3. Verify ZooKeeper/Keeper connectivity:

   SELECT * FROM system.zookeeper WHERE path = '/';
   

   If this fails, the keeper connection is down. Resolve the keeper issue before retrying.

4. Kill conflicting queries if safe to do so:

   -- Find blocking ALTER queries
   SELECT query_id, query, elapsed FROM system.processes
   WHERE query LIKE 'ALTER%' AND query LIKE '%your_table%';
   
   -- Kill if appropriate
   KILL QUERY WHERE query_id = 'blocking_query_id';
   

5. Wait and retry. Some ALTER operations are queued and will execute once the blocker completes:

   -- Check mutations queue
   SELECT command, create_time, is_done FROM system.mutations
   WHERE table = 'your_table' ORDER BY create_time DESC;
   

6. For replicated tables, ensure the DDL is issued on a connected replica:

   SELECT host_name, is_readonly FROM system.replicas WHERE table = 'your_table';
   

Best Practices

• Monitor ZooKeeper/Keeper health continuously to prevent replicated tables from entering readonly mode unexpectedly.
• Avoid running multiple concurrent ALTER operations on the same table.
• Schedule schema migrations during low-traffic periods to minimize conflicts with ongoing queries and mutations.
• Use ALTER TABLE ... ON CLUSTER for distributed environments to ensure consistent schema changes across replicas.
• Implement retry logic with backoff in migration scripts that may encounter transient lock conflicts.

Frequently Asked Questions

Q: Can I run ALTER on a non-leader replica?
A: For replicated tables, ALTER statements are coordinated through ZooKeeper/Keeper and can generally be submitted to any connected replica. However, the replica must not be in readonly mode. The leader handles merge assignment, but ALTERs are typically accepted by any writable replica.

Q: How long do ALTER metadata locks last?
A: Metadata locks for ALTER operations are typically short-lived for schema changes (ADD COLUMN, DROP COLUMN). Mutations (ALTER UPDATE, ALTER DELETE) run asynchronously and do not hold metadata locks for their entire duration.

Q: Will killing a stuck ALTER cause data corruption?
A: Killing an ALTER that modifies schema (ADD/DROP COLUMN) before it completes should not cause corruption, as ClickHouse uses atomic metadata changes. Killing a mutation in progress is also safe -- it will be marked as failed and can be retried.

Q: Does readonly mode affect all operations or just ALTER?
A: Readonly mode on a replicated table blocks all write operations: INSERTs, ALTERs, OPTIMIZE, and mutations. SELECTs continue to work against the local data.