ClickHouse DB::Exception: Incorrect file name

The "DB::Exception: Incorrect file name" error occurs when ClickHouse encounters a file name that does not meet its naming requirements. The error code INCORRECT_FILE_NAME indicates that a data file, table name, database name, or partition identifier contains invalid characters, is too long, or uses a reserved format. ClickHouse maps many objects to filesystem paths, so naming constraints are tighter than in some other databases.

Impact

The operation that triggered the error fails. This could be a CREATE TABLE, INSERT (when generating new data parts), or an ATTACH operation. Existing data is not affected, but the offending operation cannot proceed until the naming issue is resolved.

Common Causes

1. Special characters in table or database names -- characters like /, \, .., null bytes, or non-ASCII characters in names that get mapped to filesystem paths.
2. Overly long names -- table or partition names that exceed the filesystem's maximum path length.
3. Partition key values producing invalid path components -- a partition expression that results in characters ClickHouse cannot use in directory names.
4. Manually placed files in ClickHouse data directories -- files with unexpected names in the data path that ClickHouse tries to interpret.
5. Corrupt part names -- data part directories with malformed names after a crash or manual intervention.

Troubleshooting and Resolution Steps

1. Read the full error message. ClickHouse typically includes the problematic file name:

   DB::Exception: Incorrect file name: some/bad/name
   

2. If the issue is a table or database name, rename using valid characters. Stick to alphanumeric characters and underscores:

   -- Instead of this
   CREATE TABLE `my-table!` (...) ENGINE = MergeTree() ORDER BY id;
   
   -- Use this
   CREATE TABLE my_table (...) ENGINE = MergeTree() ORDER BY id;
   

3. Check partition key expressions. If your partition key generates values with special characters, adjust the expression:

   -- This could produce problematic path components depending on the data
   PARTITION BY toString(some_column)
   
   -- Safer approach
   PARTITION BY toYYYYMM(event_date)
   

4. Inspect the data directory for problematic files:

   ls -la /var/lib/clickhouse/data/my_database/my_table/
   

   Look for directories or files with unusual names, very long names, or special characters.

5. Remove or rename offending files on disk if they were placed manually or result from corruption:

   # After detaching the table
   mv /var/lib/clickhouse/data/my_database/my_table/bad_part_name /tmp/
   

6. If the issue is with an ATTACH operation, verify the part directory names match the expected format (all_X_X_X or partition_X_X_X):

   ls /var/lib/clickhouse/data/my_database/my_table/detached/
   

Best Practices

• Use only alphanumeric characters, underscores, and dots in table and database names.
• Design partition keys that produce short, filesystem-safe values (dates, integers, short hashes).
• Avoid placing files manually in ClickHouse data directories -- let ClickHouse manage its own filesystem layout.
• Test table creation and data insertion in a staging environment before production deployment.
• If your application generates table names dynamically, sanitize them to remove special characters and enforce length limits.

Frequently Asked Questions

Q: What characters are allowed in ClickHouse table names?
A: Table names can contain letters, digits, underscores, and dots. If you use backtick quoting, some additional characters are allowed, but it is best to stick to simple alphanumeric names and underscores for maximum compatibility.

Q: Can partition values cause this error?
A: Yes. Partition key values are used in directory names on disk. If your partition expression produces values with characters that are invalid in file paths, you will see this error during INSERT.

Q: How long can a table name be?
A: There is no strict character limit in ClickHouse itself, but the full path (data directory + database name + table name + part name) must fit within the operating system's maximum path length (typically 255 characters per component and 4096 characters total on Linux).

Q: I see this error after restoring a backup. What should I do?
A: The backup may contain files with names that were valid on the source system but not on the target (e.g., different filesystem or OS). Inspect the data directory for files with unusual names and rename or remove them as needed.