ClickHouse DB::Exception: Table UUID mismatch

The "DB::Exception: Table UUID mismatch" error occurs when ClickHouse detects that a table's UUID in its metadata does not match the UUID expected by the system. The error code TABLE_UUID_MISMATCH is specific to the Atomic database engine, which assigns a UUID to every table. This mismatch can happen during table restoration, replication, or after manual metadata manipulation.

Impact

The affected table may fail to load, or operations that reference it by UUID will break. In the Atomic database engine, UUIDs are used for internal bookkeeping including atomic DDL operations, so a mismatch can prevent DDL, replication, or even server startup for the affected table.

Common Causes

1. Restoring a table from backup with a different UUID -- the backup contains a UUID that conflicts with the current state.
2. Manual metadata file editing -- changing the UUID in a .sql metadata file without updating corresponding references.
3. Re-creating a table with the same name after a drop -- the new table gets a different UUID, but some component still references the old one.
4. ZooKeeper state mismatch -- for replicated tables, the UUID in ZooKeeper differs from the local metadata.
5. Moving tables between databases -- especially between Atomic databases on different servers.
6. Incomplete RENAME or EXCHANGE operation -- a crash during an atomic operation left inconsistent UUIDs.

Troubleshooting and Resolution Steps

1. Check the table's current UUID:

   SELECT database, name, uuid, engine
   FROM system.tables
   WHERE name = 'my_table';
   

2. Check the metadata file on disk for the stored UUID:

   cat /var/lib/clickhouse/metadata/my_database/my_table.sql
   

   Look for the UUID keyword in the ATTACH statement.

3. For replicated tables, compare with ZooKeeper:

   SELECT value FROM system.zookeeper
   WHERE path = '/clickhouse/tables/shard1/my_table' AND name = 'metadata';
   

4. If the table needs to be re-created with the correct UUID, drop and recreate it:

   DROP TABLE IF EXISTS my_database.my_table SYNC;
   CREATE TABLE my_database.my_table UUID 'expected-uuid-here' (...)
   ENGINE = MergeTree()
   ORDER BY id;
   

   Note: specifying a UUID explicitly in CREATE TABLE is an advanced operation.

5. If restoring from backup, use ATTACH with the correct UUID. Edit the metadata file to match the expected UUID, or let ClickHouse assign a new one:

   -- Drop the conflicting entry
   DETACH TABLE my_database.my_table;
   -- Edit the metadata file to fix the UUID, then reattach
   ATTACH TABLE my_database.my_table;
   

6. Clean up ZooKeeper paths if needed. If the ZooKeeper state references an old UUID:

   -- Check the ZooKeeper path
   SELECT * FROM system.zookeeper
   WHERE path = '/clickhouse/tables/shard1/my_table';
   

   You may need to delete and recreate the ZooKeeper nodes, which is an advanced operation.

7. Check server logs for context on when the mismatch occurred:

   grep "UUID mismatch" /var/log/clickhouse-server/clickhouse-server.err.log
   

Best Practices

• Avoid manually editing metadata files -- always use DDL statements.
• When restoring tables from backups, let ClickHouse assign new UUIDs unless you have a specific reason to preserve the original.
• Keep ZooKeeper and local metadata in sync by using SYSTEM RESTART REPLICA after manual interventions.
• Use the Atomic database engine's EXCHANGE TABLES for safe table replacements rather than drop-and-recreate sequences.
• Document table UUIDs in your backup procedures so you can verify consistency during restoration.

Frequently Asked Questions

Q: What is a table UUID and why does ClickHouse use it?
A: In the Atomic database engine, each table is assigned a UUID (universally unique identifier) that acts as its stable identity. This allows ClickHouse to perform atomic DDL operations (like RENAME or EXCHANGE) without ambiguity, even if table names change.

Q: Do tables in the Ordinary database engine have UUIDs?
A: No. UUIDs are specific to the Atomic database engine. Tables in Ordinary databases are identified solely by their name and path.

Q: Can I specify a UUID when creating a table?
A: Yes, but it is generally not recommended unless you are performing a specific recovery operation. The syntax is CREATE TABLE my_table UUID '...' (...). Let ClickHouse auto-generate UUIDs under normal circumstances.

Q: Will this error prevent ClickHouse from starting?
A: It can prevent the specific table from loading, but the server itself should start. The table will be marked as failed to load, and you can fix it while the server is running.