clickhouse-keeper + DROP TABLE ... ON CLUSTER ... SYNC, doesn't clean replica as expected

[aleclerc-sonrai]

I've been trying to test backup/restore on a cluster (2 shards x 3 replicas) = 6 nodes

I created the cluster, then imported the hits/visits database on the entire cluster: https://clickhouse.com/docs/en/getting-started/tutorial/

I created a remote backup in s3, then I then destroyed a node, and brought it back up 'blank' with the same ip and configuration.

I then attempted to restore the database, and got the following:

[root@ip-10-22-180-149 ec2-user]# clickhouse-backup list
2022-03-17T04-00-01   1001.32MiB   17/03/2022 04:00:41   remote      tar
2022-03-18T04-00-01   1001.22MiB   18/03/2022 04:00:44   remote      tar
[root@ip-10-22-180-149 ec2-user]# clickhouse-backup restore --rm 2022-03-18T04-00-01
2022/03/18 18:21:57.818928 error stat /var/lib/clickhouse/backup/2022-03-18T04-00-01/metadata: no such file or directory
[root@ip-10-22-180-149 ec2-user]# clickhouse-backup restore_remote --rm 2022-03-18T04-00-01
2022/03/18 18:22:10.791897  info done                      backup=2022-03-18T04-00-01 duration=47ms operation=download size=3.93KiB table_metadata=tutorial.hits_local
2022/03/18 18:22:10.830709  info done                      backup=2022-03-18T04-00-01 duration=39ms operation=download size=5.24KiB table_metadata=tutorial.visits_local
2022/03/18 18:22:10.877778  info done                      backup=2022-03-18T04-00-01 duration=47ms operation=download size=3.31KiB table_metadata=tutorial.hits_all
2022/03/18 18:22:10.952001  info done                      backup=2022-03-18T04-00-01 duration=74ms operation=download size=4.81KiB table_metadata=tutorial.visits_all
2022/03/18 18:22:44.504355  info done                      diff_parts=0 duration=0s operation=downloadDiffParts
2022/03/18 18:22:44.504430  info done                      backup=2022-03-18T04-00-01 duration=33.552s operation=download_data size=716.03MiB table=tutorial.hits_local
2022/03/18 18:22:57.683173  info done                      diff_parts=0 duration=0s operation=downloadDiffParts
2022/03/18 18:22:57.683236  info done                      backup=2022-03-18T04-00-01 duration=13.179s operation=download_data size=282.05MiB table=tutorial.visits_local
2022/03/18 18:22:57.683352  info done                      diff_parts=0 duration=0s operation=downloadDiffParts
2022/03/18 18:22:57.683388  info done                      backup=2022-03-18T04-00-01 duration=0s operation=download_data size=0B table=tutorial.hits_all
2022/03/18 18:22:57.683491  info done                      diff_parts=0 duration=0s operation=downloadDiffParts
2022/03/18 18:22:57.683526  info done                      backup=2022-03-18T04-00-01 duration=0s operation=download_data size=0B table=tutorial.visits_all
2022/03/18 18:22:57.715127  info done                      backup=2022-03-18T04-00-01 duration=47.103s operation=download size=998.10MiB
2022/03/18 18:22:59.402725  warn can't create table 'tutorial.hits_local': code: 253, message: There was an error on [ip-10-22-167-36.ec2.internal:9440]: Code: 253. DB::Exception: Replica /clickhouse/tables/c73a3570-0a95-4c50-b769-ba46d21df393/1/replicas/ip-10-22-180-149.ec2.internal already exists. (REPLICA_IS_ALREADY_EXIST) (version 22.2.2.1), will try again backup=2022-03-18T04-00-01 operation=restore
2022/03/18 18:22:59.636296  warn can't create table 'tutorial.visits_local': code: 253, message: There was an error on [ip-10-22-171-5.ec2.internal:9440]: Code: 253. DB::Exception: Replica /clickhouse/tables/67d260e4-b9b1-4fc5-b6a9-a0d5ec5845f7/1/replicas/ip-10-22-180-149.ec2.internal already exists. (REPLICA_IS_ALREADY_EXIST) (version 22.2.2.1), will try again backup=2022-03-18T04-00-01 operation=restore
2022/03/18 18:23:00.131722  warn can't create table 'tutorial.hits_local': code: 253, message: There was an error on [ip-10-22-167-36.ec2.internal:9440]: Code: 253. DB::Exception: Replica /clickhouse/tables/c73a3570-0a95-4c50-b769-ba46d21df393/1/replicas/ip-10-22-180-149.ec2.internal already exists. (REPLICA_IS_ALREADY_EXIST) (version 22.2.2.1), will try again backup=2022-03-18T04-00-01 operation=restore
2022/03/18 18:23:00.267935 error can't create table `tutorial`.`visits_local`: code: 57, message: There was an error on [ip-10-22-167-36.ec2.internal:9440]: Code: 57. DB::Exception: Table tutorial.visits_local already exists. (TABLE_ALREADY_EXISTS) (version 22.2.2.1) after 4 times, please check your schema dependencies

So the schema for the local table failed, and the distributed seemed to work:

SHOW DATABASES
┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ default            │
│ information_schema │
│ system             │
│ tutorial           │
└────────────────────┘

SHOW TABLES
┌─name───────┐
│ hits_all   │
│ visits_all │
└────────────┘

This is an issue, however the bigger concern to me is that I went to go check my other nodes, and now the hits_local and visits_local are gone on ALL nodes.

[root@ip-10-22-166-127 ec2-user]# clickhouse-client --port 9440 -d tutorial -q "show tables"
hits_all
visits_all

Once node has the schema, but the rows are all gone:

[root@ip-10-22-167-36 ec2-user]# clickhouse-client --port 9440 -d tutorial -q "show tables"
hits_all
visits_all
visits_local
[root@ip-10-22-167-36 ec2-user]# clickhouse-client --port 9440 -d tutorial -q 'select count(*) from visits_local'
0

[Slach]

which clickhouse-backup version do you use?

could you share
clickhouse-backup print-config without credentials?

[DipalPrajapati]

Atomic databases keep dropped tables for 8 minutes.
did you wait for some time between delete and restoring?

[aleclerc-sonrai]

Backup Version and print config (buckets redacted)

Version:	 1.3.1
Git Commit:	 0b1a97a6a2610fa59b58d86c139b67b0ce62e420
Build Date:	 2022-02-17
[root@ip-10-22-167-36 ec2-user]# clickhouse-backup print-config
general:
  remote_storage: s3
  max_file_size: 1073741824
  disable_progress_bar: true
  backups_to_keep_local: 0
  backups_to_keep_remote: 30
  log_level: info
  allow_empty_backups: false
  download_concurrency: 1
  upload_concurrency: 1
  restore_schema_on_cluster: prod_cluster
  upload_by_part: true
  download_by_part: true
clickhouse:
  username: <<REDACTED>>
  password: <<REDACTED>>
  host: localhost
  port: 9440
  disk_mapping: {}
  skip_tables:
  - system.*
  - INFORMATION_SCHEMA.*
  - information_schema.*
  timeout: 5m
  freeze_by_part: false
  secure: true
  skip_verify: true
  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: <<<REDACTED>>>
  endpoint: ""
  region: us-east-1
  acl: private
  assume_role_arn: ""
  force_path_style: false
  path: clickhouse/stage-ch-bu-testrun/ip-10-22-167-36.ec2.internal
  disable_ssl: false
  compression_level: 1
  compression_format: tar
  sse: ""
  disable_cert_verification: false
  storage_class: STANDARD
  concurrency: 1
  part_size: 0
  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: 0.0.0.0:7171
  enable_metrics: true
  enable_pprof: false
  username: ""
  password: ""
  secure: false
  certificate_file: ""
  private_key_file: ""
  create_integration_tables: true
  allow_parallel: false
ftp:
  address: ""
  timeout: 2m
  username: ""
  password: ""
  tls: false
  path: ""
  compression_format: tar
  compression_level: 1
  concurrency: 1
  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

For info about how my cluster is setup:

[root@ip-10-22-167-36 ec2-user]# cat /etc/clickhouse-server/config.d/remote_servers.yaml
remote_servers:
  prod_cluster:
    secret: <<REDACTED>>
    shard:
    - internal_replication: true
      replica:
      - host: "ip-10-22-167-36.ec2.internal"
        port: 9440
        secure: 1
      - host: "ip-10-22-171-5.ec2.internal"
        port: 9440
        secure: 1
      - host: "ip-10-22-176-6.ec2.internal"
        port: 9440
        secure: 1
    - internal_replication: true
      replica:
      - host: "ip-10-22-166-127.ec2.internal"
        port: 9440
        secure: 1
      - host: "ip-10-22-174-114.ec2.internal"
        port: 9440
        secure: 1
      - host: "ip-10-22-180-149.ec2.internal"
        port: 9440
        secure: 1

[root@ip-10-22-167-36 ec2-user]# cat /etc/clickhouse-server/config.d/zookeepers.yaml
listen_host: "::"
zookeeper:
  node:
  - host: "ip-10-22-167-36.ec2.internal"
    port: 10181
    secure: 1
  - host: "ip-10-22-171-5.ec2.internal"
    port: 10181
    secure: 1
  - host: "ip-10-22-176-6.ec2.internal"
    port: 10181
    secure: 1

[root@ip-10-22-167-36 ec2-user]# cat /etc/clickhouse-server/config.d/keepers.yaml
keeper_server:
  coordination_settings:
    operation_timeout_ms: "10000"
    raft_logs_level: "information"
    session_timeout_ms: "30000"
  log_storage_path: "/var/log/clickhouse-server/coordination/"
  raft_configuration:
    secure: true
    server:
    - hostname: "ip-10-22-167-36.ec2.internal"
      id: 1
      port: 9444
    - hostname: "ip-10-22-171-5.ec2.internal"
      id: 2
      port: 9444
    - hostname: "ip-10-22-176-6.ec2.internal"
      id: 3
      port: 9444
  server_id: 1
  snapshot_storage_path: "/var/lib/clickhouse/coordination/snapshots"
  tcp_port_secure: 10181

Only major difference is that we are using clickhouse-keeper, instead of regular zookeeper.

[Slach]

did you run clickhouse-keeper as standalone process or as embedded inside clickhouse-server?

[Slach]

Atomic databases keep dropped tables for 8 minutes.
did you wait for some time between delete and restoring?

We use DROP TABLE ... SYNC for atomic databases, to avoid this type of waiting.

[aleclerc-sonrai]

It is embedded currently. Also, the node I "took down" was not one of the 3 with zookeeper (only my first 3 nodes have clickhouse-keeper enabled. Again, not sure if that matters here, but wanted to make sure it was noted

[aleclerc-sonrai]

When i run clickhouse-backup restore_remote --rm <name> does the --rm portion do a drop on the distributed tables as well, or just the local ones? If it doesn't on the distributed, then it might make sense that it would send a 'request' to delete all rows to the shards, which would them get propegated over all all nodes?

[Slach]

We don't use DROP TABLE ... ON CLUSTER, tables always dropped locally (restore_on_cluster: "" by default)

We didn't test clickhouse-keeper together with clickhouse-backup

REPLICA_IS_ALREADY_EXIST
means data not cleaned from clickhouse-keeper after execute DROP TABLE ... SYNC

so, you should use https://github.com/outbrain/zookeepercli and clean /clickhouse/tables/c73a3570-0a95-4c50-b769-ba46d21df393/1/replicas/ip-10-22-180-149.ec2.internal

after it run

LOG_LEVEL=debug clickhouse-backup restore--rm <name>

and share results

[aleclerc-sonrai]

2022-03-23T12-00-43   998.41MiB    23/03/2022 12:00:43   local
2022-03-17T04-00-01   1001.32MiB   17/03/2022 04:00:41   remote      tar
2022-03-18T04-00-01   1001.22MiB   18/03/2022 04:00:44   remote      tar
2022-03-23T12-00-43   1001.52MiB   23/03/2022 12:01:21   remote      tar
[root@ip-10-22-180-149 ec2-user]# LOG_LEVEL=debug clickhouse-backup restore --rm 2022-03-23T12-00-43
2022/03/23 12:53:45.381497 debug SELECT value FROM `system`.`build_options` where name='VERSION_INTEGER'
2022/03/23 12:53:45.392103 debug SELECT * FROM system.disks;
2022/03/23 12:53:45.404684 debug CREATE DATABASE IF NOT EXISTS default
ENGINE = Atomic
2022/03/23 12:53:45.417256 debug CREATE DATABASE IF NOT EXISTS tutorial
ENGINE = Atomic
2022/03/23 12:53:45.426385 debug SELECT * FROM system.disks;
2022/03/23 12:53:45.437833 debug SELECT engine FROM system.databases WHERE name = 'tutorial'
2022/03/23 12:53:45.448003 debug DROP TABLE IF EXISTS `tutorial`.`hits_local` ON CLUSTER 'prod_cluster'  NO DELAY
2022/03/23 12:53:46.050378 debug SELECT engine FROM system.databases WHERE name = 'tutorial'
2022/03/23 12:53:46.059718 debug DROP TABLE IF EXISTS `tutorial`.`visits_local` ON CLUSTER 'prod_cluster'  NO DELAY
2022/03/23 12:53:46.561291 debug SELECT engine FROM system.databases WHERE name = 'tutorial'
2022/03/23 12:53:46.574292 debug DROP TABLE IF EXISTS `tutorial`.`hits_all` ON CLUSTER 'prod_cluster'  NO DELAY
2022/03/23 12:53:46.718770 debug SELECT engine FROM system.databases WHERE name = 'tutorial'
2022/03/23 12:53:46.730155 debug DROP TABLE IF EXISTS `tutorial`.`visits_all` ON CLUSTER 'prod_cluster'  NO DELAY
2022/03/23 12:53:46.874303 debug CREATE DATABASE IF NOT EXISTS `tutorial`
2022/03/23 12:53:46.885902 debug CREATE TABLE tutorial.hits_local UUID '0ab5ed25-2c59-4158-a316-c4b93c1c40d1'  ON CLUSTER 'prod_cluster' (`WatchID` UInt64, `JavaEnable` UInt8, `Title` String, `GoodEvent` Int16, `EventTime` DateTime, `EventDate` Date, `CounterID` UInt32, `ClientIP` UInt32, `ClientIP6` FixedString(16), `RegionID` UInt32, `UserID` UInt64, `CounterClass` Int8, `OS` UInt8, `UserAgent` UInt8, `URL` String, `Referer` String, `URLDomain` String, `RefererDomain` String, `Refresh` UInt8, `IsRobot` UInt8, `RefererCategories` Array(UInt16), `URLCategories` Array(UInt16), `URLRegions` Array(UInt32), `RefererRegions` Array(UInt32), `ResolutionWidth` UInt16, `ResolutionHeight` UInt16, `ResolutionDepth` UInt8, `FlashMajor` UInt8, `FlashMinor` UInt8, `FlashMinor2` String, `NetMajor` UInt8, `NetMinor` UInt8, `UserAgentMajor` UInt16, `UserAgentMinor` FixedString(2), `CookieEnable` UInt8, `JavascriptEnable` UInt8, `IsMobile` UInt8, `MobilePhone` UInt8, `MobilePhoneModel` String, `Params` String, `IPNetworkID` UInt32, `TraficSourceID` Int8, `SearchEngineID` UInt16, `SearchPhrase` String, `AdvEngineID` UInt8, `IsArtifical` UInt8, `WindowClientWidth` UInt16, `WindowClientHeight` UInt16, `ClientTimeZone` Int16, `ClientEventTime` DateTime, `SilverlightVersion1` UInt8, `SilverlightVersion2` UInt8, `SilverlightVersion3` UInt32, `SilverlightVersion4` UInt16, `PageCharset` String, `CodeVersion` UInt32, `IsLink` UInt8, `IsDownload` UInt8, `IsNotBounce` UInt8, `FUniqID` UInt64, `HID` UInt32, `IsOldCounter` UInt8, `IsEvent` UInt8, `IsParameter` UInt8, `DontCountHits` UInt8, `WithHash` UInt8, `HitColor` FixedString(1), `UTCEventTime` DateTime, `Age` UInt8, `Sex` UInt8, `Income` UInt8, `Interests` UInt16, `Robotness` UInt8, `GeneralInterests` Array(UInt16), `RemoteIP` UInt32, `RemoteIP6` FixedString(16), `WindowName` Int32, `OpenerName` Int32, `HistoryLength` Int16, `BrowserLanguage` FixedString(2), `BrowserCountry` FixedString(2), `SocialNetwork` String, `SocialAction` String, `HTTPError` UInt16, `SendTiming` Int32, `DNSTiming` Int32, `ConnectTiming` Int32, `ResponseStartTiming` Int32, `ResponseEndTiming` Int32, `FetchTiming` Int32, `RedirectTiming` Int32, `DOMInteractiveTiming` Int32, `DOMContentLoadedTiming` Int32, `DOMCompleteTiming` Int32, `LoadEventStartTiming` Int32, `LoadEventEndTiming` Int32, `NSToDOMContentLoadedTiming` Int32, `FirstPaintTiming` Int32, `RedirectCount` Int8, `SocialSourceNetworkID` UInt8, `SocialSourcePage` String, `ParamPrice` Int64, `ParamOrderID` String, `ParamCurrency` FixedString(3), `ParamCurrencyID` UInt16, `GoalsReached` Array(UInt32), `OpenstatServiceName` String, `OpenstatCampaignID` String, `OpenstatAdID` String, `OpenstatSourceID` String, `UTMSource` String, `UTMMedium` String, `UTMCampaign` String, `UTMContent` String, `UTMTerm` String, `FromTag` String, `HasGCLID` UInt8, `RefererHash` UInt64, `URLHash` UInt64, `CLID` UInt32, `YCLID` UInt64, `ShareService` String, `ShareURL` String, `ShareTitle` String, `ParsedParams.Key1` Array(String), `ParsedParams.Key2` Array(String), `ParsedParams.Key3` Array(String), `ParsedParams.Key4` Array(String), `ParsedParams.Key5` Array(String), `ParsedParams.ValueDouble` Array(Float64), `IslandID` FixedString(16), `RequestNum` UInt32, `RequestTry` UInt8) ENGINE = ReplicatedMergeTree('/clickhouse/tables/0ab5ed25-2c59-4158-a316-c4b93c1c40d1/{shard}', 'ip-10-22-180-149.ec2.internal') PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity = 8192
2022/03/23 12:53:47.185976  warn can't create table 'tutorial.hits_local': code: 253, message: There was an error on [ip-10-22-174-114.ec2.internal:9440]: Code: 253. DB::Exception: Replica /clickhouse/tables/0ab5ed25-2c59-4158-a316-c4b93c1c40d1/2/replicas/ip-10-22-180-149.ec2.internal already exists. (REPLICA_IS_ALREADY_EXIST) (version 22.2.2.1), will try again backup=2022-03-23T12-00-43 operation=restore
2022/03/23 12:53:47.186025 debug CREATE DATABASE IF NOT EXISTS `tutorial`
2022/03/23 12:53:47.197557 debug CREATE TABLE tutorial.visits_local UUID 'b227d3b8-7252-48c9-b9f8-eb011af78809'  ON CLUSTER 'prod_cluster' (`CounterID` UInt32, `StartDate` Date, `Sign` Int8, `IsNew` UInt8, `VisitID` UInt64, `UserID` UInt64, `StartTime` DateTime, `Duration` UInt32, `UTCStartTime` DateTime, `PageViews` Int32, `Hits` Int32, `IsBounce` UInt8, `Referer` String, `StartURL` String, `RefererDomain` String, `StartURLDomain` String, `EndURL` String, `LinkURL` String, `IsDownload` UInt8, `TraficSourceID` Int8, `SearchEngineID` UInt16, `SearchPhrase` String, `AdvEngineID` UInt8, `PlaceID` Int32, `RefererCategories` Array(UInt16), `URLCategories` Array(UInt16), `URLRegions` Array(UInt32), `RefererRegions` Array(UInt32), `IsYandex` UInt8, `GoalReachesDepth` Int32, `GoalReachesURL` Int32, `GoalReachesAny` Int32, `SocialSourceNetworkID` UInt8, `SocialSourcePage` String, `MobilePhoneModel` String, `ClientEventTime` DateTime, `RegionID` UInt32, `ClientIP` UInt32, `ClientIP6` FixedString(16), `RemoteIP` UInt32, `RemoteIP6` FixedString(16), `IPNetworkID` UInt32, `SilverlightVersion3` UInt32, `CodeVersion` UInt32, `ResolutionWidth` UInt16, `ResolutionHeight` UInt16, `UserAgentMajor` UInt16, `UserAgentMinor` UInt16, `WindowClientWidth` UInt16, `WindowClientHeight` UInt16, `SilverlightVersion2` UInt8, `SilverlightVersion4` UInt16, `FlashVersion3` UInt16, `FlashVersion4` UInt16, `ClientTimeZone` Int16, `OS` UInt8, `UserAgent` UInt8, `ResolutionDepth` UInt8, `FlashMajor` UInt8, `FlashMinor` UInt8, `NetMajor` UInt8, `NetMinor` UInt8, `MobilePhone` UInt8, `SilverlightVersion1` UInt8, `Age` UInt8, `Sex` UInt8, `Income` UInt8, `JavaEnable` UInt8, `CookieEnable` UInt8, `JavascriptEnable` UInt8, `IsMobile` UInt8, `BrowserLanguage` UInt16, `BrowserCountry` UInt16, `Interests` UInt16, `Robotness` UInt8, `GeneralInterests` Array(UInt16), `Params` Array(String), `Goals.ID` Array(UInt32), `Goals.Serial` Array(UInt32), `Goals.EventTime` Array(DateTime), `Goals.Price` Array(Int64), `Goals.OrderID` Array(String), `Goals.CurrencyID` Array(UInt32), `WatchIDs` Array(UInt64), `ParamSumPrice` Int64, `ParamCurrency` FixedString(3), `ParamCurrencyID` UInt16, `ClickLogID` UInt64, `ClickEventID` Int32, `ClickGoodEvent` Int32, `ClickEventTime` DateTime, `ClickPriorityID` Int32, `ClickPhraseID` Int32, `ClickPageID` Int32, `ClickPlaceID` Int32, `ClickTypeID` Int32, `ClickResourceID` Int32, `ClickCost` UInt32, `ClickClientIP` UInt32, `ClickDomainID` UInt32, `ClickURL` String, `ClickAttempt` UInt8, `ClickOrderID` UInt32, `ClickBannerID` UInt32, `ClickMarketCategoryID` UInt32, `ClickMarketPP` UInt32, `ClickMarketCategoryName` String, `ClickMarketPPName` String, `ClickAWAPSCampaignName` String, `ClickPageName` String, `ClickTargetType` UInt16, `ClickTargetPhraseID` UInt64, `ClickContextType` UInt8, `ClickSelectType` Int8, `ClickOptions` String, `ClickGroupBannerID` Int32, `OpenstatServiceName` String, `OpenstatCampaignID` String, `OpenstatAdID` String, `OpenstatSourceID` String, `UTMSource` String, `UTMMedium` String, `UTMCampaign` String, `UTMContent` String, `UTMTerm` String, `FromTag` String, `HasGCLID` UInt8, `FirstVisit` DateTime, `PredLastVisit` Date, `LastVisit` Date, `TotalVisits` UInt32, `TraficSource.ID` Array(Int8), `TraficSource.SearchEngineID` Array(UInt16), `TraficSource.AdvEngineID` Array(UInt8), `TraficSource.PlaceID` Array(UInt16), `TraficSource.SocialSourceNetworkID` Array(UInt8), `TraficSource.Domain` Array(String), `TraficSource.SearchPhrase` Array(String), `TraficSource.SocialSourcePage` Array(String), `Attendance` FixedString(16), `CLID` UInt32, `YCLID` UInt64, `NormalizedRefererHash` UInt64, `SearchPhraseHash` UInt64, `RefererDomainHash` UInt64, `NormalizedStartURLHash` UInt64, `StartURLDomainHash` UInt64, `NormalizedEndURLHash` UInt64, `TopLevelDomain` UInt64, `URLScheme` UInt64, `OpenstatServiceNameHash` UInt64, `OpenstatCampaignIDHash` UInt64, `OpenstatAdIDHash` UInt64, `OpenstatSourceIDHash` UInt64, `UTMSourceHash` UInt64, `UTMMediumHash` UInt64, `UTMCampaignHash` UInt64, `UTMContentHash` UInt64, `UTMTermHash` UInt64, `FromHash` UInt64, `WebVisorEnabled` UInt8, `WebVisorActivity` UInt32, `ParsedParams.Key1` Array(String), `ParsedParams.Key2` Array(String), `ParsedParams.Key3` Array(String), `ParsedParams.Key4` Array(String), `ParsedParams.Key5` Array(String), `ParsedParams.ValueDouble` Array(Float64), `Market.Type` Array(UInt8), `Market.GoalID` Array(UInt32), `Market.OrderID` Array(String), `Market.OrderPrice` Array(Int64), `Market.PP` Array(UInt32), `Market.DirectPlaceID` Array(UInt32), `Market.DirectOrderID` Array(UInt32), `Market.DirectBannerID` Array(UInt32), `Market.GoodID` Array(String), `Market.GoodName` Array(String), `Market.GoodQuantity` Array(Int32), `Market.GoodPrice` Array(Int64), `IslandID` FixedString(16)) ENGINE = ReplicatedMergeTree('/clickhouse/tables/b227d3b8-7252-48c9-b9f8-eb011af78809/{shard}', 'ip-10-22-180-149.ec2.internal') PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID) SAMPLE BY intHash32(UserID) SETTINGS index_granularity = 8192
2022/03/23 12:53:47.500833  warn can't create table 'tutorial.visits_local': code: 253, message: There was an error on [ip-10-22-166-127.ec2.internal:9440]: Code: 253. DB::Exception: Replica /clickhouse/tables/b227d3b8-7252-48c9-b9f8-eb011af78809/2/replicas/ip-10-22-180-149.ec2.internal already exists. (REPLICA_IS_ALREADY_EXIST) (version 22.2.2.1), will try again backup=2022-03-23T12-00-43 operation=restore
2022/03/23 12:53:47.501306 debug CREATE DATABASE IF NOT EXISTS `tutorial`
2022/03/23 12:53:47.512395 debug CREATE TABLE tutorial.hits_all UUID '8af984c2-d842-42a9-9d80-038f184ea718'  ON CLUSTER 'prod_cluster' (`WatchID` UInt64, `JavaEnable` UInt8, `Title` String, `GoodEvent` Int16, `EventTime` DateTime, `EventDate` Date, `CounterID` UInt32, `ClientIP` UInt32, `ClientIP6` FixedString(16), `RegionID` UInt32, `UserID` UInt64, `CounterClass` Int8, `OS` UInt8, `UserAgent` UInt8, `URL` String, `Referer` String, `URLDomain` String, `RefererDomain` String, `Refresh` UInt8, `IsRobot` UInt8, `RefererCategories` Array(UInt16), `URLCategories` Array(UInt16), `URLRegions` Array(UInt32), `RefererRegions` Array(UInt32), `ResolutionWidth` UInt16, `ResolutionHeight` UInt16, `ResolutionDepth` UInt8, `FlashMajor` UInt8, `FlashMinor` UInt8, `FlashMinor2` String, `NetMajor` UInt8, `NetMinor` UInt8, `UserAgentMajor` UInt16, `UserAgentMinor` FixedString(2), `CookieEnable` UInt8, `JavascriptEnable` UInt8, `IsMobile` UInt8, `MobilePhone` UInt8, `MobilePhoneModel` String, `Params` String, `IPNetworkID` UInt32, `TraficSourceID` Int8, `SearchEngineID` UInt16, `SearchPhrase` String, `AdvEngineID` UInt8, `IsArtifical` UInt8, `WindowClientWidth` UInt16, `WindowClientHeight` UInt16, `ClientTimeZone` Int16, `ClientEventTime` DateTime, `SilverlightVersion1` UInt8, `SilverlightVersion2` UInt8, `SilverlightVersion3` UInt32, `SilverlightVersion4` UInt16, `PageCharset` String, `CodeVersion` UInt32, `IsLink` UInt8, `IsDownload` UInt8, `IsNotBounce` UInt8, `FUniqID` UInt64, `HID` UInt32, `IsOldCounter` UInt8, `IsEvent` UInt8, `IsParameter` UInt8, `DontCountHits` UInt8, `WithHash` UInt8, `HitColor` FixedString(1), `UTCEventTime` DateTime, `Age` UInt8, `Sex` UInt8, `Income` UInt8, `Interests` UInt16, `Robotness` UInt8, `GeneralInterests` Array(UInt16), `RemoteIP` UInt32, `RemoteIP6` FixedString(16), `WindowName` Int32, `OpenerName` Int32, `HistoryLength` Int16, `BrowserLanguage` FixedString(2), `BrowserCountry` FixedString(2), `SocialNetwork` String, `SocialAction` String, `HTTPError` UInt16, `SendTiming` Int32, `DNSTiming` Int32, `ConnectTiming` Int32, `ResponseStartTiming` Int32, `ResponseEndTiming` Int32, `FetchTiming` Int32, `RedirectTiming` Int32, `DOMInteractiveTiming` Int32, `DOMContentLoadedTiming` Int32, `DOMCompleteTiming` Int32, `LoadEventStartTiming` Int32, `LoadEventEndTiming` Int32, `NSToDOMContentLoadedTiming` Int32, `FirstPaintTiming` Int32, `RedirectCount` Int8, `SocialSourceNetworkID` UInt8, `SocialSourcePage` String, `ParamPrice` Int64, `ParamOrderID` String, `ParamCurrency` FixedString(3), `ParamCurrencyID` UInt16, `GoalsReached` Array(UInt32), `OpenstatServiceName` String, `OpenstatCampaignID` String, `OpenstatAdID` String, `OpenstatSourceID` String, `UTMSource` String, `UTMMedium` String, `UTMCampaign` String, `UTMContent` String, `UTMTerm` String, `FromTag` String, `HasGCLID` UInt8, `RefererHash` UInt64, `URLHash` UInt64, `CLID` UInt32, `YCLID` UInt64, `ShareService` String, `ShareURL` String, `ShareTitle` String, `ParsedParams.Key1` Array(String), `ParsedParams.Key2` Array(String), `ParsedParams.Key3` Array(String), `ParsedParams.Key4` Array(String), `ParsedParams.Key5` Array(String), `ParsedParams.ValueDouble` Array(Float64), `IslandID` FixedString(16), `RequestNum` UInt32, `RequestTry` UInt8) ENGINE = Distributed('prod_cluster', 'tutorial', 'hits_local', rand())
2022/03/23 12:53:47.657090 debug CREATE DATABASE IF NOT EXISTS `tutorial`
2022/03/23 12:53:47.668610 debug CREATE TABLE tutorial.visits_all UUID '592f0e38-d7ae-46ff-81e9-8fa444b8cb73'  ON CLUSTER 'prod_cluster' (`CounterID` UInt32, `StartDate` Date, `Sign` Int8, `IsNew` UInt8, `VisitID` UInt64, `UserID` UInt64, `StartTime` DateTime, `Duration` UInt32, `UTCStartTime` DateTime, `PageViews` Int32, `Hits` Int32, `IsBounce` UInt8, `Referer` String, `StartURL` String, `RefererDomain` String, `StartURLDomain` String, `EndURL` String, `LinkURL` String, `IsDownload` UInt8, `TraficSourceID` Int8, `SearchEngineID` UInt16, `SearchPhrase` String, `AdvEngineID` UInt8, `PlaceID` Int32, `RefererCategories` Array(UInt16), `URLCategories` Array(UInt16), `URLRegions` Array(UInt32), `RefererRegions` Array(UInt32), `IsYandex` UInt8, `GoalReachesDepth` Int32, `GoalReachesURL` Int32, `GoalReachesAny` Int32, `SocialSourceNetworkID` UInt8, `SocialSourcePage` String, `MobilePhoneModel` String, `ClientEventTime` DateTime, `RegionID` UInt32, `ClientIP` UInt32, `ClientIP6` FixedString(16), `RemoteIP` UInt32, `RemoteIP6` FixedString(16), `IPNetworkID` UInt32, `SilverlightVersion3` UInt32, `CodeVersion` UInt32, `ResolutionWidth` UInt16, `ResolutionHeight` UInt16, `UserAgentMajor` UInt16, `UserAgentMinor` UInt16, `WindowClientWidth` UInt16, `WindowClientHeight` UInt16, `SilverlightVersion2` UInt8, `SilverlightVersion4` UInt16, `FlashVersion3` UInt16, `FlashVersion4` UInt16, `ClientTimeZone` Int16, `OS` UInt8, `UserAgent` UInt8, `ResolutionDepth` UInt8, `FlashMajor` UInt8, `FlashMinor` UInt8, `NetMajor` UInt8, `NetMinor` UInt8, `MobilePhone` UInt8, `SilverlightVersion1` UInt8, `Age` UInt8, `Sex` UInt8, `Income` UInt8, `JavaEnable` UInt8, `CookieEnable` UInt8, `JavascriptEnable` UInt8, `IsMobile` UInt8, `BrowserLanguage` UInt16, `BrowserCountry` UInt16, `Interests` UInt16, `Robotness` UInt8, `GeneralInterests` Array(UInt16), `Params` Array(String), `Goals.ID` Array(UInt32), `Goals.Serial` Array(UInt32), `Goals.EventTime` Array(DateTime), `Goals.Price` Array(Int64), `Goals.OrderID` Array(String), `Goals.CurrencyID` Array(UInt32), `WatchIDs` Array(UInt64), `ParamSumPrice` Int64, `ParamCurrency` FixedString(3), `ParamCurrencyID` UInt16, `ClickLogID` UInt64, `ClickEventID` Int32, `ClickGoodEvent` Int32, `ClickEventTime` DateTime, `ClickPriorityID` Int32, `ClickPhraseID` Int32, `ClickPageID` Int32, `ClickPlaceID` Int32, `ClickTypeID` Int32, `ClickResourceID` Int32, `ClickCost` UInt32, `ClickClientIP` UInt32, `ClickDomainID` UInt32, `ClickURL` String, `ClickAttempt` UInt8, `ClickOrderID` UInt32, `ClickBannerID` UInt32, `ClickMarketCategoryID` UInt32, `ClickMarketPP` UInt32, `ClickMarketCategoryName` String, `ClickMarketPPName` String, `ClickAWAPSCampaignName` String, `ClickPageName` String, `ClickTargetType` UInt16, `ClickTargetPhraseID` UInt64, `ClickContextType` UInt8, `ClickSelectType` Int8, `ClickOptions` String, `ClickGroupBannerID` Int32, `OpenstatServiceName` String, `OpenstatCampaignID` String, `OpenstatAdID` String, `OpenstatSourceID` String, `UTMSource` String, `UTMMedium` String, `UTMCampaign` String, `UTMContent` String, `UTMTerm` String, `FromTag` String, `HasGCLID` UInt8, `FirstVisit` DateTime, `PredLastVisit` Date, `LastVisit` Date, `TotalVisits` UInt32, `TraficSource.ID` Array(Int8), `TraficSource.SearchEngineID` Array(UInt16), `TraficSource.AdvEngineID` Array(UInt8), `TraficSource.PlaceID` Array(UInt16), `TraficSource.SocialSourceNetworkID` Array(UInt8), `TraficSource.Domain` Array(String), `TraficSource.SearchPhrase` Array(String), `TraficSource.SocialSourcePage` Array(String), `Attendance` FixedString(16), `CLID` UInt32, `YCLID` UInt64, `NormalizedRefererHash` UInt64, `SearchPhraseHash` UInt64, `RefererDomainHash` UInt64, `NormalizedStartURLHash` UInt64, `StartURLDomainHash` UInt64, `NormalizedEndURLHash` UInt64, `TopLevelDomain` UInt64, `URLScheme` UInt64, `OpenstatServiceNameHash` UInt64, `OpenstatCampaignIDHash` UInt64, `OpenstatAdIDHash` UInt64, `OpenstatSourceIDHash` UInt64, `UTMSourceHash` UInt64, `UTMMediumHash` UInt64, `UTMCampaignHash` UInt64, `UTMContentHash` UInt64, `UTMTermHash` UInt64, `FromHash` UInt64, `WebVisorEnabled` UInt8, `WebVisorActivity` UInt32, `ParsedParams.Key1` Array(String), `ParsedParams.Key2` Array(String), `ParsedParams.Key3` Array(String), `ParsedParams.Key4` Array(String), `ParsedParams.Key5` Array(String), `ParsedParams.ValueDouble` Array(Float64), `Market.Type` Array(UInt8), `Market.GoalID` Array(UInt32), `Market.OrderID` Array(String), `Market.OrderPrice` Array(Int64), `Market.PP` Array(UInt32), `Market.DirectPlaceID` Array(UInt32), `Market.DirectOrderID` Array(UInt32), `Market.DirectBannerID` Array(UInt32), `Market.GoodID` Array(String), `Market.GoodName` Array(String), `Market.GoodQuantity` Array(Int32), `Market.GoodPrice` Array(Int64), `IslandID` FixedString(16)) ENGINE = Distributed('prod_cluster', 'tutorial', 'visits_local', rand())
2022/03/23 12:53:47.818035 debug CREATE DATABASE IF NOT EXISTS `tutorial`
2022/03/23 12:53:47.829340 debug CREATE TABLE tutorial.hits_local UUID '0ab5ed25-2c59-4158-a316-c4b93c1c40d1'  ON CLUSTER 'prod_cluster' (`WatchID` UInt64, `JavaEnable` UInt8, `Title` String, `GoodEvent` Int16, `EventTime` DateTime, `EventDate` Date, `CounterID` UInt32, `ClientIP` UInt32, `ClientIP6` FixedString(16), `RegionID` UInt32, `UserID` UInt64, `CounterClass` Int8, `OS` UInt8, `UserAgent` UInt8, `URL` String, `Referer` String, `URLDomain` String, `RefererDomain` String, `Refresh` UInt8, `IsRobot` UInt8, `RefererCategories` Array(UInt16), `URLCategories` Array(UInt16), `URLRegions` Array(UInt32), `RefererRegions` Array(UInt32), `ResolutionWidth` UInt16, `ResolutionHeight` UInt16, `ResolutionDepth` UInt8, `FlashMajor` UInt8, `FlashMinor` UInt8, `FlashMinor2` String, `NetMajor` UInt8, `NetMinor` UInt8, `UserAgentMajor` UInt16, `UserAgentMinor` FixedString(2), `CookieEnable` UInt8, `JavascriptEnable` UInt8, `IsMobile` UInt8, `MobilePhone` UInt8, `MobilePhoneModel` String, `Params` String, `IPNetworkID` UInt32, `TraficSourceID` Int8, `SearchEngineID` UInt16, `SearchPhrase` String, `AdvEngineID` UInt8, `IsArtifical` UInt8, `WindowClientWidth` UInt16, `WindowClientHeight` UInt16, `ClientTimeZone` Int16, `ClientEventTime` DateTime, `SilverlightVersion1` UInt8, `SilverlightVersion2` UInt8, `SilverlightVersion3` UInt32, `SilverlightVersion4` UInt16, `PageCharset` String, `CodeVersion` UInt32, `IsLink` UInt8, `IsDownload` UInt8, `IsNotBounce` UInt8, `FUniqID` UInt64, `HID` UInt32, `IsOldCounter` UInt8, `IsEvent` UInt8, `IsParameter` UInt8, `DontCountHits` UInt8, `WithHash` UInt8, `HitColor` FixedString(1), `UTCEventTime` DateTime, `Age` UInt8, `Sex` UInt8, `Income` UInt8, `Interests` UInt16, `Robotness` UInt8, `GeneralInterests` Array(UInt16), `RemoteIP` UInt32, `RemoteIP6` FixedString(16), `WindowName` Int32, `OpenerName` Int32, `HistoryLength` Int16, `BrowserLanguage` FixedString(2), `BrowserCountry` FixedString(2), `SocialNetwork` String, `SocialAction` String, `HTTPError` UInt16, `SendTiming` Int32, `DNSTiming` Int32, `ConnectTiming` Int32, `ResponseStartTiming` Int32, `ResponseEndTiming` Int32, `FetchTiming` Int32, `RedirectTiming` Int32, `DOMInteractiveTiming` Int32, `DOMContentLoadedTiming` Int32, `DOMCompleteTiming` Int32, `LoadEventStartTiming` Int32, `LoadEventEndTiming` Int32, `NSToDOMContentLoadedTiming` Int32, `FirstPaintTiming` Int32, `RedirectCount` Int8, `SocialSourceNetworkID` UInt8, `SocialSourcePage` String, `ParamPrice` Int64, `ParamOrderID` String, `ParamCurrency` FixedString(3), `ParamCurrencyID` UInt16, `GoalsReached` Array(UInt32), `OpenstatServiceName` String, `OpenstatCampaignID` String, `OpenstatAdID` String, `OpenstatSourceID` String, `UTMSource` String, `UTMMedium` String, `UTMCampaign` String, `UTMContent` String, `UTMTerm` String, `FromTag` String, `HasGCLID` UInt8, `RefererHash` UInt64, `URLHash` UInt64, `CLID` UInt32, `YCLID` UInt64, `ShareService` String, `ShareURL` String, `ShareTitle` String, `ParsedParams.Key1` Array(String), `ParsedParams.Key2` Array(String), `ParsedParams.Key3` Array(String), `ParsedParams.Key4` Array(String), `ParsedParams.Key5` Array(String), `ParsedParams.ValueDouble` Array(Float64), `IslandID` FixedString(16), `RequestNum` UInt32, `RequestTry` UInt8) ENGINE = ReplicatedMergeTree('/clickhouse/tables/0ab5ed25-2c59-4158-a316-c4b93c1c40d1/{shard}', 'ip-10-22-180-149.ec2.internal') PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity = 8192
2022/03/23 12:53:47.975993  warn can't create table 'tutorial.hits_local': code: 253, message: There was an error on [ip-10-22-180-149.ec2.internal:9440]: Code: 253. DB::Exception: Replica /clickhouse/tables/0ab5ed25-2c59-4158-a316-c4b93c1c40d1/2/replicas/ip-10-22-180-149.ec2.internal already exists. (REPLICA_IS_ALREADY_EXIST) (version 22.2.2.1), will try again backup=2022-03-23T12-00-43 operation=restore
2022/03/23 12:53:47.976046 debug CREATE DATABASE IF NOT EXISTS `tutorial`
2022/03/23 12:53:47.985488 debug CREATE TABLE tutorial.visits_local UUID 'b227d3b8-7252-48c9-b9f8-eb011af78809'  ON CLUSTER 'prod_cluster' (`CounterID` UInt32, `StartDate` Date, `Sign` Int8, `IsNew` UInt8, `VisitID` UInt64, `UserID` UInt64, `StartTime` DateTime, `Duration` UInt32, `UTCStartTime` DateTime, `PageViews` Int32, `Hits` Int32, `IsBounce` UInt8, `Referer` String, `StartURL` String, `RefererDomain` String, `StartURLDomain` String, `EndURL` String, `LinkURL` String, `IsDownload` UInt8, `TraficSourceID` Int8, `SearchEngineID` UInt16, `SearchPhrase` String, `AdvEngineID` UInt8, `PlaceID` Int32, `RefererCategories` Array(UInt16), `URLCategories` Array(UInt16), `URLRegions` Array(UInt32), `RefererRegions` Array(UInt32), `IsYandex` UInt8, `GoalReachesDepth` Int32, `GoalReachesURL` Int32, `GoalReachesAny` Int32, `SocialSourceNetworkID` UInt8, `SocialSourcePage` String, `MobilePhoneModel` String, `ClientEventTime` DateTime, `RegionID` UInt32, `ClientIP` UInt32, `ClientIP6` FixedString(16), `RemoteIP` UInt32, `RemoteIP6` FixedString(16), `IPNetworkID` UInt32, `SilverlightVersion3` UInt32, `CodeVersion` UInt32, `ResolutionWidth` UInt16, `ResolutionHeight` UInt16, `UserAgentMajor` UInt16, `UserAgentMinor` UInt16, `WindowClientWidth` UInt16, `WindowClientHeight` UInt16, `SilverlightVersion2` UInt8, `SilverlightVersion4` UInt16, `FlashVersion3` UInt16, `FlashVersion4` UInt16, `ClientTimeZone` Int16, `OS` UInt8, `UserAgent` UInt8, `ResolutionDepth` UInt8, `FlashMajor` UInt8, `FlashMinor` UInt8, `NetMajor` UInt8, `NetMinor` UInt8, `MobilePhone` UInt8, `SilverlightVersion1` UInt8, `Age` UInt8, `Sex` UInt8, `Income` UInt8, `JavaEnable` UInt8, `CookieEnable` UInt8, `JavascriptEnable` UInt8, `IsMobile` UInt8, `BrowserLanguage` UInt16, `BrowserCountry` UInt16, `Interests` UInt16, `Robotness` UInt8, `GeneralInterests` Array(UInt16), `Params` Array(String), `Goals.ID` Array(UInt32), `Goals.Serial` Array(UInt32), `Goals.EventTime` Array(DateTime), `Goals.Price` Array(Int64), `Goals.OrderID` Array(String), `Goals.CurrencyID` Array(UInt32), `WatchIDs` Array(UInt64), `ParamSumPrice` Int64, `ParamCurrency` FixedString(3), `ParamCurrencyID` UInt16, `ClickLogID` UInt64, `ClickEventID` Int32, `ClickGoodEvent` Int32, `ClickEventTime` DateTime, `ClickPriorityID` Int32, `ClickPhraseID` Int32, `ClickPageID` Int32, `ClickPlaceID` Int32, `ClickTypeID` Int32, `ClickResourceID` Int32, `ClickCost` UInt32, `ClickClientIP` UInt32, `ClickDomainID` UInt32, `ClickURL` String, `ClickAttempt` UInt8, `ClickOrderID` UInt32, `ClickBannerID` UInt32, `ClickMarketCategoryID` UInt32, `ClickMarketPP` UInt32, `ClickMarketCategoryName` String, `ClickMarketPPName` String, `ClickAWAPSCampaignName` String, `ClickPageName` String, `ClickTargetType` UInt16, `ClickTargetPhraseID` UInt64, `ClickContextType` UInt8, `ClickSelectType` Int8, `ClickOptions` String, `ClickGroupBannerID` Int32, `OpenstatServiceName` String, `OpenstatCampaignID` String, `OpenstatAdID` String, `OpenstatSourceID` String, `UTMSource` String, `UTMMedium` String, `UTMCampaign` String, `UTMContent` String, `UTMTerm` String, `FromTag` String, `HasGCLID` UInt8, `FirstVisit` DateTime, `PredLastVisit` Date, `LastVisit` Date, `TotalVisits` UInt32, `TraficSource.ID` Array(Int8), `TraficSource.SearchEngineID` Array(UInt16), `TraficSource.AdvEngineID` Array(UInt8), `TraficSource.PlaceID` Array(UInt16), `TraficSource.SocialSourceNetworkID` Array(UInt8), `TraficSource.Domain` Array(String), `TraficSource.SearchPhrase` Array(String), `TraficSource.SocialSourcePage` Array(String), `Attendance` FixedString(16), `CLID` UInt32, `YCLID` UInt64, `NormalizedRefererHash` UInt64, `SearchPhraseHash` UInt64, `RefererDomainHash` UInt64, `NormalizedStartURLHash` UInt64, `StartURLDomainHash` UInt64, `NormalizedEndURLHash` UInt64, `TopLevelDomain` UInt64, `URLScheme` UInt64, `OpenstatServiceNameHash` UInt64, `OpenstatCampaignIDHash` UInt64, `OpenstatAdIDHash` UInt64, `OpenstatSourceIDHash` UInt64, `UTMSourceHash` UInt64, `UTMMediumHash` UInt64, `UTMCampaignHash` UInt64, `UTMContentHash` UInt64, `UTMTermHash` UInt64, `FromHash` UInt64, `WebVisorEnabled` UInt8, `WebVisorActivity` UInt32, `ParsedParams.Key1` Array(String), `ParsedParams.Key2` Array(String), `ParsedParams.Key3` Array(String), `ParsedParams.Key4` Array(String), `ParsedParams.Key5` Array(String), `ParsedParams.ValueDouble` Array(Float64), `Market.Type` Array(UInt8), `Market.GoalID` Array(UInt32), `Market.OrderID` Array(String), `Market.OrderPrice` Array(Int64), `Market.PP` Array(UInt32), `Market.DirectPlaceID` Array(UInt32), `Market.DirectOrderID` Array(UInt32), `Market.DirectBannerID` Array(UInt32), `Market.GoodID` Array(String), `Market.GoodName` Array(String), `Market.GoodQuantity` Array(Int32), `Market.GoodPrice` Array(Int64), `IslandID` FixedString(16)) ENGINE = ReplicatedMergeTree('/clickhouse/tables/b227d3b8-7252-48c9-b9f8-eb011af78809/{shard}', 'ip-10-22-180-149.ec2.internal') PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID) SAMPLE BY intHash32(UserID) SETTINGS index_granularity = 8192
2022/03/23 12:53:48.133491 error can't create table `tutorial`.`visits_local`: code: 253, message: There was an error on [ip-10-22-180-149.ec2.internal:9440]: Code: 253. DB::Exception: Replica /clickhouse/tables/b227d3b8-7252-48c9-b9f8-eb011af78809/2/replicas/ip-10-22-180-149.ec2.internal already exists. (REPLICA_IS_ALREADY_EXIST) (version 22.2.2.1) after 4 times, please check your schema dependencies
[root@ip-10-22-180-149 ec2-user]#

So obviously the issue is these lines

2022/03/23 12:53:45.437833 debug SELECT engine FROM system.databases WHERE name = 'tutorial'
2022/03/23 12:53:45.448003 debug DROP TABLE IF EXISTS `tutorial`.`hits_local` ON CLUSTER 'prod_cluster'  NO DELAY
2022/03/23 12:53:46.050378 debug SELECT engine FROM system.databases WHERE name = 'tutorial'
2022/03/23 12:53:46.059718 debug DROP TABLE IF EXISTS `tutorial`.`visits_local` ON CLUSTER 'prod_cluster'  NO DELAY
2022/03/23 12:53:46.561291 debug SELECT engine FROM system.databases WHERE name = 'tutorial'
2022/03/23 12:53:46.574292 debug DROP TABLE IF EXISTS `tutorial`.`hits_all` ON CLUSTER 'prod_cluster'  NO DELAY
2022/03/23 12:53:46.718770 debug SELECT engine FROM system.databases WHERE name = 'tutorial'
2022/03/23 12:53:46.730155 debug DROP TABLE IF EXISTS `tutorial`.`visits_all` ON CLUSTER 'prod_cluster'  NO DELAY

[Slach]

DROP TABLE IF EXISTS tutorial.hits_local ON CLUSTER 'prod_cluster' NO DELAY

It must clean Zookeeper / Clickhouse keeper path /clickhouse/tables/0ab5ed25-2c59-4158-a316-c4b93c1c40d1/2/replicas/ip-10-22-180-149.ec2.internal
if table exists on disk (not only in zoo/keeper) during restore

could you run

clickhouse-client -q 'SYSTEM DROP REPLICA 'ip-10-22-180-149.ec2.internal'
LOG_LEVEL=debug clickhouse-backup restore --rm 2022-03-23T12-00-43

and share results?

[aleclerc-sonrai]

I ran

clickhouse-client -q 'SYSTEM DROP REPLICA 'ip-10-22-180-149.ec2.internal'

from both the 'current' node I'm trying to restore and another one, neither seemed to do anything, I still have the replica in in zookeeper

ip-10-22-167-36.ec2.internal :) select * from system.zookeeper where path = '/clickhouse/tables/0d933126-89c8-4c6b-af2b-5fcdf27e4419/2/replicas/'

SELECT *
FROM system.zookeeper
WHERE path = '/clickhouse/tables/0d933126-89c8-4c6b-af2b-5fcdf27e4419/2/replicas/'

Query id: 1c8f99ba-0239-428e-b7e5-645094191d35

┌─name──────────────────────────┬─value─┬─czxid─┬─mzxid─┬───────────────ctime─┬───────────────mtime─┬─version─┬─cversion─┬─aversion─┬─ephemeralOwner─┬─dataLength─┬─numChildren─┬─pzxid─┬─path────────────────────────────────────────────────────────────────┐
│ ip-10-22-174-114.ec2.internal │       │   441 │   441 │ 2022-03-23 15:01:58 │ 2022-03-23 15:01:58 │       0 │       52 │        0 │              0 │          0 │          13 │   537 │ /clickhouse/tables/0d933126-89c8-4c6b-af2b-5fcdf27e4419/2/replicas/ │
│ ip-10-22-166-127.ec2.internal │       │   430 │   430 │ 2022-03-23 15:01:58 │ 2022-03-23 15:01:58 │       0 │       52 │        0 │              0 │          0 │          13 │   529 │ /clickhouse/tables/0d933126-89c8-4c6b-af2b-5fcdf27e4419/2/replicas/ │
│ ip-10-22-180-149.ec2.internal │       │   422 │   422 │ 2022-03-23 15:01:58 │ 2022-03-23 15:01:58 │       0 │       55 │        0 │              0 │          0 │          12 │  4580 │ /clickhouse/tables/0d933126-89c8-4c6b-af2b-5fcdf27e4419/2/replicas/ │
└───────────────────────────────┴───────┴───────┴───────┴─────────────────────┴─────────────────────┴─────────┴──────────┴──────────┴────────────────┴────────────┴─────────────┴───────┴─────────────────────────────────────────────────────────────────────┘

3 rows in set. Elapsed: 0.003 sec.

I think I might be approaching this all wrong, please bear with me while I explain my use case.

I'm basically trying to replicate total loss of a node. Say I lost an entire instance (ec2 in my case), I'm trying to figure out how to 'recover' that node and get it happy again, ideally with clickhouse-backup. I simulated this by logging into my node, doing a service clickhouse-server stop and then running a rm -rf /var/lib/clickhouse/*, then starting clickhouse back up again.

By doing this, there are no existing databases, it's as if it is brand new. The other 2 replicas are up and happy. The first step, as i can see it, is to get my schema correct.

When i restore using clickhouse-backup I get the above messages (REPLICA_IS_ALREADY_EXIST) because as far as zookeeper is concerned, it doesn't have any knowledge to 'erase' the replica for the table. If i copied all /var/lib/clickhouse-metadata from another node on the cluster, that wouldn't work because the replica name is in all of the ATTACH queries - this is the method suggested in https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replication/#recovery-after-complete-data-loss .
If I was able to do an ATTACH for the schema that were stored by clickhouse-backup, then I think i would be OK. But clickhouse-backup purposefully changes ATTACH to CREATE from what I can tell in the code.

Once the schema is up, data restoration seems like the easiest part. Either I can stream from an existing node or do the data restore from clickhouse-backup. However I think it's the schema generation that's throwing me off. From what I can see I have the following options:

1. Don't use clickhouse-backup, copy the metadata dir from another node and sed in the hostname that I'm on instead of the one I got it from. Restart clickhouse with the force_restore_data flag - essentially this: https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replication/#recovery-after-complete-data-loss
2. Delete all the replica-specific entries for my host that i'm trying to restore in zookeeper. (Not quite sure how to do this yet) and then use clickhouse-backup to restore the schema and get everything aligned.
3. Download the clickhouse-backup and change all the CREATE TABLE to ATTACH TABLE before doing the restore.

Am I correct here? Is there something obvious that I'm missing?

[Slach]

i copied all /var/lib/clickhouse-metadata from another node on the cluster

Why do you do it? Instead of just run clickhouse-backup restore --rm ?

try to use

SYSTEM DROP REPLICA 'ip-10-22-180-149.ec2.internal' FROM ZKPATH '/clickhouse/tables/0ab5ed25-2c59-4158-a316-c4b93c1c40d1/2/replicas';

SELECT * FROM system.zookeeper WHERE path = '/clickhouse/tables/0d933126-89c8-4c6b-af2b-5fcdf27e4419/2/replicas';

and after it

clickhouse-backup restore --rm 2022-03-23T12-00-43

[aleclerc-sonrai]

Ah! This finally worked. My mistake yesterday when I went to run

clickhouse-client -q 'SYSTEM DROP REPLICA 'ip-10-22-180-149.ec2.internal'

I did it from a node that was a different shard (not one in the same shard).

Running the SYSTEM DROP REPLICA now properly saw and clenaed up all the entries in zookeeper. Exmaple:

ip-10-22-174-114.ec2.internal :) SELECT * FROM system.zookeeper WHERE path = '/clickhouse/tables/0d933126-89c8-4c6b-af2b-5fcdf27e4419/2/replicas/'

SELECT *
FROM system.zookeeper
WHERE path = '/clickhouse/tables/0d933126-89c8-4c6b-af2b-5fcdf27e4419/2/replicas/'

Query id: c7d3e7f9-cf87-48ab-adf9-99bc14f16d4b

┌─name──────────────────────────┬─value─┬─czxid─┬─mzxid─┬───────────────ctime─┬───────────────mtime─┬─version─┬─cversion─┬─aversion─┬─ephemeralOwner─┬─dataLength─┬─numChildren─┬─pzxid─┬─path────────────────────────────────────────────────────────────────┐
│ ip-10-22-174-114.ec2.internal │       │   441 │   441 │ 2022-03-23 15:01:58 │ 2022-03-23 15:01:58 │       0 │       52 │        0 │              0 │          0 │          13 │   537 │ /clickhouse/tables/0d933126-89c8-4c6b-af2b-5fcdf27e4419/2/replicas/ │
│ ip-10-22-166-127.ec2.internal │       │   430 │   430 │ 2022-03-23 15:01:58 │ 2022-03-23 15:01:58 │       0 │       52 │        0 │              0 │          0 │          13 │   529 │ /clickhouse/tables/0d933126-89c8-4c6b-af2b-5fcdf27e4419/2/replicas/ │
└───────────────────────────────┴───────┴───────┴───────┴─────────────────────┴─────────────────────┴─────────┴──────────┴──────────┴────────────────┴────────────┴─────────────┴───────┴─────────────────────────────────────────────────────────────────────┘

2 rows in set. Elapsed: 0.008 sec.

And the

clickhouse-backup restore --rm 2022-03-23T16-12-48

worked beautifully. Thanks for the trouble and helping me sort this all out!

[Slach]

ok. still need to figure out why

DROP TABLE IF EXISTS tutorial.hits_local ON CLUSTER 'prod_cluster' NO DELAY

didn't clean replicas in clickhouse-keeper

[aleclerc-sonrai]

That I can't answer. I can certainly test some stuff out if you can point me in the right direction on where to look. I have an easy setup that I've been using to create/bootstrap clusters.

Maybe it's just a timing/race condition?

[Slach]

is issue still reproduced with clickhouse-keeper:22.8 ?