ClickHouse DB::Exception: No such replica

The DB::Exception: No such replica error (code NO_SUCH_REPLICA) occurs when ClickHouse references a replica by name but cannot find it in ZooKeeper's replica registry for the given table. The replica may have been decommissioned, its ZooKeeper path may have been cleaned up, or the name may be misspelled.

Impact

Operations that target the missing replica will fail. This includes commands like SYSTEM DROP REPLICA, fetching parts from a specific replica, or administrative queries that reference the replica by name. Other replicas continue to function normally; only operations explicitly referencing the nonexistent replica are affected.

Common Causes

1. Replica was already removed from ZooKeeper by a previous SYSTEM DROP REPLICA command or manual cleanup.
2. Typo in the replica name when issuing an administrative command.
3. The replica was never created -- the CREATE TABLE statement on that node failed or was never executed.
4. ZooKeeper data loss -- a ZooKeeper restore from an old snapshot may not include recently added replicas.
5. Macro mismatch -- the {replica} macro resolves to a different value than expected.

Troubleshooting and Resolution Steps

1. List existing replicas in ZooKeeper

   SELECT name
   FROM system.zookeeper
   WHERE path = '/clickhouse/tables/01/my_table/replicas';
   

   Compare the output against the replica name you are trying to reference.

2. Verify the replica macro on the target node

   SELECT * FROM system.macros;
   

   Ensure the {replica} value matches what is registered in ZooKeeper.

3. Check if the table exists on the target node Connect to the node in question and verify:

   SHOW TABLES FROM db LIKE 'my_table';
   

   If the table does not exist, create it with the appropriate ReplicatedMergeTree engine definition.

4. Use the correct replica name in commands When running SYSTEM DROP REPLICA, use the exact name from the ZooKeeper listing:

   SYSTEM DROP REPLICA 'exact_replica_name' FROM TABLE db.my_table;
   

5. Recreate the replica if it was removed prematurely On the node that should host the replica, create the table with the correct replica name. ClickHouse will register a new replica node in ZooKeeper and begin fetching parts.

6. Restore ZooKeeper if metadata was lost If ZooKeeper was restored from a backup and is missing replicas:

   -- On each affected node, restart replication
   SYSTEM RESTART REPLICA db.my_table;
   

   ClickHouse will re-register itself if the local table still exists.

Best Practices

• Always verify the exact replica name from system.zookeeper before running administrative commands.
• Keep an inventory of all replicas and their assigned names for each table.
• Use consistent naming conventions (e.g., hostname-based) to make replica names predictable.
• When decommissioning a node, run SYSTEM DROP REPLICA on a surviving replica to clean up the metadata properly.
• Avoid manually editing ZooKeeper paths -- let ClickHouse manage its own metadata.

Frequently Asked Questions

Q: Can this error appear during normal query execution?
A: It is uncommon during regular SELECT or INSERT operations. It typically surfaces during administrative commands or when the replication system references a peer that no longer exists.

Q: What happens if I ignore this error?
A: If it occurs in an administrative command, the command simply fails and nothing changes. If it appears in the replication queue, the affected entry will retry and fail repeatedly until the issue is addressed.

Q: Is it safe to remove a replica from ZooKeeper if the node is permanently gone?
A: Yes. Use SYSTEM DROP REPLICA 'name' FROM TABLE db.my_table from any surviving replica. This cleanly removes the metadata without affecting other replicas.

Q: Will creating a new table on a node with the same replica name fix this?
A: Yes, as long as the ZooKeeper path does not already have a conflicting entry. If the old entry was properly cleaned up, the new table creation will register a fresh replica.