ClickHouse DB::Exception: Potentially broken data part

Q: How can I tell if the corruption is caused by disk hardware?

Check system logs ( dmesg ) for I/O errors and use smartctl to examine disk SMART attributes. Recurring broken parts on the same disk are a strong indicator of hardware failure.

The "DB::Exception: Potentially broken data part" error in ClickHouse indicates that the server has detected a data part that may be corrupt. The POTENTIALLY_BROKEN_DATA_PART error code is raised during part loading, merges, or queries when checksums, file sizes, or internal consistency checks suggest that a part on disk is damaged or incomplete.

Impact

When a data part is flagged as potentially broken, ClickHouse may refuse to load or query it. This can cause SELECT queries to fail if they need data from the affected part, and merges involving that part will be skipped. In replicated tables, the replica holding the broken part will attempt to fetch a healthy copy from another replica. If no healthy replica exists, the data in that part may be temporarily or permanently inaccessible until the issue is resolved.

Common Causes

Disk corruption or bad sectors on the storage device holding ClickHouse data
Incomplete writes due to a power failure, OS crash, or forced server termination during a merge or insert
Filesystem issues such as a full disk, corrupted filesystem journal, or storage controller errors
A bug in a previous ClickHouse version that produced malformed parts
Manual modification or deletion of files inside the ClickHouse data directory
Network storage (NFS, EBS) returning corrupt data due to network issues or volume detachment
Out-of-memory killer terminating ClickHouse during a write operation

Troubleshooting and Resolution Steps

Identify the broken part from the error message or by checking the detached parts directory:

SELECT database, table, name, reason
FROM system.detached_parts
WHERE reason LIKE '%broken%';

Check the server log for details about why the part was flagged:

grep -i 'broken\|corrupt\|checksum' /var/log/clickhouse-server/clickhouse-server.log | tail -30

For replicated tables, ClickHouse can automatically restore the broken part from another replica. Verify replication status:

SELECT database, table, is_leader, active_replicas, queue_size, last_queue_update
FROM system.replicas
WHERE table = 'your_table';

For replicated tables, ClickHouse re-fetches a healthy copy of a broken part from another replica automatically. To force the replica to reconcile with the replication queue and pull any missing parts immediately, run:
```
SYSTEM SYNC REPLICA your_database.your_table;
```
(SYSTEM RESTORE REPLICA is a different command, used only to rebuild a replica's ClickHouse Keeper metadata when that metadata has been lost and the table is in read-only mode; it is not the mechanism for re-fetching an individual broken part.)
For non-replicated tables, check if you have a backup. If so, restore the affected partition:
```
ALTER TABLE your_table DROP PARTITION 'affected_partition';
-- Then restore from backup
```
If no backup or replica is available, you can attempt to drop the broken part and accept the data loss:
```
ALTER TABLE your_table DROP DETACHED PART 'part_name' SETTINGS allow_drop_detached = 1;
```

Run a filesystem check on the underlying storage to rule out hardware issues:

sudo dmesg | grep -i 'error\|fault\|bad'
smartctl -a /dev/sda  # Check disk health

After recovery, verify table integrity:
```
CHECK TABLE your_database.your_table;
```

Best Practices

Use replicated MergeTree tables so that broken parts can be automatically recovered from other replicas.
Monitor disk health with SMART tools and replace drives showing signs of degradation.
Ensure adequate UPS protection and filesystem journaling to prevent corruption from power failures.
Keep regular backups using clickhouse-backup or ClickHouse's built-in BACKUP command.
Avoid manually modifying files inside the ClickHouse data directory.
Monitor the system.detached_parts table for broken parts as part of your operational alerting.
Use checksums on network storage volumes to detect silent data corruption.

Frequently Asked Questions

Q: Will ClickHouse automatically fix broken parts on replicated tables?
A: Yes, for ReplicatedMergeTree tables, ClickHouse detaches the broken part and attempts to fetch a healthy copy from another replica automatically. This process happens in the background without manual intervention.

Q: Can I lose data from a broken part?
A: If the table is replicated and at least one other replica has a healthy copy of the part, no data is lost. For non-replicated tables without backups, the data in the broken part may be unrecoverable.

Q: How can I tell if the corruption is caused by disk hardware?
A: Check system logs (dmesg) for I/O errors and use smartctl to examine disk SMART attributes. Recurring broken parts on the same disk are a strong indicator of hardware failure.