Restore backup with check_table_dependencies settings

[ViktorNazarenko-ST]

I've found one issue, if in backup i want to restore some dictionaries were renamed - then there will be error like:

04:45:54 2022/06/18 02:45:54.080308  warn can't drop table 'default.source_table': code: 630, message: Cannot drop or rename default.source_table, because some tables depend on it: default.dictionary_for_source_table, default.dictionary_for_source_table2, will try again backup=2022-06-18 operation=restore

Is there some way to set check_table_dependencies before restore?

[Slach]

You tried to restore in server with data and schema exists?

[ViktorNazarenko-ST]

Yes, i have 1 server and 1 with backups. I tried to restore backup on other server.

[Slach]

look like you see warn not error what exactly means "renamed" ?

[ViktorNazarenko-ST]

it tries to delete table several times, and then fail to restore backup.

Rename mean that initially it was dictionary dictionary_for_source_table_old then i do

   CREATE DICTIONARY dictionary_for_source_table ON source_table;
   DROP DICTIONARY dictionary_for_source_table_old

So on backup server database contain dictionary_for_source_table_old ( due to previous backup ) And during new backup restoration - i start to get that can't delete dictionary warns and backup restoration fails

2022/06/18 02:45:54.304393 error can't drop table `default`.`source_table `: code: 630, message: Cannot drop or rename default.source_table, because some tables depend on it: default. dictionary_for_source_table, default. dictionary_for_source_table2 after 62 times, please check your schema dependencies

[Slach]

did you tried clickhouse-backup restore --rm <backup_name> ?

by default clickhouse-backup operated only with objects which contains in backup metadata and know nothing about objects which already present in your current database

Unfortunatelly, i don't know good method (which will works with 19.x and 20.x clickhouse versions) to look all oject dependencies in clickhouse, if you can suggest, please

[ViktorNazarenko-ST]

Yes backup restore i do with --rm options, but looks like it's not drop full database - but only object exists in backup, one by one. The only option i could imagine now is support of check_table_dependencies=0 for drop statements on restore, but it also could be dangerous.

[Slach]

ok. understood, ok. option make sense

[Slach]

@ViktorNazarenko-ST if we add check_table_dependencies=0 then dictionary_for_source_table_old stay exists would be better implements delete dependencies first?

[NickStepanov]

@Slach I think this parameter --ignore_dependencies wasn't added to the documentation in the readme? Does it execute DROP DDL commands with check_table_dependencies set to 0? If I read this part of the code correctly: https://github.com/AlexAkulov/clickhouse-backup/blob/6021fe09e28a5da0da724d3e3701fc04e7615365/pkg/clickhouse/clickhouse.go#L654

[Slach]

@NickStepanov clickhouse-backup help restore show this parameter https://github.com/AlexAkulov/clickhouse-backup/blob/master/ReadMe.md?plain=1#L183-L198

[NickStepanov]

@Slach Possibly, I am doing something wrong, but even if I use this option, I still can't drop the DB. It complaints that there is a dictionary depending on a table in another DB.

LOG_LEVEL=debug clickhouse-backup restore_remote --rm --schema --tables=db_name.* --ignore-dependencies shard01_db_name_schema
2023/05/03 10:39:16.601707  info clickhouse connection prepared: tcp://host_name:9000 run ping logger=clickhouse
2023/05/03 10:39:16.630936  info clickhouse connection open: tcp://host_name:9000 logger=clickhouse
2023/05/03 10:39:16.631045  info SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER' logger=clickhouse
2023/05/03 10:39:16.636825  info SELECT * FROM system.disks; logger=clickhouse
2023/05/03 10:39:16.647002  warn shard01_db_name_schema already exists will try to resume download backup=shard01_db_name_schema logger=backuper operation=download
2023/05/03 10:39:16.647086  info SELECT max(toInt64(bytes_on_disk * 1.02)) AS max_file_size FROM system.parts logger=clickhouse
2023/05/03 10:39:16.763174  info SELECT count() AS is_macros_exists FROM system.tables WHERE database='system' AND name='macros' logger=clickhouse
2023/05/03 10:39:16.773036  info SELECT * FROM system.macros logger=clickhouse
2023/05/03 10:39:16.806008 debug /tmp/.clickhouse-backup-metadata.cache.S3 load 481 elements logger=s3
2023/05/03 10:39:17.592766 debug /tmp/.clickhouse-backup-metadata.cache.S3 save 481 elements logger=s3
2023/05/03 10:39:17.593595 debug prepare table METADATA concurrent semaphore with concurrency=2 len(tablesForDownload)=84 backup=shard01_db_name_schema logger=backuper operation=download
....
2023/05/03 10:39:17.613455  info done                      backup=shard01_db_name_schema duration=966ms logger=backuper operation=download size=40.92KiB
2023/05/03 10:39:17.613588  info clickhouse connection closed logger=clickhouse
2023/05/03 10:39:17.613845  info clickhouse connection prepared: tcp://host_name:9000 run ping logger=clickhouse
2023/05/03 10:39:17.622361  info clickhouse connection open: tcp://host_name:9000 logger=clickhouse
2023/05/03 10:39:17.622413  info SELECT * FROM system.disks; logger=clickhouse
2023/05/03 10:39:17.631769  info SELECT count() AS is_macros_exists FROM system.tables WHERE database='system' AND name='macros' logger=clickhouse
2023/05/03 10:39:17.641875  info SELECT * FROM system.macros logger=clickhouse
2023/05/03 10:39:17.649356  info DROP DATABASE IF EXISTS `db_name`  ON CLUSTER 'my_cluster' SYNC logger=clickhouse
2023/05/03 10:39:17.767762  info clickhouse connection closed logger=clickhouse
2023/05/03 10:39:17.768028 error code: 630, message: There was an error on [host_3:9000]: Code: 630. DB::Exception: Cannot drop or rename db_name.table_name, because some tables depend on it: dictionaries.table_name. (HAVE_DEPENDENT_OBJECTS) (version 22.8.8.3 (official build))

Am I doing something wrong? This is cluster with 4 nodes.

[NickStepanov]

I think it just doesn't apply dropQuery += " SETTINGS check_table_dependencies=0" to drop database statement, when I tested with a single table - it did work and I can see SETTINGS check_table_dependencies=0 logger=clickhouse being added to the DROP TABLE statement. But for DROP DATABASE - it's not added.

[Slach]

@NickStepanov

do you use restore_schema_on_cluster: my_cluster option?

[Slach]

Why do you think DROP DATABASE IF EXISTS db ON CLUSTER 'cluster_name' SYNC SETTINGS check_table_dependencies=0 will help?

[NickStepanov]

@NickStepanov

do you use restore_schema_on_cluster: my_cluster option?

Yes, I do.

Why do you think DROP DATABASE IF EXISTS db ON CLUSTER 'cluster_name' SYNC SETTINGS check_table_dependencies=0 will help?

Well, when I do this command, and specify the table directly, it works just fine:

LOG_LEVEL=debug clickhouse-backup restore_remote --rm --schema --tables=db_name.table_name --ignore-dependencies shard01_db_name_schema

And it does add the SETTINGS check_table_dependencies=0 at the end of the DROP TABLE command.

I presume because this setting is missing from the DROP DATABASE, it fails?