restore_remote not working properly after doing create_remote

[arikodelabs]

I get one of the following error when i do clickhouse-backup restore_remote shardreplikimi07 2022/06/24 14:35:19.664141 error can't create table bms_qa_qaclient.connector_time_series_last_values_view: code: 70, message: Conversion from AggregateFunction(argMax, Nullable(String), DateTime) to AggregateFunction(argMax, String, DateTime) is not supported: while converting source column s to destination column s after 5986 times, please check your schema dependencies

And the tables are not filled with data

We have around 500gb database hence why we have it difficult to replicate the exact time when this issue happens. Please let me know what logs you need and we will provide.

Here is the config of /etc/clickhouse-backup/config.yml:

general:
  remote_storage: gcs
  max_file_size: 1099511627776
  disable_progress_bar: false
  backups_to_keep_local: 0
  backups_to_keep_remote: 0
  log_level: info
  allow_empty_backups: false
clickhouse:
  username:
  password:
  host: 172.16.33.41 #localhost
  port: 9000
  disk_mapping: {}
  skip_tables:
    - system.*
  timeout: 30m
  freeze_by_part: false
  secure: false
  skip_verify: false
  sync_replicated_tables: true
gcs:
  credentials_file: "/etc/google/gcs.json"
  credentials_json: ""
  bucket: "clickhouse-1-backup"
  path: "clickhouse-dev-1-backup"
  compression_level: 1
  compression_format: tar

[Slach]

Which clickhouse-backup version do you use to restore, and which clickhouse-server version on source and destination server?

[arikodelabs]

Source Server: Version: 1.4.5 (Clickhouse-backup) Git Commit: 028d6270102188bd311bd587e8220cfaaa0855e0 Build Date: 2022-06-23

clickhouse-server version : 20.5.3.27

Destination Server:

Version: 1.4.5 (Clickhouse-backup) Git Commit: 028d6270102188bd311bd587e8220cfaaa0855e0 Build Date: 2022-06-23

clickhouse-server version : 22.4.4.7

[Slach]

could you share

LOG_LEVEL=debug clickhouse-backup restore_remote <backup_name>

[arikodelabs]

logleveldebug.log Here you go! thankyou

[Slach]

issue is not related to clickhouse-backup itself

looks like clickhouse 22.4 can't accept your table SQL definitions from 20.5, see similar issue https://github.com/ClickHouse/ClickHouse/issues/23019

for example

2022/06/28 09:11:52.709337 info CREATE TABLE bms_qa_roletesting.device_time_series (device_id String, building_id String, t_utc DateTime('UTC'), s Nullable(String), t_local DateTime) ENGINE = MergeTree() PARTITION BY toYYYYMM(t_local) ORDER BY (device_id, t_utc) SETTINGS index_granularity = 8192

CREATE TABLE bms_qa_roletesting.device_time_series_last_values (building_id String, device_id String, s AggregateFunction(argMax, String, DateTime), time AggregateFunction(max, DateTime)) ENGINE = AggregatingMergeTree() ORDER BY device_id SETTINGS index_granularity = 8192;

2022/06/28 09:12:56.873751 info ATTACH MATERIALIZED VIEW bms_qa_roletesting.device_time_series_last_values_view TO bms_qa_roletesting.device_time_series_last_values (building_id String, device_id String, s AggregateFunction(argMax, String, DateTime), time AggregateFunction(max, DateTime)) AS SELECT building_id, device_id, argMaxState(s, t_local) AS s, maxState(t_local) AS time FROM bms_qa_roletesting.device_time_series GROUP BY building_id, device_id

2022/06/28 09:12:56.876063 warn can't create table 'bms_qa_roletesting.device_time_series_last_values_view': code: 70, message: Conversion from AggregateFunction(argMax, Nullable(String), DateTime) to AggregateFunction(argMax, String, DateTime) is not supported: while converting source column s to destination column s, will try again backup=shardreplikimi07 operation=restore

your source tables contains following fields s Nullable(String) but materialized view contains s AggregateFunction(argMax, String, DateTime), which require s as not nullable in clickhouse-server after version 22.4

I would like to change your destination server version to 22.3 LTS, instead of use 22.4