ATTACH MATERIALIZED VIEW + ON CLUSTER doesn't work as expected

[shattl2000]

Hi,
I have a some problem with restore data from backup. When I try restore data from backup, restored only database without tables and data in they.
I use this commad for restore
clickhouse-backup restore 2022-02-10T07-24-02 --rm

When I try to restore a table from some database i get this error
clickhouse-backup restore 2022-02-10T07-24-02 --rm --table=bd2.bd2_sharded 2022/02/10 12:07:00.655248 error can't attach partitions for table 'bd2.bd2_sharded': code: 1000, message: Access to file denied: insufficient permissions: /var/lib/clickhouse/store/77a/77ae17d9-5adb-4c9c-a2de-2b1184e39c7d/detached/attaching_45ada9f87e7a3926379f51376de3c0e6_0_37_7

This issue occurs when using clickhouse-backup version 1.3.0.
When I use clickhouse-backup version 1.2.2 this problem doesn't exist. However, using version 1.2.2 does not create tables of type Distributed whose names match the name of the database.

[Slach]

How you download your backup to clickhouse server? Which type of remote_storage do you use in your config?

[Slach]

Could you share the following command?

id
ls -la /var/lib/clickhouse/backup/2022-02-10T07-24-02/shadow

[shattl2000]

I downloaded backup with command
clickhouse-backup download 2022-02-10T07-24-02
All backups stored on s3. I was made backup and upload to s3 with command
clickhouse-backup create_remote

Remote storage in config set s3
remote_storage: s3

My result commands

id
uid=0(root) gid=0(root) группы=0(root)

ls -la /var/lib/clickhouse/backup/2022-02-10T07-24-02/shadow
итого 4
drwxr-x--- 11 root root  143 фев 10 10:26 .
drwxr-x---  4 root root   57 фев 10 10:26 ..
drwxr-x---  6 root root  192 фев 10 10:26 bd1
drwxr-x---  4 root root   51 фев 10 10:26 bd2
drwxr-x---  5 root root   86 фев 10 10:26 bd3
drwxr-x---  5 root root  123 фев 10 10:26 bd4
drwxr-x---  5 root root   86 фев 10 10:26 bd5
drwxr-x--- 22 root root 4096 фев 10 10:26 bd6
drwxr-x---  3 root root   31 фев 10 10:26 bd7
drwxr-x---  4 root root   72 фев 10 10:26 bd8
drwxr-x---  4 root root   47 фев 10 10:26 system

[Slach]

Could you share the following commands results?

ls -la /var/lib/clickhouse/data
ls -la /var/lib/clickhouse/store

[shattl2000]

ls -la /var/lib/clickhouse/data
итого 0
drwxr-x--- 13 clickhouse clickhouse 171 фев 10 11:53 .
drwxrwx--- 16 clickhouse clickhouse 266 фев  9 17:28 ..
drwxr-x---  2 clickhouse clickhouse   6 фев 10 11:53 bd1
drwxr-x---  2 clickhouse clickhouse 204 фев 10 12:07 bd2
drwxr-x---  2 clickhouse clickhouse   6 фев 10 11:53 default
drwxr-x---  2 clickhouse clickhouse   6 фев 10 11:53 bd3
drwxr-x---  2 clickhouse clickhouse   6 фев 10 11:53 event
drwxr-x---  2 clickhouse clickhouse   6 фев 10 11:53 bd4
drwxr-x---  2 clickhouse clickhouse   6 фев 10 11:53 bd5
drwxr-x---  2 clickhouse clickhouse   6 фев 10 11:53 bd6
drwxr-x---  2 clickhouse clickhouse   6 фев 10 11:53 bd7
drwxr-x---  2 clickhouse clickhouse   6 фев 10 11:53 bd8
drwxr-x---  2 clickhouse clickhouse  47 фев  9 17:28 system

ls -la /var/lib/clickhouse/store
итого 0
drwxr-x--- 23 clickhouse clickhouse 237 фев 10 11:54 .
drwxrwx--- 16 clickhouse clickhouse 266 фев  9 17:28 ..
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:53 025
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:54 049
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:54 094
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:54 0af
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:53 143
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:54 14a
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:54 25e
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:53 335
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:54 342
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:53 58f
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:54 5c1
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:54 60c
drwxr-x---  3 clickhouse clickhouse  50 фев 10 12:07 77a
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:54 8ea
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:54 9c2
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:53 a5c
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:53 bad
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:54 bc7
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:53 ddc
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:53 f79
drwxr-x---  3 clickhouse clickhouse  50 фев 10 11:53 fe8

[Slach]

clickhouse-backup restore 2022-02-10T07-24-02 --rm
executed under root or under another user?

[shattl2000]

executed under root

[Slach]

hmm,
could you share SELECT * FROM system.disks ?

clickhouse-backup restore should do chown operation during restore operation

[Slach]

As quick workaround, just run chown -R clickhouse:clickhouse /var/lib/clickhouse/backups

[shattl2000]

select * from system.disks;

SELECT *
FROM system.disks

Query id: cdaba122-1634-44f7-9181-05f9169d9ad8

┌─name────┬─path─────────────────┬──free_space─┬──total_space─┬─keep_free_space─┬─type──┐
│ default │ /var/lib/clickhouse/ │ 97607118848 │ 343513497600 │               0 │ local │
└─────────┴──────────────────────┴─────────────┴──────────────┴─────────────────┴───────┘

1 rows in set. Elapsed: 0.002 sec.

[Slach]

I attached debug build where add log for chown operation
clickhouse-backup.zip

unpack zip into /tmp/ folder
could you run and share results for the following command

LOG_LEVEL=debug /tmp/clickhouse-backup restore 2022-02-10T07-24-02 --rm --table=bd2.bd2_sharded

[shattl2000]

I started restore with command
clickhouse-backup restore clickhouse_backup_2022-02-10T15-21-12 --schema

And I got a new error
warn can't create table 'bd6.table1': code: 62, message: Syntax error: failed at position 710 ('CLUSTER'): CLUSTER 'ch' (b.competition_id != '') AND (b.event_id = ''). Expected one of: UNION, LIMIT, WINDOW, LIKE, GLOBAL NOT IN, end of query, HAVING, AS, DIV, IS, GROUP BY, INTO OUTFILE, OR, QuestionMark, BETWEEN, OFFSET, NOT LIKE, MOD, AND, Comma, alias, ORDER BY, SETTINGS, IN, ILIKE, FORMAT, Dot, NOT ILIKE, WITH, NOT, Arrow, token, NOT IN, GLOBAL IN, will try again backup=clickhouse_backup_2022-02-10T15-21-12 operation=restore

When I run this command to create table in clickhouse it completes without error
"query": "ATTACH MATERIALIZED VIEW bd6.table1 UUID '27493bcc-f130-44c8-8e01-cdb87995321b' TO bd6.table2 (periodString,operator_idUInt64,competition_idString,gambling_type_idUInt32,bets_amountDecimal(16, 2),bets_countUInt64,is_deletedUInt8,updated_atDateTime64(3, 'UTC'),ver Int64) AS SELECT DISTINCT b.period AS period, b.operator_id AS operator_id, b.competition_id AS competition_id, b.gambling_type_id AS gambling_type_id, ifNull(b.bets_amount, toDecimal64(0, 2)) AS bets_amount, ifNull(b.bets_count, 0) AS bets_count, b.is_deleted AS is_deleted, b.updated_at AS updated_at, toUnixTimestamp64Milli(b.created_at) AS ver FROM bd6.table5 AS b WHERE (b.competition_id != '') AND (b.event_id = '')"

I took this command from a metadata file backup.
After manually creating the table, I ran the restore again
clickhouse-backup restore clickhouse_backup_2022-02-10T15-21-12 --data
And now I restored without errors.

[Slach]

Sorry, I don't understand what you do exactly
we tried to resolve issue with failed attach table and wrong permissions

look like your last message is a separate issue?

could you turn on export LOG_LEVEL=debug (environment variable) or change config.yml

general:
  log_level: debug

and share the result of
LOG_LEVEL=debug clickhouse-backup restore clickhouse_backup_2022-02-10T15-21-12 --shema ?

[shattl2000]

That's right, your last build you attached fixed that bug. The databases have been restored with tables. But now I got a new error that some tables could not be created, which I wrote about in a previous comment.

[Slach]

Looks strange, cause attached build is the same as 1.3.0, just add log about Chown operation, did you run chown -R clickhouse:clickhouse /var/lib/clickhouse/backup before executing attached debug build?

Ok. let's check your tables and try figure out,
could you share the following command result?

cat /var/lib/backups/clickhouse_backup_2022-02-10T15-21-12/metadata/bd6/table1.json

[shattl2000]

No, I did't run chown -R before executing attached build.

cat /var/lib/clickhouse/backup/clickhouse_backup_2022-02-10T15-21-12/metadata/bd6/table1.json

{
        "table": "table1",
        "database": "bd6",
        "parts": {},
        "query": "ATTACH MATERIALIZED VIEW bd6.table1 UUID '27493bcc-f130-44c8-8e01-cdb87995321b' TO bd6.table2 (periodString,operator_idUInt64,competition_idString,gambling_type_idUInt32,bets_amountDecimal(16, 2),bets_countUInt64,is_deletedUInt8,updated_atDateTime64(3, 'UTC'),ver Int64) AS SELECT DISTINCT b.period AS period, b.operator_id AS operator_id, b.competition_id AS competition_id, b.gambling_type_id AS gambling_type_id, ifNull(b.bets_amount, toDecimal64(0, 2)) AS bets_amount, ifNull(b.bets_count, 0) AS bets_count, b.is_deleted AS is_deleted, b.updated_at AS updated_at, toUnixTimestamp64Milli(b.created_at) AS ver FROM bd6.table5 AS b WHERE (b.competition_id != '') AND (b.event_id = '')",
        "size": null,
        "metadata_only": false
}

I just copied command from query and executing it in clickhouse, table was created.

[Slach]

@shattl2000 ok.
could you share clickhouse-backup print-config
without sensitive credentials?

[shattl2000]

Sorry for log reply

clickhouse-backup print-config
general:
  remote_storage: s3
  max_file_size: 1073741824
  disable_progress_bar: false
  backups_to_keep_local: 3
  backups_to_keep_remote: 15
  log_level: debug
  allow_empty_backups: false
  download_concurrency: 2
  upload_concurrency: 2
  restore_schema_on_cluster: test
  upload_by_part: true
  download_by_part: true
clickhouse:
  username: default
  password: ""
  host: localhost
  port: 9000
  disk_mapping: {}
  skip_tables:
  - system.*
  - INFORMATION_SCHEMA.*
  - information_schema.*
  timeout: 5m
  freeze_by_part: false
  secure: false
  skip_verify: false
  sync_replicated_tables: false
  log_sql_queries: false
  config_dir: /etc/clickhouse-server/
  restart_command: systemctl restart clickhouse-server
  ignore_not_exists_error_during_freeze: true
  debug: false
s3:
  access_key: 
  secret_key: 
  bucket: test
  endpoint: url-s3
  region: region
  acl: private
  assume_role_arn: ""
  force_path_style: false
  path: 
  disable_ssl: false
  compression_level: 1
  compression_format: gzip
  sse: ""
  disable_cert_verification: false
  storage_class: STANDARD
  concurrency: 1
  part_size: 104857600
  debug: false
gcs:
  credentials_file: ""
  credentials_json: ""
  bucket: ""
  path: ""
  compression_level: 1
  compression_format: tar
  debug: false
  endpoint: ""
cos:
  url: ""
  timeout: 2m
  secret_id: ""
  secret_key: ""
  path: ""
  compression_format: tar
  compression_level: 1
  debug: false
api:
  listen: localhost:7171
  enable_metrics: true
  enable_pprof: false
  username: ""
  password: ""
  secure: false
  certificate_file: ""
  private_key_file: ""
  create_integration_tables: false
  allow_parallel: false
ftp:
  address: ""
  timeout: 2m
  username: ""
  password: ""
  tls: false
  path: ""
  compression_format: tar
  compression_level: 1
  concurrency: 2
  debug: false
sftp:
  address: ""
  port: 22
  username: ""
  password: ""
  key: ""
  path: ""
  compression_format: tar
  compression_level: 1
  concurrency: 1
  debug: false
azblob:
  endpoint_suffix: core.windows.net
  account_name: ""
  account_key: ""
  sas: ""
  use_managed_identity: false
  container: ""
  path: ""
  compression_level: 1
  compression_format: tar
  sse_key: ""
  buffer_size: 0
  buffer_count: 3

[Slach]

restore_schema_on_cluster: test

This is the key of last error
could you share the full log of the following command?

LOG_LEVEL=debug clickhouse-backup restore clickhouse_backup_2022-02-10T15-21-12 --schema

need clickhouse sql query which clickhouse-backup try to execute before error

[shattl2000]

I run command
LOG_LEVEL=debug clickhouse-backup restore clickhouse_backup_2022-02-10T15-21-12 --schema

And got a same error
2022/02/14 11:27:42.242435 debug CREATE DATABASE IF NOT EXISTS bd6
2022/02/14 11:27:42.243775 debug ATTACH MATERIALIZED VIEW bd6.table1 UUID '27493bcc-f130-44c8-8e01-cdb87995321b' TO bd6.table2 (`period` String, `operator_id` UInt64, `competition_id` String, `gambling_type_id` UInt32, `bets_amount` Decimal(16, 2), `bets_count` UInt64, `is_deleted` UInt8, `updated_at` DateTime64(3, 'UTC'), `ver` Int64) AS SELECT DISTINCT b.period AS period, b.operator_id AS operator_id, b.competition_id AS competition_id, b.gambling_type_id AS gambling_type_id, ifNull(b.bets_amount, toDecimal64(0, 2)) AS bets_amount, ifNull(b.bets_count, 0) AS bets_count, b.is_deleted AS is_deleted, b.updated_at AS updated_at, toUnixTimestamp64Milli(b.created_at) AS ver FROM bd6.table5 AS b WHERE ON CLUSTER 'test' (b.competition_id != '') AND (b.event_id = '')
2022/02/14 11:27:42.245461 warn can't create table 'bd6.table1': code: 62, message: Syntax error: failed at position 710 ('CLUSTER'): CLUSTER 'test' (b.competition_id != '') AND (b.event_id = ''). Expected one of: UNION, LIMIT, WINDOW, LIKE, GLOBAL NOT IN, end of query, HAVING, AS, DIV, IS, GROUP BY, INTO OUTFILE, OR, QuestionMark, BETWEEN, OFFSET, NOT LIKE, MOD, AND, Comma, alias, ORDER BY, SETTINGS, IN, ILIKE, FORMAT, Dot, NOT ILIKE, WITH, NOT, Arrow, token, NOT IN, GLOBAL IN, will try again backup=clickhouse_backup_2022-02-10T15-21-12 operation=restore

[Slach]

Are you sure file which you share here #388 (comment) is correct?

I don't see spaces between field names and field types in shared cat *.json result

Did you try manual change metadata/bd5/table1.json file on backup folder?

I look into codebase, and provided query should convert to ON CLUSTER clause correctly

[shattl2000]

It's must be correct. I didn't manual change file.

cat /var/lib/clickhouse/backup/clickhouse_backup_2022-02-10T15-21-12/metadata/bd6/table1.json
{
	"table": "table1",
	"database": "bd6",
	"parts": {},
	"query": "ATTACH MATERIALIZED VIEW bd6.table1 UUID '27493bcc-f130-44c8-8e01-cdb87995321b' TO bd6.table2 (`period` String, `operator_id` UInt64, `competition_id` String, `gambling_type_id` UInt32, `bets_amount` Decimal(16, 2), `bets_count` UInt64, `is_deleted` UInt8, `updated_at` DateTime64(3, 'UTC'), `ver` Int64) AS SELECT DISTINCT b.period AS period, b.operator_id AS operator_id, b.competition_id AS competition_id, b.gambling_type_id AS gambling_type_id, ifNull(b.bets_amount, toDecimal64(0, 2)) AS bets_amount, ifNull(b.bets_count, 0) AS bets_count, b.is_deleted AS is_deleted, b.updated_at AS updated_at, toUnixTimestamp64Milli(b.created_at) AS ver FROM bd6.table5 AS b WHERE (b.competition_id != '') AND (b.event_id = '')",
	"size": null,
	"metadata_only": false
}

[Slach]

@shattl2000 could you run the following debug build?
Please unpack it on /tmp/ folder
clickhouse-backup.zip

and run
LOG_LEVEL=debug /tmp/clickhouse-backup restore clickhouse_backup_2022-02-10T15-21-12 --schema

[shattl2000]

The same error

2022/02/15 19:18:21.450556 debug CREATE DATABASE IF NOT EXISTS bd6
2022/02/15 19:18:21.451940 debug ATTACH MATERIALIZED VIEW bd6.table1 UUID '27493bcc-f130-44c8-8e01-cdb87995321b' TO bd6.table2 (`period` String, `operator_id` UInt64, `competition_id` String, `gambling_type_id` UInt32, `bets_amount` Decimal(16, 2), `bets_count` UInt64, `is_deleted` UInt8, `updated_at` DateTime64(3, 'UTC'), `ver` Int64) AS SELECT DISTINCT b.period AS period, b.operator_id AS operator_id, b.competition_id AS competition_id, b.gambling_type_id AS gambling_type_id, ifNull(b.bets_amount, toDecimal64(0, 2)) AS bets_amount, ifNull(b.bets_count, 0) AS bets_count, b.is_deleted AS is_deleted, b.updated_at AS updated_at, toUnixTimestamp64Milli(b.created_at) AS ver FROM bd6.table5 AS b WHERE ON CLUSTER 'test' (b.competition_id != '') AND (b.event_id = '')
2022/02/15 19:18:21.453595 warn can't create table 'bd6.table1': code: 62, message: Syntax error: failed at position 710 ('CLUSTER'): CLUSTER 'test' (b.competition_id != '') AND (b.event_id = ''). Expected one of: UNION, LIMIT, WINDOW, LIKE, GLOBAL NOT IN, end of query, HAVING, AS, DIV, IS, GROUP BY, INTO OUTFILE, OR, QuestionMark, BETWEEN, OFFSET, NOT LIKE, MOD, AND, Comma, alias, ORDER BY, SETTINGS, IN, ILIKE, FORMAT, Dot, NOT ILIKE, WITH, NOT, Arrow, token, NOT IN, GLOBAL IN, will try again backup=clickhouse_backup_2022-02-10T15-21-12 operation=restore

[Slach]

clickhouse-backup.zip
reproduced, thanks a lot for reporting
check latest debug build, should work as expected

[shattl2000]

Thanks, it's look like all right. Schema restored without error. Could you tell me, how right restore data?
The first time I run command
clickhouse-backup restore clickhouse_backup_2022-02-10T15-21-12 --schema
And after execute, I run tis command
clickhouse-backup restore clickhouse_backup_2022-02-10T15-21-12 --data
Or can I somehow restore both the schema and the data in it with one command?

[Slach]

These two commands are enough
Moreover, you can just skip --schema and --data and use one command clickhouse-backup restore --rm <backup_name> then clickhouse-backup will drop exists table before restore

[shattl2000]

Thanks a lot! I think this issue can be close.