ClickHouse DB::Exception: File doesn't exist

The "DB::Exception: File doesn't exist" error in ClickHouse means the server tried to access a specific file that it expected to find on disk, but the file was not there. This FILE_DOESNT_EXIST error commonly arises when data part files, metadata files, or other internal files have been removed or lost. It is distinct from CANNOT_OPEN_FILE in that ClickHouse explicitly checks for file existence and determines the file is absent before even attempting to open it.

Impact

A missing file can cause a range of disruptions depending on what the file contains:

• If the file is part of a data part, queries against that part will fail
• Missing metadata files can prevent tables from loading at startup
• Mutations or merges that reference the file will be unable to proceed
• In severe cases, entire tables may become inaccessible until the missing files are restored or the parts are detached

Common Causes

1. Manual deletion of files from the ClickHouse data directory
2. Backup or cleanup scripts that inadvertently removed active data files
3. Disk failure causing file loss
4. Incomplete data part download during replication
5. A bug in a previous version of ClickHouse that left orphaned references in metadata
6. Filesystem corruption that removed directory entries without ClickHouse's knowledge
7. Moving ClickHouse data between servers without copying all required files

Troubleshooting and Resolution Steps

1. Identify the missing file from the error log:

   grep "File doesn't exist" /var/log/clickhouse-server/clickhouse-server.err.log | tail -5
   

   Note the full path to understand which table and part are affected.

2. Check if the parent directory and other files in the part exist:

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

   If the entire part directory is missing, the issue is broader than a single file.

3. Look at the system.parts table for clues:

   SELECT name, path, active, rows, bytes_on_disk
   FROM system.parts
   WHERE database = 'your_db' AND table = 'your_table'
   ORDER BY modification_time DESC
   LIMIT 20;
   

4. For replicated tables, let replication heal the part:

   ALTER TABLE your_db.your_table DETACH PART 'affected_part_name';
   

   The replica queue will automatically schedule a download of the missing part from a healthy replica.

5. For non-replicated tables, restore from backup: Copy the missing part from your backup into the detached directory, then attach it:

   cp -r /backup/your_db/your_table/affected_part /var/lib/clickhouse/data/your_db/your_table/detached/
   sudo chown -R clickhouse:clickhouse /var/lib/clickhouse/data/your_db/your_table/detached/affected_part
   

   ALTER TABLE your_db.your_table ATTACH PART 'affected_part';
   

6. If the file is metadata, not a data part: Check the metadata directory:

   ls /var/lib/clickhouse/metadata/your_db/
   

   You may need to recreate the table from a DDL backup or restore the metadata file from a backup.

7. Investigate why the file went missing by reviewing system logs, cron jobs, backup scripts, and disk health.

Best Practices

• Never manually delete files from the ClickHouse data directory -- use SQL commands for all data management
• Implement file-level monitoring or integrity checks on the ClickHouse data directory
• Use ReplicatedMergeTree tables in production for automatic part recovery
• Keep current backups that include both data parts and metadata
• Document and audit any scripts or processes that interact with the ClickHouse data directory
• After migrating data between servers, verify file completeness before starting ClickHouse

Frequently Asked Questions

Q: Can I just ignore the missing file and keep using the table?
A: Queries that don't touch the affected part will still work. However, leaving a broken part in place can cause ongoing errors in logs and may interfere with merges. It is better to detach the broken part and restore it.

Q: Will ClickHouse automatically detect and recover missing files?
A: For replicated tables, yes -- if a part is detected as broken or missing, the replication mechanism will fetch it from another replica. Non-replicated tables require manual intervention.

Q: How do I prevent backup scripts from deleting active files?
A: Use ClickHouse's built-in BACKUP command or clickhouse-backup tool, which understands the data directory structure. Avoid writing custom scripts that directly manipulate files in the data directory.

Q: The error mentions a .bin or .mrk file. What are these?
A: These are column data files (.bin) and mark files (.mrk or .mrk2) that ClickHouse uses to store and index columnar data within each part. Both are required for reading the part successfully.