ClickHouse DB::Exception: Database already exists

The "DB::Exception: Database already exists" error is triggered when you execute a CREATE DATABASE statement for a database name that is already present on the server. ClickHouse reports this with the error code DATABASE_ALREADY_EXISTS. While it is a straightforward error, it frequently catches teams off guard in automated deployments and CI/CD pipelines that run DDL scripts repeatedly.

Impact

The CREATE DATABASE statement is rejected, but the existing database and all its tables remain untouched. The practical risk is that your script halts at this point, potentially leaving subsequent steps (table creation, data loading) unexecuted unless your pipeline handles the error gracefully.

Common Causes

1. Running initialization scripts more than once -- a CREATE DATABASE statement without IF NOT EXISTS will fail on every run after the first.
2. Parallel deployments -- multiple deployment processes trying to create the same database simultaneously.
3. Environment drift -- a developer manually created the database before the migration script ran.
4. Cluster-wide DDL with partial state -- the database exists on some nodes but not others, and a non-cluster-aware script tries to create it everywhere.

Troubleshooting and Resolution Steps

1. Add IF NOT EXISTS to your CREATE DATABASE statement:

   CREATE DATABASE IF NOT EXISTS my_database;
   

   This makes the statement idempotent. If the database already exists, ClickHouse simply moves on.

2. Verify the existing database is the one you expect:

   SHOW DATABASES;
   SHOW CREATE DATABASE my_database;
   

3. Check the database engine. If you intended to create the database with a specific engine (e.g., Replicated, MaterializedMySQL), confirm the existing database uses the same engine:

   SELECT name, engine FROM system.databases WHERE name = 'my_database';
   

4. If you need to recreate the database with different settings, drop it first:

   DROP DATABASE IF EXISTS my_database;
   CREATE DATABASE my_database ENGINE = Atomic;
   

   This destroys all tables and data in the database, so proceed with caution.

5. On a cluster, use distributed DDL:

   CREATE DATABASE IF NOT EXISTS my_database ON CLUSTER my_cluster ENGINE = Atomic;
   

Best Practices

• Always use IF NOT EXISTS when creating databases in scripts, migrations, and infrastructure-as-code.
• Treat database creation as an idempotent operation in your deployment pipeline.
• Log and review any DDL errors rather than ignoring them silently, even when using IF NOT EXISTS.
• In clustered setups, prefer ON CLUSTER DDL to maintain consistency across all nodes.

Frequently Asked Questions

Q: Does IF NOT EXISTS check whether the database engine matches?
A: No. It only checks whether a database with that name exists. If a database with the same name but a different engine already exists, the statement silently succeeds without modifying the existing database.

Q: Can I change the engine of an existing database?
A: Not directly. You would need to drop the database and recreate it with the new engine. Alternatively, create a new database with the desired engine, migrate the tables, and then drop the old one.

Q: Is this error dangerous? Can it corrupt data?
A: No. The error is purely a DDL rejection -- the existing database and its contents are not affected in any way.

Q: How do I handle this in a multi-node cluster?
A: Use CREATE DATABASE IF NOT EXISTS ... ON CLUSTER cluster_name to ensure the database is created on all nodes that do not already have it, while nodes that do have it simply skip the operation.