ClickHouse DB::Exception: Async load cycle detected

The "DB::Exception: Async load cycle detected" error in ClickHouse means the server found a circular dependency among tables or databases during the asynchronous loading phase. The error code is ASYNC_LOAD_CYCLE. When ClickHouse loads tables asynchronously at startup, it builds a dependency graph -- if table A depends on table B which depends on table A, neither can be loaded first, and the server reports this cycle.

Impact

A detected dependency cycle prevents the involved tables from loading:

• All tables in the cycle will remain inaccessible until the circular dependency is resolved
• Views, materialized views, or dictionaries that form part of the cycle are affected
• Other tables without circular dependencies continue to load and function normally
• The server starts but with degraded functionality for the affected tables

Common Causes

1. Materialized views forming a loop -- Table A has a materialized view that writes to Table B, and Table B has a materialized view that writes to Table A.
2. Dictionary circular references -- Dictionary X sources from a table that depends on Dictionary Y, which in turn sources from a table depending on Dictionary X.
3. View dependencies -- A chain of views where the last view references the first, creating a cycle.
4. Cross-database dependencies -- Tables in different databases referencing each other in a circular manner through dictionaries, views, or materialized views.
5. Accidental self-reference -- A table's default expression or materialized column referencing itself through an intermediate object.

Troubleshooting and Resolution Steps

1. Identify the tables involved in the cycle from the server log:

   grep -i "cycle\|circular\|ASYNC_LOAD_CYCLE" /var/log/clickhouse-server/clickhouse-server.log | tail -20
   

   The error message typically lists the chain of dependencies that forms the cycle.

2. Map out the dependency chain manually. For each table named in the error, inspect its definition:

   SHOW CREATE TABLE <database>.<table>;
   

   Look for references to other tables in ENGINE clauses, WHERE conditions, dictGet calls, or materialized view targets.

3. Identify the link to break. In most cases, one of the dependencies is non-essential or can be restructured. Common approaches:

   • Remove a materialized view that creates the back-edge in the dependency graph
   • Change a dictionary to use a different source that does not create a cycle
   • Replace a direct table dependency with a query-time JOIN

4. Drop or detach the problematic object:

   -- If you can access the server
   DETACH TABLE <database>.<problematic_view>;
   

   If the server cannot load the tables at all, edit the metadata files directly:

   # Move the metadata file to prevent it from loading
   mv /var/lib/clickhouse/metadata/<database>/<view>.sql /tmp/<view>.sql.bak
   

   Then restart ClickHouse.

5. Recreate the dependency without the cycle. For example, if a materialized view created the cycle:

   -- Instead of MV on table B writing back to table A:
   -- Create an intermediate table C
   CREATE TABLE <database>.intermediate_table (...) ENGINE = MergeTree() ...;
   
   -- Point the MV to write to intermediate_table instead
   CREATE MATERIALIZED VIEW <database>.mv_fixed TO <database>.intermediate_table AS
   SELECT ... FROM <database>.table_b;
   

6. Restart the server and verify that all tables load successfully:

   systemctl restart clickhouse-server
   grep -i "async_load\|cycle\|error" /var/log/clickhouse-server/clickhouse-server.log | tail -20
   

Best Practices

• Before creating materialized views or dictionaries, sketch out the dependency graph to ensure no cycles will be introduced.
• Avoid bidirectional data flows between tables through materialized views. If two-way synchronization is needed, handle it at the application level.
• Use DETACH/ATTACH to test dependency changes without permanently dropping objects.
• When working with complex schemas involving many views and dictionaries, document the dependency hierarchy.
• Periodically review the dependency structure as the schema evolves to catch emerging cycles early.

Frequently Asked Questions

Q: Can ClickHouse resolve circular dependencies automatically?
A: No. Circular dependencies are a logical error in the schema design. ClickHouse detects and reports them but cannot resolve them automatically. You must restructure the dependencies to eliminate the cycle.

Q: Will this error prevent the ClickHouse server from starting?
A: The server will still start, but the tables involved in the cycle will not be loaded. Other tables without circular dependencies are unaffected and will function normally.

Q: How can I visualize my table dependencies?
A: Query system.tables and system.dictionaries to extract dependency information. You can also use SHOW CREATE TABLE for each table and build a graph manually or with a scripting tool. Some third-party ClickHouse management tools offer dependency visualization.

Q: Is this error new? I did not see it in older ClickHouse versions.
A: The ASYNC_LOAD_CYCLE error was introduced alongside the asynchronous table loading feature in newer ClickHouse versions. In older versions, tables were loaded synchronously, and circular dependencies might have manifested as hangs or different errors during startup.