ClickHouse DB::Exception: Unknown overflow mode

The UNKNOWN_OVERFLOW_MODE error is raised when you set an overflow mode setting to a value ClickHouse does not recognize. The message looks like DB::Exception: Unknown overflow mode: 'invalid_value'. Overflow modes control what happens when a query exceeds a resource limit (such as max_rows_to_group_by or max_result_rows), and they only accept a specific set of string values.

Impact

The query that specifies the invalid overflow mode will fail immediately. If the invalid value is set in a user profile or server configuration, it can affect all queries for that user or profile. The underlying resource limit will not be enforced with the intended behavior, which could either cause queries to fail unexpectedly or allow them to consume more resources than desired.

Common Causes

1. Typo in the overflow mode value -- Writing 'breaking' instead of 'break', or 'throwing' instead of 'throw'.
2. Using a value from a different setting -- Overflow mode values for different settings are the same, but confusing them with unrelated enum values (e.g., using 'random' which belongs to load_balancing).
3. Copy-paste from outdated documentation -- Older examples may reference values that were renamed or are not applicable.
4. Case sensitivity -- Overflow mode values are case-sensitive; 'Throw' will not be accepted when 'throw' is expected.

Troubleshooting and Resolution Steps

1. Check the valid overflow mode values: ClickHouse supports the following overflow modes for most limit settings:

   • 'throw' -- Throw an exception when the limit is exceeded (default for most settings).
   • 'break' -- Stop processing and return partial results.
   • 'any' -- Continue processing but only keep data that fits within the limit (available for group_by_overflow_mode).

2. Identify which setting uses the overflow mode: Common settings that accept an overflow mode:

   -- Group by limits
   SET max_rows_to_group_by = 1000000;
   SET group_by_overflow_mode = 'any';  -- Valid: 'throw', 'break', 'any'
   
   -- Result limits
   SET max_result_rows = 100000;
   SET result_overflow_mode = 'break';  -- Valid: 'throw', 'break'
   
   -- Sort limits
   SET max_rows_to_sort = 1000000;
   SET sort_overflow_mode = 'break';  -- Valid: 'throw', 'break'
   
   -- Other limit overflow modes follow the same pattern
   

3. Fix the value in your query or configuration:

   -- Incorrect
   SET group_by_overflow_mode = 'truncate';
   
   -- Correct
   SET group_by_overflow_mode = 'any';
   

4. Check configuration files if the error occurs at startup:

   grep -rn 'overflow_mode' /etc/clickhouse-server/users.xml /etc/clickhouse-server/users.d/
   

   Correct any invalid values found.

5. Verify available values for your version:

   SELECT name, type FROM system.settings WHERE name LIKE '%overflow_mode%';
   

   The type column will show OverflowMode or similar, confirming it is an enum type.

Best Practices

• Use 'throw' as the default overflow mode to catch queries that exceed limits, then selectively use 'break' or 'any' where partial results are acceptable.
• Document which overflow mode each profile uses and why, so administrators understand the intended behavior.
• When using group_by_overflow_mode = 'any', be aware that results are approximate -- ClickHouse will keep only the groups that fit within max_rows_to_group_by and discard the rest.
• Test overflow behavior in a development environment to understand how partial results affect your application.
• Always specify overflow mode values in lowercase to avoid case-sensitivity issues.

Frequently Asked Questions

Q: What is the difference between 'break' and 'any' overflow modes?
A: 'break' stops query execution and returns whatever results have been computed so far. 'any' is specific to GROUP BY operations -- it continues processing all rows but only keeps aggregation state for the groups that fit within the limit, discarding others. Both return partial results, but 'any' processes all input data.

Q: Can I use 'any' with result_overflow_mode?
A: No. The 'any' mode is only valid for group_by_overflow_mode. Other overflow mode settings only accept 'throw' and 'break'.

Q: Why does my query return incomplete results without an error?
A: If an overflow mode is set to 'break' or 'any', ClickHouse silently returns partial results instead of throwing an error. Check your session settings to see if an overflow mode is masking a limit breach.

Q: Are overflow modes case-sensitive?
A: Yes. Always use lowercase values: 'throw', 'break', 'any'. Using 'Throw' or 'BREAK' will result in the UNKNOWN_OVERFLOW_MODE error.