ClickHouse DB::Exception: Unknown setting

When ClickHouse encounters a setting name it does not recognize, it raises the UNKNOWN_SETTING error (error code 115). The full message typically looks like DB::Exception: Unknown setting 'some_setting_name'. This is one of the most frequently hit errors after a ClickHouse version upgrade, because settings are occasionally renamed or removed between releases. A simple typo in a query or configuration file can also trigger it.

Impact

Any query or session that references the unrecognized setting will fail immediately. If the unknown setting appears in a server configuration file (users.xml, profiles.xml, or a YAML equivalent), ClickHouse may refuse to start or may reject connections for the affected user profile. In distributed setups, a mismatch in ClickHouse versions across nodes can cause the error to appear intermittently depending on which node processes the query.

Common Causes

1. Typo in setting name -- A misspelled setting such as max_thread instead of max_threads will produce this error.
2. ClickHouse version upgrade -- Settings are sometimes renamed or dropped. For example, allow_experimental_lightweight_delete was removed in later versions after lightweight deletes became generally available.
3. Version mismatch in a cluster -- Sending a query with a newer setting to an older node that does not yet support it.
4. Copy-paste from documentation for a different version -- Online examples may reference settings that only exist in certain ClickHouse releases.
5. Setting placed in the wrong scope -- Some settings are MergeTree table-level settings, not session-level settings. Using them in a SET statement or query-level SETTINGS clause will trigger this error.

Troubleshooting and Resolution Steps

1. Verify the setting name spelling: Query the system.settings table to check whether the setting exists on your server:

   SELECT name FROM system.settings WHERE name ILIKE '%your_setting%';
   

2. Check your ClickHouse version:

   SELECT version();
   

   Then consult the ClickHouse changelog to confirm the setting exists in your version.

3. Look for renamed settings: ClickHouse sometimes renames settings for consistency. Search the changelog or release notes for the old name to find its replacement. For example, input_format_allow_errors_num replaced an earlier variant.

4. Distinguish session settings from MergeTree settings: If the setting is a MergeTree-specific option (e.g., index_granularity, min_bytes_for_wide_part), it belongs in the table's SETTINGS clause during CREATE TABLE or ALTER TABLE, not in a SET statement:

   -- Correct: table-level setting
   CREATE TABLE example (id UInt64) ENGINE = MergeTree()
   ORDER BY id
   SETTINGS index_granularity = 4096;
   
   -- Incorrect: this will produce UNKNOWN_SETTING
   SET index_granularity = 4096;
   

5. Update configuration files after an upgrade: After upgrading ClickHouse, review users.xml, config.xml, and any included config fragments for deprecated settings. The server log (clickhouse-server.log) will usually mention the specific unknown setting at startup.

6. Handle cluster version mismatches: If your cluster has nodes on different versions, ensure queries only use settings supported by the oldest version in the cluster, or perform a rolling upgrade to bring all nodes to the same release.

Best Practices

• Pin your ClickHouse version in deployment scripts and test upgrades in a staging environment before production.
• Review the changelog for removed or renamed settings before every major or minor upgrade.
• Use system.settings as the source of truth for available setting names on a given server.
• Keep configuration files under version control so you can diff changes after upgrades.
• Avoid hardcoding experimental settings in application code -- they are subject to removal without notice.

Frequently Asked Questions

Q: Can I make ClickHouse ignore unknown settings instead of throwing an error?
A: Yes. You can set SET unknown_setting_mode = 'ignore' (available in recent versions) or use SETTINGS ... SETTINGS unknown_setting_mode = 'ignore' at the query level. This tells ClickHouse to silently skip unrecognized settings. Use this with caution, as it may mask real problems.

Q: I upgraded ClickHouse and now the server won't start because of an unknown setting in the config. What should I do?
A: Edit the configuration file (typically /etc/clickhouse-server/users.xml or files in users.d/) to remove or rename the offending setting. Check the server error log for the exact setting name. After fixing the config, restart ClickHouse.

Q: How do I find all valid settings for my ClickHouse version?
A: Run SELECT name, value, description FROM system.settings ORDER BY name. This returns every session-level setting recognized by the running server. For MergeTree-specific settings, query SELECT name FROM system.merge_tree_settings.

Q: Does this error affect only queries, or can it affect replication too?
A: It primarily affects queries and server startup (when the setting is in a config file). However, if a distributed DDL query (ON CLUSTER) includes an unknown setting, it will fail on nodes that do not recognize it, which can look like a replication issue.