ClickHouse DB::Exception: Cannot open file

The "DB::Exception: Cannot open file" error in ClickHouse is raised when the server attempts to open a file and the operating system returns a failure. Identified by the CANNOT_OPEN_FILE error code, this problem can stem from missing data files, incorrect permissions, or resource exhaustion. Because ClickHouse relies heavily on direct file access for reading table parts and metadata, this error often blocks queries or prevents tables from loading entirely.

Impact

When ClickHouse cannot open a required file, the effects cascade quickly:

• Queries targeting the affected table or partition will fail
• Table loading during server startup may be skipped, leaving the table inaccessible
• Merges and mutations that need the missing or inaccessible file will stall
• If the file belongs to a replicated table, the replica may fall out of sync until the issue is resolved

Common Causes

1. The file was deleted or moved by an external process (manual cleanup, cron job, or misconfigured backup tool)
2. Incorrect ownership or permissions on data files or directories
3. The file descriptor limit is exhausted, so the OS cannot open additional files
4. A corrupted filesystem where directory entries point to missing inodes
5. Storage volume unmounted or disconnected (common with network-attached or cloud storage)
6. SELinux or AppArmor policies blocking access to specific paths

Troubleshooting and Resolution Steps

1. Identify the exact file from the error message: The ClickHouse log will include the full path of the file it failed to open. Check the server error log:

   grep "Cannot open file" /var/log/clickhouse-server/clickhouse-server.err.log | tail -5
   

2. Verify the file exists:

   ls -la /var/lib/clickhouse/data/your_db/your_table/some_part/
   

   If the file is genuinely missing, you may need to restore it from a backup or let replication recover it.

3. Check permissions and ownership:

   stat /var/lib/clickhouse/data/your_db/your_table/
   

   The ClickHouse data directory tree should be owned by the clickhouse user:

   sudo chown -R clickhouse:clickhouse /var/lib/clickhouse/data/your_db/your_table/
   

4. Check file descriptor limits:

   cat /proc/$(pidof clickhouse-server)/limits | grep "open files"
   ls /proc/$(pidof clickhouse-server)/fd | wc -l
   

   Raise the limit if needed in /etc/security/limits.conf or the systemd service file.

5. Inspect storage mount status:

   mount | grep /var/lib/clickhouse
   df -h /var/lib/clickhouse
   

   Remount or reconnect the storage if it has become unavailable.

6. For replicated tables, force re-fetch of the part:

   ALTER TABLE your_db.your_table DETACH PART 'broken_part_name';
   -- The replica will automatically fetch it from another replica
   

7. Check security policies:

   sudo ausearch -m avc -ts recent  # SELinux
   sudo journalctl | grep apparmor  # AppArmor
   

Best Practices

• Never manually delete or move files inside the ClickHouse data directory; use SQL commands like DROP, DETACH, or ALTER TABLE ... DROP PARTITION instead
• Set file descriptor limits to at least 100,000 for production ClickHouse instances
• Use monitoring to detect permission changes or unexpected file deletions
• Keep storage volumes reliably mounted with appropriate fstab or systemd mount configurations
• Maintain regular backups so missing files can be restored quickly
• In replicated setups, ensure at least two healthy replicas so parts can be recovered automatically

Frequently Asked Questions

Q: A specific table won't load after a server restart with this error. What should I do?
A: Check whether the part directory and its files still exist. If they are missing and the table is replicated, ClickHouse will typically recover the part from another replica. For non-replicated tables, restore the missing files from a backup or detach the broken part to allow the rest of the table to load.

Q: Could antivirus software cause this error?
A: Yes. Some antivirus or endpoint protection tools lock files or block access during scanning. Exclude the ClickHouse data directory from real-time scanning to avoid this.

Q: How can I tell if the problem is permissions versus a missing file?
A: The OS error message included in the ClickHouse exception will usually say either "No such file or directory" (ENOENT) or "Permission denied" (EACCES). This tells you which direction to investigate.

Q: Is it safe to change ownership of the entire ClickHouse data directory recursively?
A: Yes, running chown -R clickhouse:clickhouse /var/lib/clickhouse is a standard fix. Just make sure no other service depends on different ownership of files within that tree.