ClickHouse DB::Exception: Invalid setting value

The INVALID_SETTING_VALUE error in ClickHouse is raised when you assign a value to a known setting but that value fails validation. The message usually reads something like DB::Exception: Invalid setting value for 'max_threads': expected UInt64, got 'abc'. ClickHouse validates both the data type and the logical range of setting values, so passing a string where a number is expected, or a negative value where only positive numbers are allowed, will trigger this error.

Impact

Queries or sessions that attempt to apply the invalid value will be rejected. If the problematic value is in a server configuration file (users.xml, a profile definition, or an included YAML file), the affected user profile may become unusable, and in some cases the server itself may fail to start. Applications that dynamically set ClickHouse settings could experience widespread query failures if they pass incorrectly typed values.

Common Causes

Type mismatch -- Assigning a string to a numeric setting, a negative number to a UInt64 setting, or a non-boolean value to a Bool setting.
Invalid enum value -- Settings that accept a fixed set of string values (like load_balancing or join_algorithm) will reject any value not in the allowed list.
Locale or formatting issues -- Passing numbers with commas (e.g., 1,000,000 instead of 1000000) or locale-specific decimal separators.
Copy-paste errors in configuration files -- Whitespace, invisible characters, or XML encoding issues in users.xml can corrupt the value.
Programmatic setting from application code -- ORMs or client libraries may serialize values in unexpected formats.

Troubleshooting and Resolution Steps

Check the expected type for the setting:
```
SELECT name, type, value FROM system.settings WHERE name = 'your_setting_name';
```
The type column tells you what ClickHouse expects (e.g., UInt64, Bool, String, Enum).
Inspect the exact value you are passing: For queries, check your client code or SQL for formatting issues. For configuration files, examine the raw XML or YAML:
```
grep -A1 'your_setting_name' /etc/clickhouse-server/users.xml
```
Look for stray whitespace, invisible characters, or XML escaping problems.
Test the value in an interactive session:
```
SET max_threads = 8;  -- numeric setting, must be a positive integer
SELECT 1;
```
If this works interactively but fails in your application, the problem is in how the application serializes the value.
Check for enum-type settings: Some settings only accept specific string values. For example, join_algorithm accepts values like 'hash', 'partial_merge', 'direct', etc. Passing an unsupported value will trigger INVALID_SETTING_VALUE. Consult the ClickHouse documentation for the list of accepted values.
Review configuration file encoding: Ensure your config files are saved as UTF-8 without BOM. Special characters in XML must be properly escaped (& for &, < for <).
Validate after changes: After fixing the value, restart ClickHouse (if the setting is in a config file) or re-run your query. Check the server log for any remaining validation errors:
```
tail -n 50 /var/log/clickhouse-server/clickhouse-server.err.log
```

Best Practices

Always consult system.settings for the expected data type before setting a value programmatically.
Use parameterized settings in client libraries rather than string interpolation to avoid type and formatting issues.
Validate configuration files with clickhouse-server --config-file=/etc/clickhouse-server/config.xml --check before restarting the server (available in recent versions).
Keep a reference copy of default configuration files so you can compare after edits.
Document the expected type and valid range for any custom profiles used in your organization.

Frequently Asked Questions

Q: How can I see all valid values for an enum-type setting?
A: ClickHouse does not expose enum values directly via system.settings. Check the official documentation for the setting in question, or look at the source code. The error message itself often lists the accepted values.

Q: I set a value that looks correct, but ClickHouse still rejects it. What could be wrong?
A: Invisible characters are a common culprit -- especially when copying from web pages or documents. Try retyping the value manually. Also check whether the setting expects a specific unit (e.g., bytes vs. megabytes).

Q: Can constraints cause this error?
A: Constraints that restrict a setting's range produce a different error (SETTING_CONSTRAINT_VIOLATION). The INVALID_SETTING_VALUE error is specifically about type validation and basic value parsing, not profile-level constraints.

Q: Does this error appear in the server log if it's in a config file?
A: Yes. When ClickHouse loads configuration at startup or during a config reload, invalid setting values are logged to clickhouse-server.err.log with the full error message, including the setting name and the rejected value.