Closed
Description
Hello, I've encountered issue with executing restore on the same existing DB when specifying --rm flag - it complains on the CREATE TABLE:
2025-04-03 16:56:12.665 INF pkg/clickhouse/clickhouse.go:1123 > CREATE TABLE `test-db`.t1 UUID 'cdcb121e-0c4b-4409-9508-9fa34d796f3c' (`DateTime` DateTime, `Name` String) ENGINE = ReplicatedMergeTree('/clickhouse/tables/cdcb121e-0c4b-4409-9508-9fa34d796f3c/{shard}', '{replica}') PARTITION BY toDate(DateTime) ORDER BY DateTime SETTINGS index_granularity = 8192
2025-04-03 16:56:12.926 WRN pkg/backup/restore.go:1165 > can't create table 'test-db.t1': code: 57, message: Table `test-db`.t1 already exists, will try againDetails
Setup
- Clickhouse cluster via Bitnami chart
7.1.4(along with their Zookeeper). Imagebitnami/clickhouse:24.12.2-debian-12-r0 - 2 Shards x 2 Replicas. Cluster called
cluster clickhouse-backupfrom imagealtinity/clickhouse-backup:2.6.13running in Clickhouse pods as a sidecar- Settings
database_replicated_allow_explicit_uuidanddatabase_replicated_allow_replicated_engine_argumentsare enabled (set to 1)
Steps to reproduce
- Create a DB, tables and (optional) some data:
CREATE DATABASE IF NOT EXISTS `test-db` ON CLUSTER cluster ENGINE = Replicated('/clickhouse/databases/test-db', '{replica}');
USE `test-db`;
CREATE TABLE IF NOT EXISTS `t1`
(
DateTime DateTime,
Name String
)
ENGINE = ReplicatedMergeTree()
PARTITION BY toDate(DateTime)
ORDER BY DateTime;
CREATE TABLE IF NOT EXISTS `t1-d` AS `t1`
ENGINE = Distributed('cluster', 'test-db', 't1', sipHash64(toDate(DateTime)));
INSERT INTO `t1` (DateTime, Name)
VALUES (now(), 'name1'), (now(), 'name2'), (now(), 'name3')
SELECT * FROM `t1-d`- Consider shard0: create a full backup on replica 0, upload it then also download (schema only) on replica 1:
k exec -it -n infra clickhouse-shard0-0 -c backup-sidecar -- curl -X POST "localhost:7171/backup/create?name=test22-shard0"
k exec -it -n infra clickhouse-shard0-0 -c backup-sidecar -- curl -X POST "localhost:7171/backup/upload/test22-shard0"
k exec -it -n infra clickhouse-shard0-1 -c backup-sidecar -- curl -X POST "localhost:7171/backup/download/test22-shard0?schema=true"- Execute restore of schema with
--rmflag on replica 0 of shard0 and check the error:
> k exec -it -n infra clickhouse-shard0-0 -c backup-sidecar -- clickhouse-backup restore -t "test-db.*" --rm -s test22-shard0
2025-04-03 16:56:12.313 INF pkg/clickhouse/clickhouse.go:131 > clickhouse connection prepared: tcp://localhost:9000 run ping
2025-04-03 16:56:12.315 INF pkg/clickhouse/clickhouse.go:134 > clickhouse connection success: tcp://localhost:9000
2025-04-03 16:56:12.315 INF pkg/clickhouse/clickhouse.go:1123 > SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2025-04-03 16:56:12.321 INF pkg/clickhouse/clickhouse.go:1123 > SELECT countIf(name='type') AS is_disk_type_present, countIf(name='object_storage_type') AS is_object_storage_type_present, countIf(name='free_space') AS is_free_space_present, countIf(name='disks') AS is_storage_policy_present FROM system.columns WHERE database='system' AND table IN ('disks','storage_policies')
2025-04-03 16:56:12.325 INF pkg/clickhouse/clickhouse.go:1123 > SELECT d.path AS path, any(d.name) AS name, any(lower(if(d.type='ObjectStorage',d.object_storage_type,d.type))) AS type, min(d.free_space) AS free_space, groupUniqArray(s.policy_name) AS storage_policies FROM system.disks AS d LEFT JOIN (SELECT policy_name, arrayJoin(disks) AS disk FROM system.storage_policies) AS s ON s.disk = d.name GROUP BY d.path
2025-04-03 16:56:12.335 INF pkg/clickhouse/clickhouse.go:1123 > DROP DATABASE IF EXISTS `test-db` SYNC
2025-04-03 16:56:12.546 INF pkg/clickhouse/clickhouse.go:1121 > CREATE DATABASE IF NOT EXISTS `test-db` ENGINE = Replicated('/clickhouse/databases/test-db', '{replica}', '{replica}') with args []interface {}{[]interface {}(nil)}
2025-04-03 16:56:12.645 INF pkg/clickhouse/clickhouse.go:1123 > SELECT engine FROM system.databases WHERE name = 'test-db'
2025-04-03 16:56:12.648 INF pkg/clickhouse/clickhouse.go:1123 > DROP TABLE IF EXISTS `test-db`.`t1`
2025-04-03 16:56:12.649 INF pkg/clickhouse/clickhouse.go:1123 > SELECT engine FROM system.databases WHERE name = 'test-db'
2025-04-03 16:56:12.651 INF pkg/clickhouse/clickhouse.go:1123 > DROP TABLE IF EXISTS `test-db`.`t1-d`
2025-04-03 16:56:12.652 INF pkg/clickhouse/clickhouse.go:1123 > CREATE DATABASE IF NOT EXISTS `test-db`
2025
A26B
-04-03 16:56:12.653 INF pkg/clickhouse/clickhouse.go:1123 > SELECT count() AS is_macros_exists FROM system.tables WHERE database='system' AND name='macros' SETTINGS empty_result_for_aggregation_by_empty_set=0
2025-04-03 16:56:12.656 INF pkg/clickhouse/clickhouse.go:1123 > SELECT macro, substitution FROM system.macros
2025-04-03 16:56:12.658 INF pkg/clickhouse/clickhouse.go:1123 > SELECT count() AS is_macros_exists FROM system.tables WHERE database='system' AND name='macros' SETTINGS empty_result_for_aggregation_by_empty_set=0
2025-04-03 16:56:12.661 INF pkg/clickhouse/clickhouse.go:1123 > SELECT macro, substitution FROM system.macros
2025-04-03 16:56:12.662 INF pkg/clickhouse/clickhouse.go:1121 > SELECT count() FROM system.zookeeper WHERE path=? with args []interface {}{"/clickhouse/tables/cdcb121e-0c4b-4409-9508-9fa34d796f3c/shard0/replicas/clickhouse-shard0-0"}
2025-04-03 16:56:12.665 INF pkg/clickhouse/clickhouse.go:1123 > CREATE TABLE `test-db`.t1 UUID 'cdcb121e-0c4b-4409-9508-9fa34d796f3c' (`DateTime` DateTime, `Name` String) ENGINE = ReplicatedMergeTree('/clickhouse/tables/cdcb121e-0c4b-4409-9508-9fa34d796f3c/{shard}', '{replica}') PARTITION BY toDate(DateTime) ORDER BY DateTime SETTINGS index_granularity = 8192
2025-04-03 16:56:12.926 WRN pkg/backup/restore.go:1165 > can't create table 'test-db.t1': code: 57, message: Table `test-db`.t1 already exists, will try again
2025-04-03 16:56:12.926 INF pkg/clickhouse/clickhouse.go:1123 > CREATE TABLE `test-db`.`t1-d` UUID '7c9ebacf-5e07-48d2-a8de-ac9639293dd6' (`DateTime` DateTime, `Name` String) ENGINE = Distributed('cluster', 'test-db', 't1', sipHash64(toDate(DateTime)))
2025-04-03 16:56:12.958 INF pkg/clickhouse/clickhouse.go:336 > clickhouse connection closed
2025-04-03 16:56:12.958 FTL cmd/clickhouse-backup/main.go:742 > error="can't create table `test-db`.`t1-d`: code: 57, message: Table `test-db`.`t1-d` already exists after 2 times, please check your schema dependencies"Expected result
Recreating tables should work with flag --rm when restoring with existing DB + tables.
Possibly expecting the same command to work multiple times on the same replica
Actual result
CREATE TABLE fails with Table X already exists
Additional details
If try to restore with database remapping flag, e.g. adding -m "test-db:test-db2" - then I can even execute the same restore command multiple times on the same replica, see the logs:
> k exec -it -n infra clickhouse-shard0-0 -c backup-sidecar -- clickhouse-backup restore -t "test-db.*" --rm -s -m "test-db:test-db2" test22-shard0
2025-04-03 16:56:33.707 INF pkg/clickhouse/clickhouse.go:131 > clickhouse connection prepared: tcp://localhost:9000 run ping
2025-04-03 16:56:33.735 INF pkg/clickhouse/clickhouse.go:134 > clickhouse connection success: tcp://localhost:9000
2025-04-03 16:56:33.736 INF pkg/clickhouse/clickhouse.go:1123 > SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2025-04-03 16:56:33.747 INF pkg/clickhouse/clickhouse.go:1123 > SELECT countIf(name='type') AS is_disk_type_present, countIf(name='object_storage_type') AS is_object_storage_type_present, countIf(name='free_space') AS is_free_space_present, countIf(name='disks') AS is_storage_policy_present FROM system.columns WHERE database='system' AND table IN ('disks','storage_policies')
2025-04-03 16:56:33.780 INF pkg/clickhouse/clickhouse.go:1123 > SELECT d.path AS path, any(d.name) AS name, any(lower(if(d.type='ObjectStorage',d.object_storage_type,d.type))) AS type, min(d.free_space) AS free_space, groupUniqArray(s.policy_name) AS storage_policies FROM system.disks AS d LEFT JOIN (SELECT policy_name, arrayJoin(disks) AS disk FROM system.storage_policies) AS s ON s.disk = d.name GROUP BY d.path
2025-04-03 16:56:33.821 INF pkg/clickhouse/clickhouse.go:1123 > SELECT engine FROM system.databases WHERE name = 'test-db2'
2025-04-03 16:56:33.828 INF pkg/clickhouse/clickhouse.go:1123 > DROP TABLE IF EXISTS `test-db2`.`t1` NO DELAY
2025-04-03 16:56:35.045 INF pkg/clickhouse/clickhouse.go:1123 > SELECT engine FROM system.databases WHERE name = 'test-db2'
2025-04-03 16:56:35.050 INF pkg/clickhouse/clickhouse.go:1123 > DROP TABLE IF EXISTS `test-db2`.`t1-d` NO DELAY
2025-04-03 16:56:35.053 INF pkg/clickhouse/clickhouse.go:1123 > CREATE DATABASE IF NOT EXISTS `test-db2`
2025-04-03 16:56:35.054 INF pkg/clickhouse/clickhouse.go:1123 > SELECT count() AS is_macros_exists FROM system.tables WHERE database='system' AND name='macros' SETTINGS empty_result_for_aggregation_by_empty_set=0
2025-04-03 16:56:35.068 INF pkg/clickhouse/clickhouse.go:1123 > SELECT macro, substitution FROM system.macros
2025-04-03 16:56:35.071 INF pkg/clickhouse/clickhouse.go:1123 > SELECT count() AS is_macros_exists FROM system.tables WHERE database='system' AND name='macros' SETTINGS empty_result_for_aggregation_by_empty_set=0
2025-04-03 16:56:35.077 INF pkg/clickhouse/clickhouse.go:1123 > SELECT macro, substitution FROM system.macros
2025-04-03 16:56:35.080 INF pkg/clickhouse/clickhouse.go:1121 > SELECT count() FROM system.zookeeper WHERE path=? with args []interface {}{"/clickhouse/tables/9924904e-10ac-11f0-af13-96a83811b550/shard0/replicas/clickhouse-shard0-0"}
2025-04-03 16:56:35.118 INF pkg/clickhouse/clickhouse.go:1123 > CREATE TABLE `test-db2`.t1 UUID '9924904e-10ac-11f0-af13-96a83811b550' (`DateTime` DateTime, `Name` String) ENGINE = ReplicatedMergeTree('/clickhouse/tables/9924904e-10ac-11f0-af13-96a83811b550/{shard}', '{replica}') PARTITION BY toDate(DateTime) ORDER BY DateTime SETTINGS index_granularity = 8192
2025-04-03 16:56:35.877 INF pkg/clickhouse/clickhouse.go:1123 > CREATE TABLE `test-db2`.`t1-d` UUID '992495cf-10ac-11f0-af13-96a83811b550' (`DateTime` DateTime, `Name` String) ENGINE = Distributed('cluster', 'test-db2', 't1', sipHash64(toDate(DateTime)))
2025-04-03 16:56:35.898 INF pkg/backup/restore.go:941 > done backup=test22-shard0 duration=2.077s operation=restore_schema
2025-04-03 16:56:35.899 INF pkg/backup/restore.go:261 > done duration=2.198s operation=restore version=2.6.13
2025-04-03 16:56:35.901 INF pkg/clickhouse/clickhouse.go:336 > clickhouse connection closedHere it adds NO DELAY to the DROP TABLE command (btw I think it is now called SYNC in newer Clickhouse versions) - then following CREATE TABLE works just fine (again, even multiple times on the same replica). Maybe that's the solution?