ClickHouse DB::Exception: Async load canceled

The "DB::Exception: Async load canceled" error in ClickHouse appears when an asynchronous loading operation for a table, database, or dictionary is cancelled before it finishes. The ASYNC_LOAD_CANCELED error code is triggered during server shutdown, a timeout, or when the object being loaded is dropped before loading completes.

Impact

The table, database, or dictionary that was being loaded asynchronously will not be available for queries. If this happens during a normal shutdown, it is expected behavior and not a concern. However, if it occurs during startup or normal operation, the affected objects remain inaccessible until the server is restarted or the object is explicitly reloaded.

Common Causes

1. The ClickHouse server is shutting down while tables are still being loaded asynchronously
2. A table or database was dropped while its async load was still in progress
3. The async loading operation exceeded an internal timeout
4. Insufficient system resources (CPU, memory, or disk I/O) caused the loading to stall and eventually be cancelled
5. Corrupted table metadata that causes the loader to fail partway through

Troubleshooting and Resolution Steps

1. Check the server logs for context around the cancellation:

   grep -i 'async_load\|AsyncLoad\|ASYNC_LOAD_CANCELED' /var/log/clickhouse-server/clickhouse-server.log | tail -30
   

2. If the error occurred during shutdown, it is typically benign. Verify that the server restarted cleanly and all tables loaded successfully:

   SELECT database, name, engine, has_own_data
   FROM system.tables
   WHERE database NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA');
   

3. Check whether asynchronous loading is enabled and review related settings:

   SELECT name, value
   FROM system.settings
   WHERE name LIKE '%async%load%';
   

4. If a specific table failed to load, try detaching and reattaching it:

   DETACH TABLE my_database.my_table;
   ATTACH TABLE my_database.my_table;
   

5. Review system resource utilization during startup. Heavy disk I/O or memory pressure can slow async loading to the point where it gets cancelled:

   dmesg | grep -i 'oom\|memory'
   

6. For persistent issues, consider disabling async loading temporarily to identify the problematic table:

   <clickhouse>
       <async_load_databases>false</async_load_databases>
   </clickhouse>
   

   This forces synchronous loading, which is slower but will produce a clear error for the specific table that fails.

Best Practices

• Monitor server startup times and log output for ASYNC_LOAD_CANCELED errors that appear outside of shutdown scenarios.
• Ensure the server has adequate resources during startup, especially when loading many tables with large metadata.
• Keep table metadata clean by periodically removing unused tables and partitions.
• Use async_load_databases thoughtfully — it speeds up startup but can mask loading issues if tables are problematic.

Frequently Asked Questions

Q: Is ASYNC_LOAD_CANCELED always a problem?
A: Not necessarily. During a graceful shutdown, ClickHouse cancels all pending async operations, and this error is expected. It only warrants investigation if it occurs during startup or normal operation.

Q: Will the table become available after a server restart?
A: In most cases, yes. If the cancellation was caused by a transient condition like shutdown or resource pressure, the table will load normally on the next startup. If there is corrupted metadata, the issue will persist.

Q: Can I force a table to reload without restarting the server?
A: Yes. Use DETACH TABLE followed by ATTACH TABLE to trigger a reload of a specific table. For dictionaries, use SYSTEM RELOAD DICTIONARY.