How Do I Delete the Backup Table of the ZooKeeper Node in the ClickHouse Cluster?

Symptom

Figure 1 Execution results

Possible Causes

Solutions

1. Create a database.

   create database demo ON CLUSTER default_cluster;
   
   

2. Create tables in the database.

   use demo;
   
   

   Create the test table.

   CREATE TABLE demo.test ON CLUSTER default_cluster(`EventDate` DateTime, `id` UInt64)ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/test', '{replica}') PARTITION BY toYYYYMM(EventDate) ORDER BY id;
   
   

3. Delete the table.

   drop table test SYNC;
   
   

4. The following error message is displayed if a table with the same name as the deleted one is created:

   ┌─host──────────┬─port─┬─status─┬─error──────────────────────────────────────────────────────────────────────────────────────────────┬─num_hosts_remaining─┬─num_hosts_active─┐
   │ 192.168.2.185 │ 9000 │     57 │ Code: 57. DB::Exception: Table demo.test already exists. (TABLE_ALREADY_EXISTS) (version 22.3.2.1) │                   1 │                1 │
   └───────────────┴──────┴────────┴────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────────────┴──────────────────┘
   ┌─host─────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
   │ 192.168.2.16 │ 9000 │      0 │       │                   0 │                0 │
   └──────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
   
   

5. Method 1: Run the following command to delete the tables and related data on each node:

   drop table test ON CLUSTER default_cluster SYNC;
   
   

   No error occurs if a table with the same name as the deleted one is created.

6. Method 2: Delete the table on other nodes.

   drop table test SYNC;