Clickhouse backup create errors with "directory already exists and is not empty"

[sushraju]

/bin/clickhouse-backup create 
2021/08/10 23:47:55 debug SELECT name, engine FROM system.databases WHERE name != 'system'
2021/08/10 23:47:55 debug SHOW CREATE DATABASE `_temporary_and_external_tables`
2021/08/10 23:47:55 debug SHOW CREATE DATABASE `default`
2021/08/10 23:47:55 debug SELECT count() FROM system.settings WHERE name = 'show_table_uuid_in_table_create_query_if_not_nil'
2021/08/10 23:47:55 debug SELECT * FROM system.tables WHERE is_temporary = 0 SETTINGS show_table_uuid_in_table_create_query_if_not_nil=1
2021/08/10 23:47:55 debug SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2021/08/10 23:47:55 debug SELECT * FROM system.disks;
2021/08/10 23:47:55 debug SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2021/08/10 23:47:55 debug SELECT * FROM system.disks;
2021/08/10 23:47:55 debug SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2021/08/10 23:47:55 debug SELECT * FROM system.disks;
2021/08/10 23:47:55 debug create data               backup=FULL_20210810_2347 operation=create table=default.enriched_video_access_log_records
2021/08/10 23:47:55 debug SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2021/08/10 23:47:55 debug SELECT * FROM system.disks;
2021/08/10 23:47:55 debug SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2021/08/10 23:47:55 debug SYSTEM SYNC REPLICA `default`.`enriched_video_access_log_records`;
2021/08/10 23:47:55 debug replica synced            table=default.enriched_video_access_log_records
2021/08/10 23:47:55 debug ALTER TABLE `default`.`enriched_video_access_log_records` FREEZE WITH NAME '9af0950fbc0c4d45a4f1ba7e10da2666';
2021/08/10 23:49:33 error can't freeze table: code: 84, message: Directory /var/lib/clickhouse/shadow/9af0950fbc0c4d45a4f1ba7e10da2666/data/default/enriched_video_access_log_records/20201105_0_9627_12 already exists and is not empty. backup=FULL_20210810_2347 operation=create table=default.enriched_video_access_log_records
2021/08/10 23:49:33 debug SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2021/08/10 23:49:33 debug SELECT * FROM system.disks;
2021/08/10 23:49:33 debug SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2021/08/10 23:49:33 debug SELECT * FROM system.disks;
2021/08/10 23:49:33 debug remove 'FULL_20210810_2347' path=/var/lib/clickhouse/
2021/08/10 23:49:33  info done                      backup=FULL_20210810_2347 location=local operation=delete
2021/08/10 23:49:33 error can't freeze table: code: 84, message: Directory /var/lib/clickhouse/shadow/9af0950fbc0c4d45a4f1ba7e10da2666/data/default/enriched_video_access_log_records/20201105_0_9627_12 already exists and is not empty.

Oddly enough, the alter table freeze with name completes successfully when run from clickhouse-client.

ALTER TABLE <table> FREEZE WITH NAME 'issue_27532';

ALTER TABLE <table>
    FREEZE WITH NAME 'issue_27532'


Ok.

Wondering why it would fail only when the clickhouse-backup is executing it.

"SELECT version();"
20.9.3.45

Clickhouse-backup - 1.0.0

[Slach]

could you run

rm -rfv /var/lib/clickhouse/shadow/9af0950fbc0c4d45a4f1ba7e10da2666
clickhouse-backup create

and share the results?

[sushraju]

Thanks for looking into this.

I tried again and the error was exactly same as what I shared earlier.

[sushraju]

The way I have worked around the issue is by also setting a read_timeout in the connection string. For large clusters (with a lot of parts), alter table freeze takes close to 90 seconds. I think the read timeout ('m not sure) is probably causing the operation to restart and maybe causing the issue. A higher value works in my case.

[JohnHaret]

i got similar trouble when i create backup with clickhouse-backup Version: v0.5.1
in the metadata files i saw
CREATE TABLE adstat_all
(
date DateTime,
url String,
country String,
spot_id UInt64,
spot_name String,
impressions UInt64,
clicks UInt64,
revenue Float64
)
ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMMDD(date)
ORDER BY date
SETTINGS index_granularity = 8192
and when i try restore this metadata with clickhouse-backup Version: 1.0.0
I got help from Slach [altinity] (telegram).
He thinks that this is a problem when restoring backups from the Legacy backup in the new version. He said, that need add "use database" before restore

[AlexAkulov]

Freezing large tables is possible for more than 60s. For this case, read_timeout was added, you can set it to 600s or more.
As I understand it, you solved your problem?

[freemanlutsk]

I've faced a similar error. Where should I set read_timeout ? I didn't find any mentions of this parameter in the documentation.
Please advice.
Thank You!

[sushraju]

I added as shown here and built it for my purpose. This wasn't merged to master.

#247

[freemanlutsk]

@AlexAkulov Hello!

Is there any way to add read_timeout to config or merge the PR #247?

Thank You!

[Slach]

@freemanlutsk thanks a lot for suggestion
it's wonder but clickhouse-go supports both read_timeout \ write_timeout on client side and receive_timeout and send_timeout on server side

Could you try to the latest master and report here, if you'll success with read_timeout?

[Slach]

@freemanlutsk just change

clickhouse:
  timeout: XXX

section in config yaml

[freemanlutsk]

@Slach Yes I can try. Would you be able to push the image into the dockerhub?

[Slach]

@freemanlutsk try to use altinity/clickhouse-backup:master

[Slach]

@freemanlutsk https://github.com/AlexAkulov/clickhouse-backup/releases/tag/v1.0.0