ClickHouse DB::Exception: Table not empty

The "DB::Exception: Table not empty" error is raised when ClickHouse encounters a table that contains data in a context where an empty table is required. The error code TABLE_NOT_EMPTY signals that the operation you are attempting can only proceed on a table with no rows or parts. This commonly occurs during certain ATTACH operations, table engine conversions, or specific ALTER commands that require a clean starting state.

Impact

The operation is rejected, and the table remains unchanged. No data is lost or corrupted. You will need to either empty the table or use an alternative approach to accomplish your goal.

Common Causes

1. Attaching a table to a path that already contains data parts -- the target directory has existing data that conflicts with the attach.
2. Certain ALTER TABLE ENGINE operations -- changing a table's engine may require the table to be empty.
3. Re-initializing a replicated table -- creating a replicated table where the local data directory is not empty.
4. Re-attaching a table after metadata changes -- the data on disk does not match expectations.
5. Using ATTACH TABLE with pre-existing parts -- ClickHouse expects the table to be empty before attaching data.

Troubleshooting and Resolution Steps

1. Check the current row count and data size:

   SELECT count() FROM my_database.my_table;
   
   SELECT
       name,
       formatReadableSize(total_bytes) AS size,
       total_rows
   FROM system.tables
   WHERE database = 'my_database' AND name = 'my_table';
   

2. If you can safely remove the data, truncate the table:

   TRUNCATE TABLE my_database.my_table;
   

3. If you need to preserve the data, back it up first:

   CREATE TABLE my_database.my_table_backup AS my_database.my_table;
   INSERT INTO my_database.my_table_backup SELECT * FROM my_database.my_table;
   TRUNCATE TABLE my_database.my_table;
   

4. For replicated table reinitialization, clear the data directory:

   -- Drop and recreate with the same engine
   DROP TABLE my_database.my_table SYNC;
   CREATE TABLE my_database.my_table (...) ENGINE = ReplicatedMergeTree(...) ORDER BY ...;
   

   The new replica will replicate data from other healthy replicas.

5. Check the data directory for orphaned parts:

   ls /var/lib/clickhouse/data/my_database/my_table/
   

   If there are parts on disk that do not belong, you may need to clean them up after detaching the table.

6. If the issue occurs during ATTACH, try ATTACH with a clean directory. Move existing parts out of the way, attach the empty table, then move parts back and run:

   ALTER TABLE my_database.my_table ATTACH PART 'part_name';
   

Best Practices

• Always back up data before truncating or dropping tables, even if you believe the data is expendable.
• When performing operations that require an empty table, script the backup-truncate-operate-restore sequence to avoid manual mistakes.
• Keep table data directories clean -- orphaned parts from failed operations can cause unexpected behavior.
• Use system.parts to understand what data exists in a table before performing structural operations.

Frequently Asked Questions

Q: What is the difference between TRUNCATE and DELETE for emptying a table?
A: TRUNCATE TABLE instantly removes all data by dropping all parts. DELETE FROM table WHERE 1=1 marks rows for deletion but uses ClickHouse's mutation mechanism, which is slower and uses more resources. For emptying a table completely, TRUNCATE is always preferred.

Q: Will TRUNCATE TABLE work on replicated tables?
A: Yes. TRUNCATE TABLE works on replicated tables and propagates the truncation to all replicas through ZooKeeper.

Q: Can I avoid this error by using INSERT INTO ... SELECT to move data to a new table?
A: Yes. Creating a new table with the desired structure, inserting data from the old table, and then swapping them with EXCHANGE TABLES is often a safer approach than trying to modify the original table in place.

Q: I see parts on disk but the table reports zero rows. What is happening?
A: This can happen if parts are detached or if there is a metadata mismatch. Check system.parts and system.detached_parts for the table to understand the state of each part.