ClickHouse DB::Exception: Cannot parse YAML configuration

The "DB::Exception: Cannot parse YAML" error in ClickHouse occurs when the server attempts to load a YAML configuration file and encounters a syntax error. The CANNOT_PARSE_YAML error code indicates that the YAML parser rejected the file contents. ClickHouse supports YAML as an alternative configuration format (alongside the traditional XML), and this error surfaces when a .yaml or .yml config file is malformed.

Impact

Since this error relates to server configuration, the consequences can be severe:

• ClickHouse may fail to start entirely if the main configuration file is malformed.
• If the error is in a supplementary config file (e.g., users, profiles, or dictionaries), the server may start but without the affected configuration applied.
• Configuration changes that introduce YAML syntax errors can take a running server down on the next restart.
• Dictionary reloads referencing YAML sources will fail, leaving stale data in the dictionary.

Common Causes

1. Incorrect indentation -- YAML is whitespace-sensitive. Mixing tabs and spaces or using the wrong indentation level breaks parsing.
2. Unquoted special characters -- values containing colons, hashes, or curly braces that YAML interprets as syntax elements (e.g., password: my:pass needs quoting).
3. Tabs instead of spaces -- YAML strictly forbids tabs for indentation. Even one tab character causes a parse failure.
4. Mismatched quotes -- opening a string with a single or double quote without properly closing it.
5. Invalid YAML structure -- using list syntax where a mapping is expected, or vice versa.
6. Encoding issues -- BOM characters or non-UTF-8 encoding in the file.
7. Copy-paste artifacts -- smart quotes, em-dashes, or other Unicode characters introduced by word processors or rich-text editors.

Troubleshooting and Resolution Steps

1. Validate the YAML file with a linter before deploying:

   python3 -c "import yaml; yaml.safe_load(open('/etc/clickhouse-server/config.d/my_config.yaml'))"
   

   Or use an online YAML validator.

2. Check for tabs. Find and replace any tab characters:

   grep -P '\t' /etc/clickhouse-server/config.d/my_config.yaml
   

   Replace tabs with spaces:

   sed -i 's/\t/    /g' /etc/clickhouse-server/config.d/my_config.yaml
   

3. Quote values with special characters. If a value contains :, #, {, }, [, ], or starts with * or &, wrap it in quotes:

   # Wrong
   password: my:secret#pass
   
   # Correct
   password: "my:secret#pass"
   

4. Verify indentation consistency. Use a text editor that shows whitespace characters to confirm everything uses spaces (typically 2 or 4 per level).

5. Check the ClickHouse server log for the specific parse error location:

   journalctl -u clickhouse-server | tail -50
   

   The error usually includes a line number and column.

6. Test configuration changes before restarting. Use a YAML linter in CI/CD or as a pre-deployment check.

7. Revert to the last known good configuration if the server will not start:

   # Restore from backup
   cp /etc/clickhouse-server/config.d/my_config.yaml.bak /etc/clickhouse-server/config.d/my_config.yaml
   systemctl restart clickhouse-server
   

Best Practices

• Always validate YAML files with a linter before deploying to ClickHouse servers.
• Use a code editor with YAML syntax highlighting and whitespace visualization when editing config files.
• Never use tabs in YAML files. Configure your editor to insert spaces when you press the Tab key.
• Quote all string values that contain special YAML characters.
• Keep configuration changes in version control and use CI checks to validate YAML syntax.
• Test configuration changes on a staging server before applying to production.

Frequently Asked Questions

Q: Does ClickHouse support both XML and YAML configuration?
A: Yes. ClickHouse supports both formats. YAML files are placed in the same config directories (e.g., /etc/clickhouse-server/config.d/) and are automatically detected by their .yaml or .yml extension.

Q: Can I mix XML and YAML config files?
A: Yes. You can have some configuration in XML and some in YAML within the same config.d directory. ClickHouse merges all configuration files regardless of format.

Q: The error shows a line number. How do I find the exact issue?
A: Open the file in an editor and go to the reported line. Common issues at that line include incorrect indentation, unquoted special characters, or tab characters. A YAML linter will give a more descriptive error message.

Q: My YAML file was working before. What could have changed?
A: Check for recent edits, especially ones done via copy-paste from web pages or documents (which can introduce smart quotes or hidden characters). Also check if a text editor auto-converted spaces to tabs.

Q: Is there a way to validate ClickHouse YAML config without starting the server?
A: While ClickHouse does not have a dedicated config validation command, you can validate the YAML syntax with any YAML parser (e.g., Python's yaml.safe_load). For semantic validation, you would need to attempt a server start, ideally on a test instance.