ClickHouse DB::Exception: Cannot drop database, not empty

The "DB::Exception: Cannot drop database, not empty" error means you tried to drop a database that still contains tables, views, or other objects. ClickHouse identifies this with the error code DATABASE_NOT_EMPTY. Unlike some other database systems, ClickHouse does not support a CASCADE option on DROP DATABASE, so you must remove all objects inside the database before dropping it.

Impact

The DROP DATABASE statement is rejected, and the database remains intact with all its tables and data. This is actually a safety feature -- it prevents accidental data loss by ensuring you consciously remove each table before discarding the database itself.

Common Causes

1. Attempting to drop a database without first removing its tables -- the most common scenario.
2. Hidden or system-generated objects -- materialized views, dictionaries, or internal tables that are not immediately visible.
3. Migration scripts that assume CASCADE behavior -- scripts ported from PostgreSQL or MySQL that expect DROP DATABASE to remove everything.
4. Replicated tables with ZooKeeper metadata -- tables that were not fully cleaned up on all replicas.
5. Temporary tables or views forgotten during cleanup -- objects created during debugging that were never removed.

Troubleshooting and Resolution Steps

1. List all objects in the database:

   SHOW TABLES FROM my_database;
   

   This shows tables, views, and materialized views.

2. Also check for dictionaries:

   SELECT name FROM system.dictionaries WHERE database = 'my_database';
   

3. Drop all tables in the database. You can generate the DROP statements:

   SELECT 'DROP TABLE IF EXISTS my_database.' || name || ' SYNC;'
   FROM system.tables
   WHERE database = 'my_database';
   

   Then execute the generated statements.

4. For clustered environments, drop tables on the cluster:

   SELECT 'DROP TABLE IF EXISTS my_database.' || name || ' ON CLUSTER my_cluster SYNC;'
   FROM system.tables
   WHERE database = 'my_database';
   

5. After all objects are removed, drop the database:

   DROP DATABASE my_database;
   

6. If you are certain you want to remove everything, script the full cleanup:

   clickhouse-client --query "SHOW TABLES FROM my_database" | \
     while read table; do
       clickhouse-client --query "DROP TABLE IF EXISTS my_database.${table} SYNC"
     done
   clickhouse-client --query "DROP DATABASE IF EXISTS my_database"
   

Best Practices

• Always inventory database objects before attempting to drop a database.
• In migration and teardown scripts, explicitly drop all tables before dropping the database.
• Keep track of materialized views and dictionaries, which are easy to overlook.
• Use the SYNC keyword when dropping tables to ensure they are fully removed before proceeding.
• Document database dependencies so cleanup scripts do not miss anything.

Frequently Asked Questions

Q: Does ClickHouse support DROP DATABASE CASCADE?
A: No. ClickHouse does not support CASCADE on DROP DATABASE. You must drop all tables and other objects individually before dropping the database.

Q: What kinds of objects can prevent a database from being dropped?
A: Tables, views, materialized views, dictionaries, and any other named objects within the database will prevent it from being dropped.

Q: I ran SHOW TABLES and it is empty, but DROP DATABASE still fails. What is going on?
A: Check for dictionaries or other objects that SHOW TABLES may not display. Query system.tables and system.dictionaries filtered by the database name. Also check for .inner tables created by materialized views.

Q: How do I drop all tables in a database in one command?
A: There is no single command to do this. Generate DROP statements from system.tables as shown above, or use a shell loop. Some ClickHouse management tools offer a "drop all" convenience function.