ClickHouse DB::Exception: Dictionaries were not loaded

The "DB::Exception: Dictionaries were not loaded" error in ClickHouse appears when you attempt to use a dictionary that has not yet been loaded into memory. External dictionaries in ClickHouse are loaded asynchronously, and if a query references a dictionary before the loading process completes, or if the load failed entirely, the DICTIONARIES_WAS_NOT_LOADED error is raised.

Impact

Queries using dictGet, dictHas, and related dictionary functions will fail until the dictionaries finish loading. This is particularly common right after a server restart or when new dictionaries are added to the configuration. If many queries depend on dictionaries, a large portion of your workload could be affected during the loading window.

Common Causes

1. Server just started -- dictionaries are loaded asynchronously after server startup. Queries issued immediately may arrive before loading completes.
2. Dictionary configuration error -- an error in the dictionary XML or DDL definition prevents loading entirely.
3. Source unavailability at startup -- the dictionary source (database, file, HTTP endpoint) was unreachable during the loading attempt.
4. Large dictionaries taking time to load -- very large dictionaries may take minutes to load, especially from remote sources.
5. Dependency ordering -- a dictionary depends on another dictionary or table that has not yet been created or loaded.

Troubleshooting and Resolution Steps

1. Check dictionary loading status:

   SELECT name, status, last_exception, loading_start_time, loading_duration
   FROM system.dictionaries;
   

   Look at the status column: LOADED, LOADING, FAILED, or NOT_LOADED.

2. Wait for loading to complete. If the server just started, dictionaries may still be loading. Monitor progress:

   SELECT name, status, element_count
   FROM system.dictionaries
   WHERE status != 'LOADED';
   

3. Manually trigger a reload:

   -- Reload all dictionaries
   SYSTEM RELOAD DICTIONARIES;
   
   -- Reload a specific dictionary
   SYSTEM RELOAD DICTIONARY my_dictionary;
   

4. Review dictionary configuration for errors. Check both DDL-defined dictionaries and XML configuration files:

   SHOW CREATE DICTIONARY my_dictionary;
   

   For XML-based dictionaries, check files in /etc/clickhouse-server/ or the directory specified by dictionaries_config.

5. Check server logs for load errors:

   grep -i 'dictionary\|dictionar' /var/log/clickhouse-server/clickhouse-server.log | tail -100
   

6. Verify source connectivity before relying on the dictionary:

   -- Test that the source is reachable
   SELECT 1 FROM mysql('host:port', 'db', 'table', 'user', 'pass') LIMIT 1;
   

Best Practices

• Implement retry logic in applications that depend on dictionaries, with appropriate backoff during server startup windows.
• Monitor system.dictionaries for dictionaries in FAILED or NOT_LOADED status and alert on these conditions.
• For critical dictionaries, consider using dictGetOrDefault with a sensible fallback value to degrade gracefully.
• Keep dictionary sources fast and reliable. Slow source queries delay the entire loading process.
• Use LIFETIME(MIN 0 MAX 0) for dictionaries that should load once and never refresh, reducing the risk of reload failures.
• If dictionary load order matters, configure dependencies explicitly or stagger startup queries.

Frequently Asked Questions

Q: How long should I wait for dictionaries to load after a server restart?
A: It depends on the number and size of your dictionaries and the speed of their sources. Small dictionaries from local tables load in seconds. Large dictionaries from remote sources can take minutes. Monitor system.dictionaries for real-time progress.

Q: Can I make ClickHouse wait for dictionaries before accepting queries?
A: There is no built-in setting to block queries until dictionaries are loaded. You can implement this at the application level by checking system.dictionaries status before sending production queries, or use a readiness probe that verifies dictionary status.

Q: What happens if only some dictionaries fail to load?
A: Only queries referencing the failed dictionaries will encounter errors. Other dictionaries and general queries will work normally.

Q: Does this error affect DDL-created dictionaries differently than XML-configured ones?
A: No, the loading mechanism and error behavior are the same regardless of how the dictionary was defined. Both types load asynchronously after server startup.