ClickHouse DB::Exception: File already exists

The "DB::Exception: File already exists" error in ClickHouse is raised when the server attempts to create a file that is already present at the target path. The FILE_ALREADY_EXISTS error code typically surfaces during operations where ClickHouse expects a clean slate -- such as creating a new data part, writing a metadata file, or setting up a new table. The presence of an unexpected file suggests leftover state from a previous operation, a race condition, or external interference with the data directory.

Impact

When this error occurs, you can expect:

• The operation that triggered the error (insert, merge, table creation) will fail
• If it occurs during merges, part consolidation is blocked for the affected table
• Table creation or restore operations may not complete
• Repeated failures can indicate a persistent state problem that needs manual cleanup

Common Causes

1. Leftover files from a previously interrupted operation (crash during merge or insert)
2. Attempting to create a table that was partially created before
3. Restoring data from a backup without first cleaning the target directory
4. Race condition where two processes try to create the same file simultaneously
5. Manual file placement in the ClickHouse data directory conflicting with server operations
6. A bug in an older ClickHouse version leaving orphaned files after failed operations

Troubleshooting and Resolution Steps

1. Identify the conflicting file from the error log:

   grep "File already exists\|FILE_ALREADY_EXISTS" /var/log/clickhouse-server/clickhouse-server.err.log | tail -5
   

2. Check the file's age and origin:

   ls -la /path/to/conflicting/file
   stat /path/to/conflicting/file
   

   If the file is old (from before the last restart), it is likely a leftover from a crashed operation.

3. Check if the part is already active:

   SELECT name, active, modification_time
   FROM system.parts
   WHERE database = 'your_db' AND table = 'your_table'
   AND name = 'conflicting_part_name';
   

   If the part is active, ClickHouse is correctly tracking it. The issue may be a duplicate insert attempt.

4. Remove the conflicting file if it is orphaned: Stop ClickHouse first if the file is in an active data directory:

   sudo systemctl stop clickhouse-server
   sudo rm /path/to/conflicting/file  # Or move to a safe location
   sudo systemctl start clickhouse-server
   

   Alternatively, move the file to the detached directory for later inspection:

   sudo mv /path/to/conflicting/part /var/lib/clickhouse/data/your_db/your_table/detached/
   

5. For table creation conflicts, drop the partial table first:

   DROP TABLE IF EXISTS your_db.your_table;
   

   Then recreate it. If DROP fails, manually remove the metadata and data directories (with ClickHouse stopped).

6. Check for duplicate inserts in replicated tables: ClickHouse deduplicates inserts by block hash. If you see this error during replication, check system.replicas and system.replication_queue for stuck entries.

7. Update ClickHouse if you suspect a bug is causing orphaned files. Check the changelog for relevant fixes.

Best Practices

• Do not manually create files in the ClickHouse data directory
• When restoring from backup, ensure the target directories are clean before copying data
• Use ClickHouse's built-in backup and restore mechanisms rather than raw file copies
• Monitor for interrupted operations (crash recovery logs at startup) and investigate orphaned files
• Keep ClickHouse updated to benefit from fixes for edge cases in file management
• In replicated setups, let the replication mechanism handle part synchronization rather than manually copying files

Frequently Asked Questions

Q: Is it safe to delete the conflicting file?
A: If the file belongs to an inactive or orphaned part (not listed as active in system.parts), it is generally safe to remove. When in doubt, move it to the detached directory instead of deleting it outright.

Q: Why does this happen after a server crash?
A: A crash can interrupt file creation mid-operation. On restart, ClickHouse may try to redo the operation but find partially created files from the previous attempt. The server normally handles this during recovery, but edge cases can leave conflicts.

Q: Can concurrent inserts cause this error?
A: In rare cases, yes. If two inserts produce parts with the same name (which can happen with certain partitioning schemes and identical data), a conflict may arise. Insert deduplication in replicated tables is designed to handle this.

Q: Does this error cause data loss?
A: No data is lost. The operation that triggered the error simply did not complete. Once the conflict is resolved, you can retry the operation.