ClickHouse DB::Exception: Incorrect dictionary definition

The "DB::Exception: Incorrect dictionary definition" error in ClickHouse occurs when the dictionary configuration -- whether defined via DDL or XML -- contains invalid or inconsistent settings. This could be a missing required field, an unsupported layout for the given key type, or a type mismatch between the source and the dictionary structure. The error code is INCORRECT_DICTIONARY_DEFINITION.

Impact

The dictionary cannot be created or loaded. Any queries that depend on the dictionary will fail until the definition is corrected. If the dictionary is defined in XML configuration, ClickHouse will log errors on startup but continue running -- other dictionaries and tables remain unaffected.

Common Causes

1. Missing PRIMARY KEY -- every dictionary requires a primary key definition.
2. Layout incompatible with key type -- for example, using HASHED layout with a composite key without specifying COMPLEX_KEY_HASHED.
3. Source configuration errors -- missing required source parameters like host, port, database, or table.
4. Type mismatch in attribute definitions -- declaring a column as UInt64 in the dictionary when the source provides a String.
5. Invalid LIFETIME specification -- specifying negative values or MIN greater than MAX.
6. Missing or incorrect RANGE specification for range-hashed dictionaries -- the RANGE_HASHED layout requires range_min and range_max attributes.

Troubleshooting and Resolution Steps

1. Review the dictionary definition carefully:

   SHOW CREATE DICTIONARY my_dictionary;
   

   Or check the XML file for XML-configured dictionaries.

2. Ensure the PRIMARY KEY is specified and matches the layout:

   -- Simple key with HASHED layout
   CREATE DICTIONARY simple_dict (
       id UInt64,
       value String
   )
   PRIMARY KEY id
   SOURCE(CLICKHOUSE(TABLE 'source' DB 'default'))
   LAYOUT(HASHED())
   LIFETIME(MIN 300 MAX 600);
   
   -- Composite key requires COMPLEX_KEY_HASHED
   CREATE DICTIONARY composite_dict (
       region String,
       city String,
       population UInt64
   )
   PRIMARY KEY region, city
   SOURCE(CLICKHOUSE(TABLE 'source' DB 'default'))
   LAYOUT(COMPLEX_KEY_HASHED())
   LIFETIME(MIN 300 MAX 600);
   

3. Verify source configuration includes all required fields:

   -- MySQL source example with all required fields
   CREATE DICTIONARY mysql_dict (
       id UInt64,
       name String
   )
   PRIMARY KEY id
   SOURCE(MYSQL(
       HOST 'mysql-host'
       PORT 3306
       USER 'reader'
       PASSWORD 'secret'
       DB 'mydb'
       TABLE 'lookup_table'
   ))
   LAYOUT(HASHED())
   LIFETIME(MIN 300 MAX 600);
   

4. Check attribute types match the source. Column types in the dictionary must be compatible with the source data:

   -- Verify source column types
   DESCRIBE TABLE source_database.source_table;
   

5. For range-hashed dictionaries, include range attributes:

   CREATE DICTIONARY range_dict (
       id UInt64,
       start_date Date,
       end_date Date,
       value String
   )
   PRIMARY KEY id
   RANGE(MIN start_date MAX end_date)
   SOURCE(CLICKHOUSE(TABLE 'source' DB 'default'))
   LAYOUT(RANGE_HASHED())
   LIFETIME(MIN 300 MAX 600);
   

6. Check server logs for detailed error descriptions:

   grep -i 'dictionary' /var/log/clickhouse-server/clickhouse-server.err.log | tail -20
   

Best Practices

• Start with the simplest layout (HASHED for single keys, COMPLEX_KEY_HASHED for composite keys) and only use specialized layouts when needed.
• Test dictionary definitions in a development environment before deploying to production.
• Use CREATE OR REPLACE DICTIONARY to iterate on definitions without needing to drop first.
• Document the expected source schema alongside the dictionary definition to catch type mismatches early.
• Validate XML dictionary configurations with a linter or schema validator before applying to the server.

Frequently Asked Questions

Q: Which layouts support composite (multi-column) keys?
A: Layouts prefixed with COMPLEX_KEY_ support composite keys: COMPLEX_KEY_HASHED, COMPLEX_KEY_CACHE, COMPLEX_KEY_DIRECT, and COMPLEX_KEY_SSD_CACHE. Using a non-complex layout with a composite key will trigger this error.

Q: Can I change a dictionary's layout without dropping it?
A: Yes, use CREATE OR REPLACE DICTIONARY with the new layout. The dictionary will be reloaded with the new configuration.

Q: What LIFETIME values should I use?
A: LIFETIME(MIN x MAX y) tells ClickHouse to reload the dictionary at a random interval between x and y seconds. This randomization prevents reload storms. Use LIFETIME(0) if you want the dictionary to load once and never auto-refresh.

Q: How do I debug XML dictionary configuration errors?
A: Check system.dictionaries for the last_exception field, and review the ClickHouse server error log. XML errors are often logged with detailed messages about which field is missing or invalid.