ClickHouse DB::Exception: Too many unexpected data parts

The DB::Exception: Too many unexpected data parts error (code TOO_MANY_UNEXPECTED_DATA_PARTS) indicates that ClickHouse has found a significant number of data parts on the local disk that are not registered in ZooKeeper. This divergence between the local state and the coordination layer suggests a replication inconsistency that needs attention.

Impact

When this error occurs, ClickHouse may refuse to start replication for the affected table or place it in a read-only state. The unexpected parts consume disk space and can interfere with merge operations. If the divergence is severe, the table may become inaccessible until the issue is resolved.

Common Causes

1. Unclean shutdown during a merge or mutation -- parts were created locally but the ZooKeeper transaction that records them did not complete.
2. Manual manipulation of the data directory -- someone copied or moved part directories into the table's data folder without going through ClickHouse's API.
3. Restore from a filesystem snapshot that is newer than the ZooKeeper state.
4. ZooKeeper data loss or rollback -- ZooKeeper was restored from an older snapshot, so it no longer knows about parts that ClickHouse created.
5. Bug or crash during replication that left orphaned parts on disk.

Troubleshooting and Resolution Steps

1. Identify the unexpected parts Check ClickHouse logs for the specific parts flagged as unexpected. You can also compare local parts against what ZooKeeper knows:

   SELECT name, active, rows, bytes_on_disk
   FROM system.parts
   WHERE table = 'my_table' AND database = 'db';
   

2. Check detached parts ClickHouse may automatically detach unexpected parts:

   SELECT * FROM system.detached_parts
   WHERE table = 'my_table';
   

3. Increase the tolerance threshold temporarily If the parts are valid and you want ClickHouse to accept them, increase the threshold:

   <merge_tree>
       <replicated_max_unexpected_parts>100</replicated_max_unexpected_parts>
   </merge_tree>
   

   Restart ClickHouse and let replication reconcile the state.

4. Attach valid detached parts If detached parts contain data you need:

   ALTER TABLE db.my_table ATTACH PART 'part_name';
   

5. Remove truly orphaned parts If the detached parts are junk (e.g., from an interrupted merge), remove them:

   ALTER TABLE db.my_table DROP DETACHED PART 'part_name' SETTINGS allow_drop_detached = 1;
   

   Or remove the directories manually from the detached folder.

6. Re-sync from a healthy replica As a last resort, drop the local table and recreate it. ClickHouse will fetch all parts from other replicas:

   DROP TABLE db.my_table SYNC;
   CREATE TABLE db.my_table (...) ENGINE = ReplicatedMergeTree(...) ...;
   

Best Practices

• Avoid direct filesystem manipulation of ClickHouse data directories; always use SQL commands to manage parts.
• Ensure ZooKeeper or ClickHouse Keeper is backed up in sync with ClickHouse data snapshots.
• Monitor the count of detached parts as an early indicator of replication drift.
• Use graceful shutdown procedures (SYSTEM SHUTDOWN or systemctl stop) to minimize the risk of incomplete transactions.
• After disaster recovery, verify consistency between local parts and ZooKeeper metadata before re-enabling replication.

Frequently Asked Questions

Q: Are the unexpected parts safe to delete?
A: It depends. If they resulted from an interrupted merge, they are likely duplicates of data already present in other parts. If they came from a manual restore, they may contain unique data. Inspect their contents before deleting.

Q: Can I raise the limit permanently?
A: You can, but a high number of unexpected parts usually signals an underlying problem. Fix the root cause rather than masking the symptom with a higher threshold.

Q: Will ClickHouse automatically resolve this if I just restart?
A: Not always. If the number of unexpected parts exceeds the threshold, ClickHouse will refuse to start replication for the table. You need to either remove the parts or raise the threshold.

Q: Does this error affect non-replicated MergeTree tables?
A: No. This error is specific to replicated tables because it involves a comparison between local state and ZooKeeper metadata. Non-replicated MergeTree tables do not use ZooKeeper.