Clickhouse: Exception: Directory for table data already exists.

[KochetovNicolai]

Version: 20.4.4.18

If table creation failed, second create query may throw exception Directory for table data ... already exists.

Examples:

CREATE TABLE ...

Code: 497. DB::Exception: Received from ... DB::Exception: travel-test: Not enough privileges. ...

CREATE TABLE ...
Received exception from server (version 20.4.4):
Code: 57. DB::Exception: Received from localhost:9000. DB::Exception: Directory for table data ... already exists. 

CREATE TABLE ...

Received exception from server (version 20.4.4):
Code: 225. DB::Exception: Received from localhost:9000. DB::Exception: Can't create replicated table without ZooKeeper. 


CREATE TABLE ...
Received exception from server (version 20.4.4):
Code: 57. DB::Exception: Received from localhost:9000. DB::Exception: Directory for table data ... already exists. 

[tavplubix]

It's dangerous to automatically remove a directory in case of failed CREATE query, because we cannot determine if the directory contains garbage or data of some detached table for example. Probably it's better to disable this check and reopen #7005. Or move the directory to some place like detached/ instead of removing.

Also there is workaround:

:) CREATE TABLE table ...
Code: 57. DB::Exception: Received from localhost:9000. DB::Exception: Directory for table data ... already exists. 
:) ATTACH TABLE table  ...
Ok.

for ReplacetedMergeTree and

:) CREATE TABLE  table ... ENGINE=ReplicatedMergeTree(...
Code: 57. DB::Exception: Received from localhost:9000. DB::Exception: Directory for table data ... already exists. 
:) ATTACH TABLE table ... ENGINE=MergeTree ...
Ok.
:) DROP TABLE table
Ok.
:) CREATE TABLE  table ... ENGINE=ReplicatedMergeTree(...
Ok.

for other tables.

[KochetovNicolai]

I suppose the correct solution is to not create any metadata on disk before checks.
Or create it in temporary directory and move after checks passed.
Or remove after checks, but only if there is no data in table.

[alexey-milovidov]

Or remove after checks, but only if there is no data in table.

Ok. This variant is implemented.

I suppose the correct solution is to not create any metadata on disk before checks.

There is some strange complicated logic - we have to do some checks after initialization of some part of the metadata... and this initialization also contains directory creation.

[calebeaires]

On my case I must create the table even if has corruption to get the flow done. Here is how I handle this problem. I dont know if it is the best to do.

      // drop first (if table exists)
      // then

       try {
            // create table
            return await this.db.query(
                `CREATE TABLE IF NOT EXISTS 
                    dbase.tableName
                    (someField String) 
                ENGINE = MergeTree
                ORDER BY tuple()`
            );
        } catch (e) {
            // maybe folder already exist
            // https://github.com/ClickHouse/ClickHouse/issues/11432
            return await this.db.query(
                `ATTACH TABLE IF NOT EXISTS 
                    dbase.tableName
                    (someField String) 
                ENGINE = MergeTree
                ORDER BY tuple()`
            );
        }

[alexeyerm]

Reproduced in v20.5.2.7:

CREATE TABLE db.tbl ... 
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/db.tbl', 'r01')

Received exception from server (version 20.5.2):
Code: 62. DB::Exception: No macro 'shard' in config while processing substitutions in '/clickhouse/tables/{shard}/db.tbl' at 20.

CREATE TABLE db.tbl ... 
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/01/db.tbl', 'r01')

Received exception from server (version 20.5.2):
Code: 57. DB::Exception: Directory for table data data/db/tbl/ already exists.