ClickHouse DB::Exception: Unknown database

The "DB::Exception: Unknown database" error means ClickHouse cannot find the database you referenced in your query or DDL statement. Identified by the error code UNKNOWN_DATABASE, it fires whenever you try to query a table, create a table, or perform any operation that specifies a database name that does not exist on the server.

Impact

Any query or statement referencing the missing database will fail. This blocks table creation, data ingestion, and reads alike. In a deployment pipeline, a missing database can cascade into a full outage of dependent services if the application assumes the database is already present.

Common Causes

1. The database was never created -- a migration step that creates the database was skipped or failed silently.
2. Typo in the database name -- CREATE TABLE my_databse.events instead of my_database.events.
3. Case sensitivity -- ClickHouse database names are case-sensitive by default. MyDatabase and mydatabase are different.
4. Wrong server or cluster node -- connecting to a node where the database has not been replicated or created yet.
5. Database was dropped -- someone or an automated process removed the database.
6. Using a fully qualified name without the database existing in the current context -- referencing production.events when only default database exists.

Troubleshooting and Resolution Steps

1. List all databases on the server:

   SHOW DATABASES;
   

   Verify that the database name you are referencing appears in the list.

2. Check for typos and casing. Compare the database name in your query against what SHOW DATABASES returns. Remember that names are case-sensitive.

3. Create the database if it does not exist:

   CREATE DATABASE IF NOT EXISTS my_database;
   

4. For clustered environments, create the database on all nodes:

   CREATE DATABASE IF NOT EXISTS my_database ON CLUSTER my_cluster;
   

5. Verify you are connected to the correct server. Check your connection string or client configuration:

   clickhouse-client --host your-host --port 9000 --query "SHOW DATABASES"
   

6. Check if the database was recently dropped. Look in the ClickHouse server logs or query the system.query_log:

   SELECT query, event_time
   FROM system.query_log
   WHERE query LIKE '%DROP DATABASE%my_database%'
   ORDER BY event_time DESC
   LIMIT 5;
   

7. If the database metadata is corrupt, check the filesystem. Database metadata lives in the ClickHouse data directory (usually /var/lib/clickhouse/metadata/). Verify that a .sql file for the database exists.

Best Practices

• Always use CREATE DATABASE IF NOT EXISTS in deployment and migration scripts.
• Include database creation as the first step in your schema migration pipeline, before any table creation.
• In multi-node clusters, use ON CLUSTER for database creation to ensure consistency.
• Monitor your ClickHouse instances for unexpected DDL operations using system.query_log.
• Use connection string defaults to set the correct database context so that unqualified table names resolve correctly.

Frequently Asked Questions

Q: Are database names case-sensitive in ClickHouse?
A: Yes. MyDatabase and mydatabase are treated as two separate databases. Always verify the exact casing.

Q: How do I set a default database for my session?
A: Use the USE statement: USE my_database;. In clickhouse-client, you can also pass --database my_database on the command line.

Q: I created the database on one replica but queries on another replica fail. Why?
A: Database creation is not automatically replicated. Use CREATE DATABASE ... ON CLUSTER to propagate the DDL to all nodes, or create the database individually on each node.

Q: Can a database disappear on its own?
A: Not under normal circumstances. Databases are only removed by explicit DROP DATABASE commands. Check your system.query_log for unexpected drops, and verify that no automated cleanup scripts are targeting your database.