ClickHouse DB::Exception: Excessive element in configuration

The EXCESSIVE_ELEMENT_IN_CONFIG error occurs when ClickHouse finds a duplicate or unexpected element in a configuration section that expects a single value or a specific structure. The error message typically reads DB::Exception: Excessive element 'element_name' in config. This often happens when the same setting is defined multiple times in a section that does not support it, or when config fragments in config.d/ inadvertently create duplicates.

Impact

Depending on where the excessive element is found, ClickHouse may fail to start or may reject a configuration reload. The ambiguity of having duplicate definitions means ClickHouse cannot determine which value to use, so it raises an error rather than silently picking one. If the affected configuration controls storage, networking, or cluster definitions, the server will not be operational until the duplication is resolved.

Common Causes

1. Duplicate elements in a single config file -- The same XML tag appearing twice in a section that only allows one instance.
2. Conflicting config fragments -- Two files in config.d/ both define the same element without using the replace="replace" attribute, causing ClickHouse to see duplicates.
3. Merge behavior misunderstanding -- ClickHouse merges config.d/ fragments into the main config. Without the replace attribute, elements are merged additively, which can create duplicates.
4. Leftover config files from upgrades -- Package upgrades may leave behind .rpmsave or .dpkg-dist files that get loaded if they have an .xml extension.
5. Copy-paste errors -- Accidentally duplicating a config block when editing.

Troubleshooting and Resolution Steps

1. Identify the duplicate element: The error message names the excessive element. Search for it across all config files:

   grep -rn 'element_name' /etc/clickhouse-server/config.xml /etc/clickhouse-server/config.d/
   

2. Understand the config merge behavior: ClickHouse merges XML fragments from config.d/ into the main config. If you want a fragment to replace an existing element rather than add to it, use the replace attribute:

   <!-- In config.d/override.xml -->
   <clickhouse>
       <max_connections replace="replace">200</max_connections>
   </clickhouse>
   

   Without replace="replace", defining <max_connections> in both the main config and a fragment creates a duplicate.

3. Remove duplicate definitions: If the same element appears in multiple files, consolidate it into a single location. Prefer using config.d/ overrides over editing the main config.xml.

4. Check for leftover files:

   ls /etc/clickhouse-server/config.d/
   

   Look for files like *.rpmsave, *.dpkg-dist, or backup files that might have an .xml extension and are being loaded inadvertently. Rename or remove them:

   # These should not have .xml extension if you don't want them loaded
   mv /etc/clickhouse-server/config.d/old_config.xml.bak /etc/clickhouse-server/config.d/old_config.bak
   

5. Validate the merged configuration: You can inspect what ClickHouse sees as the merged config by checking the preprocessed config file:

   cat /var/lib/clickhouse/preprocessed_configs/config.xml
   

   This file shows the final merged result of all config sources and is invaluable for debugging.

6. Restart or reload after fixing:

   SYSTEM RELOAD CONFIG;
   

   Or restart the server if it failed to start.

Best Practices

• Always use replace="replace" when overriding a single-valued element in config.d/ fragments.
• Keep the main config.xml and users.xml as close to the defaults as possible; put all customizations in config.d/ and users.d/ override files.
• Remove or rename backup and leftover config files so they are not accidentally loaded.
• Check the preprocessed config (/var/lib/clickhouse/preprocessed_configs/) after making changes to verify the merged result.
• Use version control for all configuration files to track changes and catch accidental duplications.

Frequently Asked Questions

Q: How does ClickHouse merge config fragments from config.d/?
A: ClickHouse loads all .xml files from config.d/ in alphabetical order and merges them into the main config.xml. Elements are merged recursively -- children with the same tag name are combined. To replace an element entirely instead of merging, add replace="replace" to the element in the override file.

Q: Can I have the same setting in two different config.d/ files?
A: It depends on the element. List-type elements (like <remote_servers> with multiple shards) support multiple entries. Single-valued elements (like <max_connections>) will cause an EXCESSIVE_ELEMENT_IN_CONFIG error if defined more than once without replace="replace".

Q: Where is the preprocessed config stored?
A: By default, it is stored in /var/lib/clickhouse/preprocessed_configs/. This directory contains config.xml and users.xml files that show the final merged configuration that ClickHouse is actually using.

Q: Does this error also apply to users.xml and users.d/?
A: Yes. The same merge behavior and potential for duplicates applies to user configuration files. The replace="replace" attribute works the same way in users.d/ overrides.