ClickHouse DB::Exception: Unknown database engine

The "DB::Exception: Unknown database engine" error fires when you reference a database engine that ClickHouse does not recognize. Tagged with the error code UNKNOWN_DATABASE_ENGINE, this typically stems from a typo, a version mismatch, or using a database engine that is not available in your particular ClickHouse build.

Impact

The CREATE DATABASE statement fails and no database is created. Downstream operations -- table creation, data loading, application connectivity -- will be blocked until the issue is corrected.

Common Causes

1. Typo in the engine name -- writing Atomc instead of Atomic or Replcated instead of Replicated.
2. Using a database engine from a newer ClickHouse version -- the Replicated database engine, for example, was introduced in version 21.4.
3. Build missing the engine -- minimal or custom ClickHouse builds may exclude certain database engines like MaterializedMySQL or MaterializedPostgreSQL.
4. Confusing table engines with database engines -- writing ENGINE = MergeTree in a CREATE DATABASE statement.
5. Case sensitivity -- database engine names are case-sensitive in ClickHouse.

Troubleshooting and Resolution Steps

1. Check the spelling and casing of the engine name. Valid database engines include Atomic, Ordinary, Replicated, MaterializedMySQL, MaterializedPostgreSQL, and Lazy.

2. List available database engines by checking existing databases:

   SELECT name, engine FROM system.databases;
   

3. Verify your ClickHouse version supports the engine:

   SELECT version();
   

   Compare against the ClickHouse changelog:

   • Atomic -- default since version 20.5
   • Replicated -- available since version 21.4
   • MaterializedMySQL -- available since version 20.8
   • MaterializedPostgreSQL -- available since version 21.11

4. If you do not need a specific engine, omit the ENGINE clause. ClickHouse will use the default engine (Atomic in modern versions):

   CREATE DATABASE my_database;
   

5. For Replicated engine, ensure ZooKeeper or ClickHouse Keeper is configured. The Replicated engine depends on a coordination service:

   CREATE DATABASE my_database ENGINE = Replicated('/clickhouse/databases/my_database', '{shard}', '{replica}');
   

6. Check if required libraries are installed. MaterializedMySQL needs MySQL client libraries, and MaterializedPostgreSQL needs PostgreSQL client libraries.

Best Practices

• Use the Atomic engine (the default) unless you have a specific reason to choose something else.
• Document which database engine each database uses in your team's infrastructure documentation.
• Test CREATE DATABASE statements against the target ClickHouse version before deploying.
• Avoid the deprecated Ordinary engine for new databases -- it lacks atomic DDL support and will be removed in future versions.

Frequently Asked Questions

Q: What is the default database engine in modern ClickHouse?
A: Starting from version 20.5, the default database engine is Atomic. In older versions, the default was Ordinary.

Q: What is the difference between Atomic and Ordinary database engines?
A: Atomic supports non-blocking DDL operations, table UUIDs, and EXCHANGE TABLES. Ordinary is the legacy engine that performs DDL operations synchronously and does not support atomic renames. New installations should use Atomic.

Q: Can I change a database's engine after creation?
A: Not directly. You would need to create a new database with the desired engine, migrate all tables, and then drop the old database.

Q: Do I need the Replicated database engine to use ReplicatedMergeTree tables?
A: No. ReplicatedMergeTree tables can exist in any database engine. The Replicated database engine is a separate feature that automatically replicates DDL operations across nodes, removing the need for ON CLUSTER DDL.