Can't restore clickhouse backup in version 24.5.0

[max-wittig]

Self-Hosted Version

24.5.0

CPU Architecture

x86_64

Docker Version

25.0.3

Docker Compose Version

2.27.0

Steps to Reproduce

1. Setup continuous backups and restores testing on a separate instance (sentry-prod, sentry-stage). We have 3 components backuping up the system (data with s3 cp, clickhouse with clickhouse-backup, postgres with pgdump and s3 cp
2. Notice how the restore of the clickhouse-backup for 24.5.0 fails, because of several problems (details below):

• Sorting key contains nullable columns, crash when running docker compose run --rm --no-deps clickhouse_backup restore --drop last-backup
• error can't create table `default`.`outcomes_mv_hourly_local`: code: 80, message: Incorrect ATTACH TABLE query for Atomic database engine

Issues identified

1. Inside the backup metadata at backup/clickhouse_backup_2024-06-26T02\:00\:00+00\:00/metadata/default/functions_mv_local.json, allow_nullable_key is not set
2. As clickhouse deprecated the old ordinary database format, we need to convert to atomic, as we drop the whole table in the stage restore and the new default is atomic.

How we fixed the issues

1. Modify backup to add allow_nullable_key into the backup json file that was created.
2. Switch our production database to atomic as it was in ordinary before, which is deprecated. See:

• Setting to forbid creation of Ordinary databases ClickHouse/ClickHouse#38312
• Add flag that enables automatic conversion from Ordinary to Atomic ClickHouse/ClickHouse#39933

Commands we've executed

#!/bin/bash

# on prod
sudo docker compose run --rm --no-deps --quiet-pull clickhouse_backup create "before-atomic"
sudo docker compose run --rm --no-deps --quiet-pull clickhouse_backup upload "before-atomic"
sudo touch /data/sentry/clickhouse/flags/convert_ordinary_to_atomic
sudo docker compose restart clickhouse
sudo docker compose run --rm --no-deps --quiet-pull clickhouse_backup create "after-atomic"
sudo docker compose run --rm --no-deps --quiet-pull clickhouse_backup upload "after-atomic"

# stage restore atomic
sudo docker compose run --rm --no-deps clickhouse_backup download "after-atomic"
sudo docker compose run --rm --no-deps clickhouse_backup restore --drop "after-atomic"

---

# restore failed, so edit /data/sentry/clickhouse/backup/clickhouse_backup_2024-06-26T02\:00\:00+00\:00/metadata/default/functions_mv_local.json
- # "query": "CREATE TABLE default.functions_mv_local (`project_id` UInt64, `transaction_name` String, `timestamp` DateTime, `depth` UInt32, `parent_fingerprint` UInt64, `fingerprint` UInt64, `name` String, `package` String, `path` String, `is_application` UInt8, `platform` LowCardinality(String), `environment` LowCardinality(Nullable(String)), `release` LowCardinality(Nullable(String)), `os_name` LowCardinality(String), `os_version` LowCardinality(String), `retention_days` UInt16, `count` AggregateFunction(count, Float64), `percentiles` AggregateFunction(quantiles(0.5, 0.75, 0.9, 0.95, 0.99), Float64), `min` AggregateFunction(min, Float64), `max` AggregateFunction(max, Float64), `avg` AggregateFunction(avg, Float64), `sum` AggregateFunction(sum, Float64), `worst` AggregateFunction(argMax, UUID, Float64), `examples` AggregateFunction(groupUniqArray(5), UUID)) ENGINE = AggregatingMergeTree PARTITION BY (retention_days, toMonday(timestamp)) PRIMARY KEY (project_id, transaction_name, timestamp, depth, parent_fingerprint, fingerprint) ORDER BY (project_id, transaction_name, timestamp, depth, parent_fingerprint, fingerprint, name, package, path, is_application, platform, environment, release, os_name, os_version, retention_days) TTL timestamp + toIntervalDay(retention_days) SETTINGS index_granularity = 2048",
+ # "query": "CREATE TABLE default.functions_mv_local (`project_id` UInt64, `transaction_name` String, `timestamp` DateTime, `depth` UInt32, `parent_fingerprint` UInt64, `fingerprint` UInt64, `name` String, `package` String, `path` String, `is_application` UInt8, `platform` LowCardinality(String), `environment` LowCardinality(Nullable(String)), `release` LowCardinality(Nullable(String)), `os_name` LowCardinality(String), `os_version` LowCardinality(String), `retention_days` UInt16, `count` AggregateFunction(count, Float64), `percentiles` AggregateFunction(quantiles(0.5, 0.75, 0.9, 0.95, 0.99), Float64), `min` AggregateFunction(min, Float64), `max` AggregateFunction(max, Float64), `avg` AggregateFunction(avg, Float64), `sum` AggregateFunction(sum, Float64), `worst` AggregateFunction(argMax, UUID, Float64), `examples` AggregateFunction(groupUniqArray(5), UUID)) ENGINE = AggregatingMergeTree PARTITION BY (retention_days, toMonday(timestamp)) PRIMARY KEY (project_id, transaction_name, timestamp, depth, parent_fingerprint, fingerprint) ORDER BY (project_id, transaction_name, timestamp, depth, parent_fingerprint, fingerprint, name, package, path, is_application, platform, environment, release, os_name, os_version, retention_days) TTL timestamp + toIntervalDay(retention_days) SETTINGS index_granularity = 2048, allow_nullable_key = 1",

sudo docker compose run --rm --no-deps clickhouse_backup restore --drop "after-atomic" # run again
sudo docker compose run --rm --no-deps clickhouse_backup delete local "after-atomic"
sudo docker compose restart clickhouse

Please let us know, if you've experienced this as well or if we did something wrong or how you deal with testing your restores on the system.

Expected Result

Smooth restore, as with every release since clickhouse exists. Way before 10.X

Actual Result

fingerprint` UInt64, `fingerprint` UInt64, `name` String, `package` String, `path` String, `is_application` UInt8, `platform` LowCardinality(String), `environment` LowCardinality(Nullable(String)), `release` LowCardinal[0/3256]
able(String)), `os_name` LowCardinality(String), `os_version` LowCardinality(String), `retention_days` UInt16, `count` AggregateFunction(count, Float64), `percentiles` AggregateFunction(quantiles(0.5, 0.75, 0.9, 0.95, 0.99), Fl
oat64), `min` AggregateFunction(min, Float64), `max` AggregateFunction(max, Float64), `avg` AggregateFunction(avg, Float64), `sum` AggregateFunction(sum, Float64), `worst` AggregateFunction(argMax, UUID, Float64), `examples` Ag
gregateFunction(groupUniqArray(5), UUID)) ENGINE = AggregatingMergeTree PARTITION BY (retention_days, toMonday(timestamp)) PRIMARY KEY (project_id, transaction_name, timestamp, depth, parent_fingerprint, fingerprint) ORDER BY (
project_id, transaction_name, timestamp, depth, parent_fingerprint, fingerprint, name, package, path, is_application, platform, environment, release, os_name, os_version, retention_days) TTL timestamp + toIntervalDay(retention_
days) SETTINGS index_granularity = 2048 logger=clickhouse", "2024/06/25 00:47:01.101488  warn can't create table 'default.functions_mv_local': code: 44, message: Sorting key contains nullable columns, but merge tree setting `al
low_nullable_key` is disabled, will try again backup=clickhouse_backup_2024-06-24T02:00:03+00:00 operation=restore_schema", "2024/06/25 00:47:01.101728  info CREATE TABLE default.functions_mv_local (`project_id` UInt64, `transa
ction_name` String, `timestamp` DateTime, `depth` UInt32, `parent_fingerprint` UInt64, `fingerprint` UInt64, `name` String, `package` String, `path` String, `is_application` UInt8, `platform` LowCardinality(String), `environmen
t` LowCardinality(Nullable(String)), `release` LowCardinality(Nullable(String)), `os_name` LowCardinality(String), `os_version` LowCardinality(String), `retention_days` UInt16, `count` AggregateFunction(count, Float64), `percen
tiles` AggregateFunction(quantiles(0.5, 0.75, 0.9, 0.95, 0.99), Float64), `min` AggregateFunction(min, Float64), `max` AggregateFunction(max, Float64), `avg` AggregateFunction(avg, Float64), `sum` AggregateFunction(sum, Float64
), `worst` AggregateFunction(argMax, UUID, Float64), `examples` AggregateFunction(groupUniqArray(5), UUID)) ENGINE = AggregatingMergeTree PARTITION BY (retention_days, toMonday(timestamp)) PRIMARY KEY (project_id, transaction_n
ame, timestamp, depth, parent_fingerprint, fingerprint) ORDER BY (project_id, transaction_name, timestamp, depth, parent_fingerprint, fingerprint, name, package, path, is_application, platform, environment, release, os_name, os
_version, retention_days) TTL timestamp + toIntervalDay(retention_days) SETTINGS index_granularity = 2048 logger=clickhouse", "2024/06/25 00:47:01.106843  warn can't create table 'default.functions_mv_local': code: 44, message:
 Sorting key contains nullable columns, but merge tree setting `allow_nullable_key` is disabled, will try again backup=clickhouse_backup_2024-06-24T02:00:03+00:00 operation=restore_schema", "2024/06/25 00:47:01.106919  info CRE
ATE TABLE default.functions_mv_local (`project_id` UInt64, `transaction_name` String, `timestamp` DateTime, `depth` UInt32, `parent_fingerprint` UInt64, `fingerprint` UInt64, `name` String, `package` String, `path` String, `is_
application` UInt8, `platform` LowCardinality(String), `environment` LowCardinality(Nullable(String)), `release` LowCardinality(Nullable(String)), `os_name` LowCardinality(String), `os_version` LowCardinality(String), `retentio
n_days` UInt16, `count` AggregateFunction(count, Float64), `percentiles` AggregateFunction(quantiles(0.5, 0.75, 0.9, 0.95, 0.99), Float64), `min` AggregateFunction(min, Float64), `max` AggregateFunction(max, Float64), `avg` Agg
regateFunction(avg, Float64), `sum` AggregateFunction(sum, Float64), `worst` AggregateFunction(argMax, UUID, Float64), `examples` AggregateFunction(groupUniqArray(5), UUID)) ENGINE = AggregatingMergeTree PARTITION BY (retention
_days, toMonday(timestamp)) PRIMARY KEY (project_id, transaction_name, timestamp, depth, parent_fingerprint, fingerprint) ORDER BY (project_id, transaction_name, timestamp, depth, parent_fingerprint, fingerprint, name, package,
 path, is_application, platform, environment, release, os_name, os_version, retention_days) TTL timestamp + toIntervalDay(retention_days) SETTINGS index_granularity = 2048 logger=clickhouse", "2024/06/25 00:47:01.111197  warn c
an't create table 'default.functions_mv_local': code: 44, message: Sorting key contains nullable columns, but merge tree setting `allow_nullable_key` is disabled, will try again backup=clickhouse_backup_2024-06-24T02:00:03+00:0
0 operation=restore_schema", "2024/06/25 00:47:01.111309  info CREATE TABLE default.functions_mv_local (`project_id` UInt64, `transaction_name` String, `timestamp` DateTime, `depth` UInt32, `parent_fingerprint` UInt64, `fingerp
rint` UInt64, `name` String, `package` String, `path` String, `is_application` UInt8, `platform` LowCardinality(String), `environment` LowCardinality(Nullable(String)), `release` LowCardinality(Nullable(String)), `os_name` LowC
ardinality(String), `os_version` LowCardinality(String), `retention_days` UInt16, `count` AggregateFunction(count, Float64), `percentiles` AggregateFunction(quantiles(0.5, 0.75, 0.9, 0.95, 0.99), Float64), `min` AggregateFuncti
on(min, Float64), `max` AggregateFunction(max, Float64), `avg` AggregateFunction(avg, Float64), `sum` AggregateFunction(sum, Float64), `worst` AggregateFunction(argMax, UUID, Float64), `examples` AggregateFunction(groupUniqArra
y(5), UUID)) ENGINE = AggregatingMergeTree PARTITION BY (retention_days, toMonday(timestamp)) PRIMARY KEY (project_id, transaction_name, timestamp, depth, parent_fingerprint, fingerprint) ORDER BY (project_id, transaction_name,
 timestamp, depth, parent_fingerprint, fingerprint, name, package, path, is_application, platform, environment, release, os_name, os_version, retention_days) TTL timestamp + toIntervalDay(retention_days) SETTINGS index_granular
ity = 2048 logger=clickhouse", "2024/06/25 00:47:01.115534  warn can't create table 'default.functions_mv_local': code: 44, message: Sorting key contains nullable columns, but merge tree setting `allow_nullable_key` is disabled
, will try again backup=clickhouse_backup_2024-06-24T02:00:03+00:00 operation=restore_schema", "2024/06/25 00:47:01.115612  info CREATE TABLE default.functions_mv_local (`project_id` UInt64, `transaction_name` String, `timestam
p` DateTime, `depth` UInt32, `parent_fingerprint` UInt64, `fingerprint` UInt64, `name` String, `package` String, `path` String, `is_application` UInt8, `platform` LowCardinality(String), `environment` LowCardinality(Nullable(St
ring)), `release` LowCardinality(Nullable(String)), `os_name` LowCardinality(String), `os_version` LowCardinality(String), `retention_days` UInt16, `count` AggregateFunction(count, Float64), `percentiles` AggregateFunction(quan
tiles(0.5, 0.75, 0.9, 0.95, 0.99), Float64), `min` AggregateFunction(min, Float64), `max` AggregateFunction(max, Float64), `avg` AggregateFunction(avg, Float64), `sum` AggregateFunction(sum, Float64), `worst` AggregateFunction(
argMax, UUID, Float64), `examples` AggregateFunction(groupUniqArray(5), UUID)) ENGINE = AggregatingMergeTree PARTITION BY (retention_days, toMonday(timestamp)) PRIMARY KEY (project_id, transaction_name, timestamp, depth, parent
_fingerprint, fingerprint) ORDER BY (project_id, transaction_name, timestamp, depth, parent_fingerprint, fingerprint, name, package, path, is_application, platform, environment, release, os_name, os_version, retention_days) TTL
 timestamp + toIntervalDay(retention_days) SETTINGS index_granularity = 2048 logger=clickhouse", "2024/06/25 00:47:01.119870  warn can't create table 'default.functions_mv_local': code: 44, message: Sorting key contains nullabl
e columns, but merge tree setting `allow_nullable_key` is disabled, will try again backup=clickhouse_backup_2024-06-24T02:00:03+00:00 operation=restore_schema", "2024/06/25 00:47:01.119948  info CREATE TABLE default.functions_m
v_local (`project_id` UInt64, `transaction_name` String, `timestamp` DateTime, `depth` UInt32, `parent_fingerprint` UInt64, `fingerprint` UInt64, `name` String, `package` String, `path` String, `is_application` UInt8, `platform
` LowCardinality(String), `environment` LowCardinality(Nullable(String)), `release` LowCardinality(Nullable(String)), `os_name` LowCardinality(String), `os_version` LowCardinality(String), `retention_days` UInt16, `count` Aggre
gateFunction(count, Float64), `percentiles` AggregateFunction(quantiles(0.5, 0.75, 0.9, 0.95, 0.99), Float64), `min` AggregateFunction(min, Float64), `max` AggregateFunction(max, Float64), `avg` AggregateFunction(avg, Float64),
 `sum` AggregateFunction(sum, Float64), `worst` AggregateFunction(argMax, UUID, Float64), `examples` AggregateFunction(groupUniqArray(5), UUID)) ENGINE = AggregatingMergeTree PARTITION BY (retention_days, toMonday(timestamp)) P
RIMARY KEY (project_id, transaction_name, timestamp, depth, parent_fingerprint, fingerprint) ORDER BY (project_id, transaction_name, timestamp, depth, parent_fingerprint, fingerprint, name, package, path, is_application, platfo
rm, environment, release, os_name, os_version, retention_days) TTL timestamp + toIntervalDay(retention_days) SETTINGS index_granularity = 2048 logger=clickhouse", "2024/06/25 00:47:01.124599  warn can't create table 'default.fu
nctions_mv_local': code: 44, message: Sorting key contains nullable columns, but merge tree setting `allow_nullable_key` is disabled, will try again backup=clickhouse_backup_2024-06-24T02:00:03+00:00 operation=restore_schema", 
"2024/06/25 00:47:01.124692  info CREATE TABLE default.functions_mv_local (`project_id` UInt64, `transaction_name` String, `timestamp` DateTime, `depth` UInt32, `parent_fingerprint` UInt64, `fingerprint` UInt64, `name` String, 
`package` String, `path` String, `is_application` UInt8, `platform` LowCardinality(String), `environment` LowCardinality(Nullable(String)), `release` LowCardinality(Nullable(String)), `os_name` LowCardinality(String), `os_versi
on` LowCardinality(String), `retention_days` UInt16, `count` AggregateFunction(count, Float64), `percentiles` AggregateFunction(quantiles(0.5, 0.75, 0.9, 0.95, 0.99), Float64), `min` AggregateFunction(min, Float64), `max` Aggre
gateFunction(max, Float64), `avg` AggregateFunction(avg, Float64), `sum` AggregateFunction(sum, Float64), `worst` AggregateFunction(argMax, UUID, Float64), `examples` AggregateFunction(groupUniqArray(5), UUID)) ENGINE = Aggrega
tingMergeTree PARTITION BY (retention_days, toMonday(timestamp)) PRIMARY KEY (project_id, transaction_name, timestamp, depth, parent_fingerprint, fingerprint) ORDER BY (project_id, transaction_name, timestamp, depth, parent_fin
gerprint, fingerprint, name, package, path, is_application, platform, environment, release, os_name, os_version, retention_days) TTL timestamp + toIntervalDay(retention_days) SETTINGS index_granularity = 2048 logger=clickhouse"
, "2024/06/25 00:47:01.129089  warn can't create table 'default.functions_mv_local': code: 44, message: Sorting key contains nullable columns, but merge tree setting `allow_nullable_key` is disabled, will try again backup=click
house_backup_2024-06-24T02:00:03+00:00 operation=restore_schema", "2024/06/25 00:47:01.129164  info CREATE TABLE default.functions_mv_local (`project_id` UInt64, `transaction_name` String, `timestamp` DateTime, `depth` UInt32, 
`parent_fingerprint` UInt64, `fingerprint` UInt64, `name` String, `package` String, `path` String, `is_application` UInt8, `platform` LowCardinality(String), `environment` LowCardinality(Nullable(String)), `release` LowCardinal
ity(Nullable(String)), `os_name` LowCardinality(String), `os_version` LowCardinality(String), `retention_days` UInt16, `count` AggregateFunction(count, Float64), `percentiles` AggregateFunction(quantiles(0.5, 0.75, 0.9, 0.95, 0
.99), Float64), `min` AggregateFunction(min, Float64), `max` AggregateFunction(max, Float64), `avg` AggregateFunction(avg, Float64), `sum` AggregateFunction(sum, Float64), `worst` AggregateFunction(argMax, UUID, Float64), `exam
ples` AggregateFunction(groupUniqArray(5), UUID)) ENGINE = AggregatingMergeTree PARTITION BY (retention_days, toMonday(timestamp)) PRIMARY KEY (project_id, transaction_name, timestamp, depth, parent_fingerprint, fingerprint) OR
DER BY (project_id, transaction_name, timestamp, depth, parent_fingerprint, fingerprint, name, package, path, is_application, platform, environment, release, os_name, os_version, retention_days) TTL timestamp + toIntervalDay(re
tention_days) SETTINGS index_granularity = 2048 logger=clickhouse", "2024/06/25 00:47:01.133983  warn can't create table 'default.functions_mv_local': code: 44, message: Sorting key contains nullable columns, but merge tree set
ting `allow_nullable_key` is disabled, will try again backup=clickhouse_backup_2024-06-24T02:00:03+00:00 operation=restore_schema", "2024/06/25 00:47:01.134206  info CREATE TABLE default.functions_mv_local (`project_id` UInt64,
 `transaction_name` String, `timestamp` DateTime, `depth` UInt32, `parent_fingerprint` UInt64, `fingerprint` UInt64, `name` String, `package` String, `path` String, `is_application` UInt8, `platform` LowCardinality(String), `en
vironment` LowCardinality(Nullable(String)), `release` LowCardinality(Nullable(String)), `os_name` LowCardinality(String), `os_version` LowCardinality(String), `retention_days` UInt16, `count` AggregateFunction(count, Float64),
 `percentiles` AggregateFunction(quantiles(0.5, 0.75, 0.9, 0.95, 0.99), Float64), `min` AggregateFunction(min, Float64), `max` AggregateFunction(max, Float64), `avg` AggregateFunction(avg, Float64), `sum` AggregateFunction(sum,
 Float64), `worst` AggregateFunction(argMax, UUID, Float64), `examples` AggregateFunction(groupUniqArray(5), UUID)) ENGINE = AggregatingMergeTree PARTITION BY (retention_days, toMonday(timestamp)) PRIMARY KEY (project_id, trans
action_name, timestamp, depth, parent_fingerprint, fingerprint) ORDER BY (project_id, transaction_name, timestamp, depth, parent_fingerprint, fingerprint, name, package, path, is_application, platform, environment, release, os_
name, os_version, retention_days) TTL timestamp + toIntervalDay(retention_days) SETTINGS index_granularity = 2048 logger=clickhouse", "2024/06/25 00:47:01.140251  info clickhouse connection closed logger=clickhouse", "2024/06/2
5 00:47:01.140378 error can't create table `default`.`functions_mv_local`: code: 44, message: Sorting key contains nullable columns, but merge tree setting `allow_nullable_key` is disabled after 58 times, please check your sche
ma dependencies"]}

---

2024/06/25 15:27:06.236416  warn can't create table 'default.generic_metric_gauges_aggregation_mv': code: 80, message: Incorrect ATTACH TABLE query for Atomic database engine. Use one of the following queries instead:
1. ATTACH TABLE generic_metric_gauges_aggregation_mv;
2. CREATE TABLE generic_metric_gauges_aggregation_mv <table definition>;
3. ATTACH TABLE generic_metric_gauges_aggregation_mv FROM '/path/to/data/' <table definition>;
4. ATTACH TABLE generic_metric_gauges_aggregation_mv UUID '<uuid>' <table definition>;, will try again backup=clickhouse_backup_2024-06-25T02:00:05+00:00 operation=restore_schema
2024/06/25 15:27:06.236539  info ATTACH MATERIALIZED VIEW default.generic_metric_sets_meta_tag_values_mv TO default.generic_metric_sets_meta_tag_values_local (`project_id` UInt64, `metric_id` UInt64, `tag_key` UInt64, `tag_value` String, `timestamp` DateTime CODEC(DoubleDelta), `retention_days` UInt16, `count` AggregateFunction(sum, Float64)) AS SELECT project_id, metric_id, tag_key, tag_value, toMonday(timestamp) AS timestamp, retention_days, sumState(count_value) AS count FROM default.generic_metric_sets_raw_local ARRAY JOIN tags.key AS tag_key, tags.raw_value AS tag_value WHERE record_meta = 1 GROUP BY project_id, metric_id, tag_key, tag_value, timestamp, retention_days logger=clickhouse
2024/06/25 15:27:06.239340  warn can't create table 'default.generic_metric_sets_meta_tag_values_mv': code: 80, message: Incorrect ATTACH TABLE query for Atomic database engine. Use one of the following queries instead:
1. ATTACH TABLE generic_metric_sets_meta_tag_values_mv;
2. CREATE TABLE generic_metric_sets_meta_tag_values_mv <table definition>;
3. ATTACH TABLE generic_metric_sets_meta_tag_values_mv FROM '/path/to/data/' <table definition>;
4. ATTACH TABLE generic_metric_sets_meta_tag_values_mv UUID '<uuid>' <table definition>;, will try again backup=clickhouse_backup_2024-06-25T02:00:05+00:00 operation=restore_schema
2024/06/25 15:27:06.239419  info ATTACH MATERIALIZED VIEW default.outcomes_mv_hourly_local TO default.outcomes_hourly_local (`org_id` UInt64, `project_id` UInt64, `key_id` UInt64, `timestamp` DateTime, `outcome` UInt8, `reason` String, `category` UInt8, `quantity` UInt64, `times_seen` UInt64) AS SELECT org_id, project_id, ifNull(key_id, 0) AS key_id, toStartOfHour(timestamp) AS timestamp, outcome, ifNull(reason, 'none') AS reason, category, count() AS times_seen, sum(quantity) AS quantity FROM default.outcomes_raw_local GROUP BY org_id, project_id, key_id, timestamp, outcome, reason, category logger=clickhouse
2024/06/25 15:27:06.241480  info clickhouse connection closed logger=clickhouse
2024/06/25 15:27:06.241512 error can't create table `default`.`outcomes_mv_hourly_local`: code: 80, message: Incorrect ATTACH TABLE query for Atomic database engine. Use one of the following queries instead:
1. ATTACH TABLE outcomes_mv_hourly_local;
2. CREATE TABLE outcomes_mv_hourly_local <table definition>;
3. ATTACH TABLE outcomes_mv_hourly_local FROM '/path/to/data/' <table definition>;
4. ATTACH TABLE outcomes_mv_hourly_local UUID '<uuid>' <table definition>; after 58 times, please check your schema dependencies

Event ID

No response

[hubertdeng123]

Am I reading this right, you are upgrading from Sentry 10.x? Clickhouse backups are not something that Sentry itself owns, but I am curious to why this may be failing for you. I would assume that if you're able to upgrade to 24.5.0 successfully, that means you are on clickhouse 23.8.

[max-wittig]

@hubertdeng123 No. We're upgrading from 24.5.0 to 24.6.0. I was just implying that we've been using Sentry for a long time and this might be the cause of the issues we're facing (legacy clickhouse DB scheme etc.). Yes we use clickhouse 23.8.

Basically we fixed it, but I'm sure that more of your customers will run into this as clickhouse removed compatibility for the ordinary database format

[getsantry]

This issue has gone three weeks without activity. In another week, I will close it.

But! If you comment or otherwise update it, I will reset the clock, and if you remove the label Waiting for: Community, I will leave it alone ... forever!

---

"A weed is but an unloved flower." ― Ella Wheeler Wilcox 🥀

[max-wittig]

@hubertdeng123 HI! This happened again, when restoring 24.06 😦 Any ideas?

In addition we got these problems now, when restoring postgres (unique constraints):

• sentry_commitauthor_organization_id: Key (organization_id, email)=(13, [email protected])
• sentry_grouprelease_group_id: (group_id, release_id, environment)=(376485, 21868, Stage)
• sentry_projectoptions_project_id: (project_id, key)=(180, sentry:relay-rev)
• unique_org_string: (string, organization_id)=(3.54.2, 2)
• sentry_release_organization_id: (organization_id, version)=(138, @[email protected])

[hubertdeng123]

Looks like that may be an issue seen here: getsentry/self-hosted#3167

[max-wittig]

There are multiple issues in this one now. We had the same while trying to restore the backup for 24.7.0.

[max-wittig]

Any news here? Seems like something on Sentry side must be wrongly set and I'm tempted to issue a query to fix it in clickhouse. E.g. https://www.markhneedham.com/blog/2024/06/27/clickhouse-unknown-setting-allow_nullable_key/

[max-wittig]

Maybe it can be fixed by upgrading clickhouse to > 23.9

See: Aiven-Open/astacus#175

[max-wittig]

@hubertdeng123 The clickhouse-backup team told us that creating this table with clickhouse 23.X would no longer work. I'm assuming that Sentry only still works because the tables were created before clickhouse 23. See: Altinity/clickhouse-backup#1005 (comment)

What do you think about this?

[hubertdeng123]

I'm going to transfer this over to snuba since they are more familiar with clickhouse and its used at Sentry. Thanks for documenting your investigation!

[max-wittig]

Thanks for fixing this!