Restore error "Mapping for table with UUID already exists"

[bmailhe]

When trying to restore a remote backup on an another cluster (empty) I got this error :

2023/12/18 15:48:20.576784 error can't create table `mydb`.`mytable`: code: 57, message: There was an error on [myserver:9000]: Code: 57. DB::Exception: Mapping for table with UUID=7e8fb205-fa31-4fb2-9a76-e4d3aaa3a423 already exists. It happened due to UUID collision, most likely because some not random UUIDs were manually specified in CREATE queries. (TABLE_ALREADY_EXISTS) (version 23.8.8.20 (official build)) after 1 times, please check your schema dependencies

Indeed, in the backup there is uuid in the create table :

$ grep -r 7e8fb205-fa31-4fb2-9a76-e4d3aaa3a423 /var/lib/clickhouse/backup/
/var/lib/clickhouse/backup/19MZ-FULL-mydb-mytable-231214010001/metadata/mydb/mytable.json:      "query": "CREATE TABLE mydb.mytable UUID '7e8fb205-fa31-4fb2-9a76-e4d3aaa3a423' (`day` DateTime('Etc/UTC') CODEC(DoubleDelta, LZ4), `slug` LowCardinality(String) CODEC(ZSTD(1)), `symbol` LowCardinality(String) CODEC(ZSTD(1))) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/{cluster}/tables/mydb/mytable', '{replica}') PARTITION BY toYYYYMM(day) ORDER BY (symbol, slug, day) SETTINGS index_granularity = 8192",

[Slach]

Could you share

SELECT uuid, database, table FROM system.tables WHERE uuid='7e8fb205-fa31-4fb2-9a76-e4d3aaa3a42'

in destination server?

[bmailhe]

# running `ls -a /var/lib/clickhouse/store/7e8` on all servers in the cluster:

server00 | CHANGED | rc=0 >>
.
..
server00 | FAILED | rc=2 >>
ls: cannot access '/var/lib/clickhouse/store/7e8': No such file or directorynon-zero return code

server02| FAILED | rc=2 >>
ls: cannot access '/var/lib/clickhouse/store/7e8': No such file or directorynon-zero return code

[Slach]

Try to rm -rfv /var/lib/clickhouse/store/7e8 on server00 after that restart server00 and try to manually execute

CREATE TABLE mydb.mytable UUID '7e8fb205-fa31-4fb2-9a76-e4d3aaa3a423' (
`day` DateTime('Etc/UTC') CODEC(DoubleDelta, LZ4), 
`slug` LowCardinality(String) CODEC(ZSTD(1)), 
`symbol` LowCardinality(String) CODEC(ZSTD(1))
) 
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/{cluster}/tables/mydb/mytable', '{replica}') 
PARTITION BY toYYYYMM(day) ORDER BY (symbol, slug, day) 
SETTINGS index_granularity = 8192

if success execute DROP TABLE IF EXISTS mydb.mytable ON CLUSTER '{cluster}' SYNC
and execute restore again
and share full logs if failed

[bmailhe]

I fully re-installed the cluster from scratch, as it was a new one. (rm -rf /var/lib/clickhouse/*+ re install)

Table creation

mycluster :) CREATE TABLE mydb.mytable UUID '7e8fb205-fa31-4fb2-9a76-e4d3aaa3a423' (
`day` DateTime('Etc/UTC') CODEC(DoubleDelta, LZ4),
`slug` LowCardinality(String) CODEC(ZSTD(1)),
`symbol` LowCardinality(String) CODEC(ZSTD(1))
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/{cluster}/tables/mydb/mytable', '{replica}')
PARTITION BY toYYYYMM(day) ORDER BY (symbol, slug, day)
SETTINGS index_granularity = 8192

CREATE TABLE mydb.mytable UUID '7e8fb205-fa31-4fb2-9a76-e4d3aaa3a423'
(
    `day` DateTime('Etc/UTC') CODEC(DoubleDelta, LZ4),
    `slug` LowCardinality(String) CODEC(ZSTD(1)),
    `symbol` LowCardinality(String) CODEC(ZSTD(1))
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/{cluster}/tables/mydb/mytable', '{replica}')
PARTITION BY toYYYYMM(day)
ORDER BY (symbol, slug, day)
SETTINGS index_granularity = 8192

Query id: 1e337780-1704-4d1c-a31f-c28ad04c0999

Ok.

0 rows in set. Elapsed: 0.348 sec.

mycluster :) DROP TABLE IF EXISTS mydb.mytable ON CLUSTER '{cluster}' SYNC

DROP TABLE IF EXISTS mydb.mytable ON CLUSTER `{cluster}` SYNC

Query id: 70e4830a-73a0-492e-b7b2-235e9f71ddee

┌─host───────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse01-mycluster │ 9000 │      0 │       │                   2 │                2 │
└────────────────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
┌─host───────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse00-mycluster │ 9000 │      0 │       │                   1 │                1 │
└────────────────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
┌─host───────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse02-mycluster │ 9000 │      0 │       │                   0 │                0 │
└────────────────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

3 rows in set. Elapsed: 0.253 sec.

mycluster :)

Restore

$ clickhouse-backup -c /etc/clickhouse-backup/config.yml restore_remote  19MZ-FULL-mydb-mytable-231214010001 --rm
2023/12/19 09:23:00.130836  info clickhouse connection prepared: tcp://localhost:9000 run ping logger=clickhouse
2023/12/19 09:23:00.133110  info clickhouse connection open: tcp://localhost:9000 logger=clickhouse
2023/12/19 09:23:00.133123  info SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER' logger=clickhouse
2023/12/19 09:23:00.134286  info SELECT count() is_disk_type_present FROM system.columns WHERE database='system' AND table='disks' AND name='type' logger=clickhouse
2023/12/19 09:23:00.136250  info SELECT path, any(name) AS name, any(type) AS type FROM system.disks GROUP BY path logger=clickhouse
2023/12/19 09:23:00.137957  warn 19MZ-FULL-mydb-mytable-231214010001 already exists will try to resume download backup=19MZ-FULL-mydb-mytable-231214010001 logger=backuper operation=download
2023/12/19 09:23:00.137983  info SELECT max(toInt64(bytes_on_disk * 1.02)) AS max_file_size FROM system.parts logger=clickhouse
2023/12/19 09:23:00.139363  info SELECT count() AS is_macros_exists FROM system.tables WHERE database='system' AND name='macros'  SETTINGS empty_result_for_aggregation_by_empty_set=0 logger=clickhouse
2023/12/19 09:23:00.140897  info SELECT macro, substitution FROM system.macros logger=clickhouse
2023/12/19 09:23:01.191374  info /var/lib/clickhouse/backup/19MZ-FULL-mydb-mytable-231214010001/metadata/mydb/mytable.json already processed logger=resumable
2023/12/19 09:23:01.191874  info done                      backup=19MZ-FULL-mydb-mytable-231214010001 duration=1ms logger=backuper operation=download size=13.23KiB table_metadata=mydb.mytable
2023/12/19 09:23:01.191998  info 19MZ-FULL-mydb-mytable-231214010001/shadow/mydb/mytable/default_201504_0_1_1.tar already processed logger=resumable
2023/12/19 09:23:01.192027  info 19MZ-FULL-mydb-mytable-231214010001/shadow/mydb/mytable/default_201506_0_1_1.tar already processed logger=resumable
2023/12/19 09:23:01.192002  info 19MZ-FULL-mydb-mytable-231214010001/shadow/mydb/mytable/default_201508_0_1_1.tar already processed logger=resumable
2023/12/19 09:23:01.192012  info 19MZ-FULL-mydb-mytable-231214010001/shadow/mydb/mytable/default_201507_0_1_1.tar already processed logger=resumable
...
2023/12/19 09:23:01.197433  info done                      diff_parts=0 duration=1ms logger=backuper operation=downloadDiffParts
2023/12/19 09:23:01.197463  info done                      backup=19MZ-FULL-mydb-mytable-231214010001 duration=6ms logger=backuper operation=download_data size=10.45MiB table=mydb.mytable
2023/12/19 09:23:01.513302  info done                      backup=19MZ-FULL-mydb-mytable-231214010001 duration=1.375s logger=backuper operation=download size=10.46MiB
2023/12/19 09:23:01.513387  info clickhouse connection closed logger=clickhouse
2023/12/19 09:23:01.513431  info clickhouse connection prepared: tcp://localhost:9000 run ping logger=clickhouse
2023/12/19 09:23:01.515880  info clickhouse connection open: tcp://localhost:9000 logger=clickhouse
2023/12/19 09:23:01.515901  info SELECT count() is_disk_type_present FROM system.columns WHERE database='system' AND table='disks' AND name='type' logger=clickhouse
2023/12/19 09:23:01.518310  info SELECT path, any(name) AS name, any(type) AS type FROM system.disks GROUP BY path logger=clickhouse
2023/12/19 09:23:01.520032  info SELECT count() AS is_macros_exists FROM system.tables WHERE database='system' AND name='macros'  SETTINGS empty_result_for_aggregation_by_empty_set=0 logger=clickhouse
2023/12/19 09:23:01.521946  info SELECT macro, substitution FROM system.macros logger=clickhouse
2023/12/19 09:23:01.522693  info CREATE DATABASE IF NOT EXISTS `mydb`  ON CLUSTER 'mycluster' ENGINE = Atomic with args [[]] logger=clickhouse
2023/12/19 09:23:01.625009  info SELECT engine FROM system.databases WHERE name = 'mydb' logger=clickhouse
2023/12/19 09:23:01.626387  info DROP TABLE IF EXISTS `mydb`.`mytable` ON CLUSTER 'mycluster'  NO DELAY logger=clickhouse
2023/12/19 09:23:01.711066  info CREATE DATABASE IF NOT EXISTS `mydb` ON CLUSTER 'mycluster' logger=clickhouse
2023/12/19 09:23:01.795498  info CREATE TABLE mydb.mytable UUID '7e8fb205-fa31-4fb2-9a76-e4d3aaa3a423'  ON CLUSTER 'mycluster' (`day` DateTime('Etc/UTC') CODEC(DoubleDelta, LZ4), `slug` LowCardinality(String) CODEC(ZSTD(1)), `symbol` LowCardinality(String) CODEC(ZSTD(1))) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/{cluster}/tables/mydb/mytable', '{replica}') PARTITION BY toYYYYMM(day) ORDER BY (symbol, slug, day) SETTINGS index_granularity = 8192 logger=clickhouse
2023/12/19 09:23:06.142041  info clickhouse connection closed logger=clickhouse
2023/12/19 09:23:06.142080 error can't create table `mydb`.`mytable`: code: 253, message: There was an error on [clickhouse01-mycluster:9000]: Code: 253. DB::Exception: Replica /clickhouse/mycluster/tables/mydb/mytable/replicas/02 already exists. (REPLICA_ALREADY_EXISTS) (version 23.8.8.20 (official build)) after 1 times, please check your schema dependencies
$

ℹ️ for privacy, I replaced the cluster name (mycluster), db name (mydb), table name (mytable) and hostnames.

[bmailhe]

I'll flush the clickhouse keeper and redo the test as the REPLICA_ALREADY_EXISTS probably comes from here.

[bmailhe]

It works well now, I'm able to restore the backup. Thank you for your help 🙏