clickhouse-backup icon indicating copy to clipboard operation
clickhouse-backup copied to clipboard
verified publisher icon Altinity

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

Open aleclerc-sonrai opened this issue 3 years ago • 15 comments

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

aleclerc-sonrai avatar Mar 18 '22 18:03 aleclerc-sonrai

which clickhouse-backup version do you use?

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

Slach avatar Mar 19 '22 02:03 Slach

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

DipalPrajapati avatar Mar 21 '22 10:03 DipalPrajapati

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.

aleclerc-sonrai avatar Mar 21 '22 12:03 aleclerc-sonrai

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

Slach avatar Mar 21 '22 13:03 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.

Slach avatar Mar 21 '22 13:03 Slach

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 avatar Mar 21 '22 13:03 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?

aleclerc-sonrai avatar Mar 21 '22 13:03 aleclerc-sonrai

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

Slach avatar Mar 21 '22 13:03 Slach 

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

aleclerc-sonrai avatar Mar 23 '22 12:03 aleclerc-sonrai

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?

Slach avatar Mar 23 '22 13:03 Slach

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?

aleclerc-sonrai avatar Mar 23 '22 17:03 aleclerc-sonrai

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

Slach avatar Mar 24 '22 03:03 Slach

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!

aleclerc-sonrai avatar Mar 24 '22 13:03 aleclerc-sonrai

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

Slach avatar Mar 24 '22 13:03 Slach

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?

aleclerc-sonrai avatar Mar 24 '22 17:03 aleclerc-sonrai

is issue still reproduced with clickhouse-keeper:22.8 ?

Slach avatar Oct 20 '22 10:10 Slach