Missing column historical_migration in table `events_recent`
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 39.9k
- Forks
- 3.4k
- Avg merge
- 6h 51m
- Merged PRs (30d)
- 232
Description
Bug Description
Please describe.
ClickHouse could not ingest new events from Kafka topic to the events_recent table due to this error:
. DB::Exception: No such column historical_migration in table posthog.events_recent (c00a3e0e-6a7c-4fbc-b0eb-d70e6b7c87fa): while pushing to view posthog.events_recent_json_mv. (NO_SUCH_COLUMN_I
Additional context
We just recently upgraded our self-hosted PostHog cluster to a much newer version. This error occurred only after the upgrade. All migrations have ran sucessfully.
The current table schema of all tables hat has `events_recent` keyword
Row 1:
──────
name: custom_metrics
engine: View
dependencies_table: []
create_table_query: CREATE VIEW posthog.custom_metrics (`name` String, `labels` Map(String, String), `value` Float64, `help` String, `type` String) AS SELECT * REPLACE (toFloat64(value) AS value) FROM posthog.custom_metrics_test UNION ALL SELECT * REPLACE (toFloat64(value) AS value) FROM posthog.custom_metrics_replication_queue UNION ALL SELECT * REPLACE (toFloat64(value) AS value) FROM posthog.custom_metrics_events_recent_lag UNION ALL SELECT * FROM posthog.custom_metrics_counters UNION ALL SELECT * REPLACE (toFloat64(value) AS value) FROM posthog.custom_metrics_server_crash UNION ALL SELECT * FROM posthog.custom_metrics_table_sizes
as_select: SELECT * REPLACE (toFloat64(value) AS value) FROM posthog.custom_metrics_test UNION ALL SELECT * REPLACE (toFloat64(value) AS value) FROM posthog.custom_metrics_replication_queue UNION ALL SELECT * REPLACE (toFloat64(value) AS value) FROM posthog.custom_metrics_events_recent_lag UNION ALL SELECT * FROM posthog.custom_metrics_counters UNION ALL SELECT * REPLACE (toFloat64(value) AS value) FROM posthog.custom_metrics_server_crash UNION ALL SELECT * FROM posthog.custom_metrics_table_sizes
has_own_data: 0
Row 2:
──────
name: custom_metrics_events_recent_lag
engine: View
dependencies_table: []
create_table_query: CREATE VIEW posthog.custom_metrics_events_recent_lag (`name` String, `labels` Map(String, String), `value` Int64, `help` String, `type` String) AS SELECT 'ClickHouseCustomMetric_EventsRecentIngestionLag' AS name, map('instance', hostname()) AS labels, dateDiff('second', max(timestamp), now()) AS value, 'The number of seconds that have passed since the most recent event was inserted into events_recent table' AS help, 'gauge' AS type FROM posthog.events_recent WHERE (team_id IN []) AND (event IN ('$heartbeat')) AND (timestamp < (now() + toIntervalMinute(3))) AND (inserted_at > (now() - toIntervalHour(3))) GROUP BY event
as_select: SELECT 'ClickHouseCustomMetric_EventsRecentIngestionLag' AS name, map('instance', hostname()) AS labels, dateDiff('second', max(timestamp), now()) AS value, 'The number of seconds that have passed since the most recent event was inserted into events_recent table' AS help, 'gauge' AS type FROM posthog.events_recent WHERE (team_id IN []) AND (event IN ('$heartbeat')) AND (timestamp < (now() + toIntervalMinute(3))) AND (inserted_at > (now() - toIntervalHour(3))) GROUP BY event
has_own_data: 0
Row 3:
──────
name: distributed_events_recent
engine: Distributed
dependencies_table: []
create_table_query: CREATE TABLE posthog.distributed_events_recent (`uuid` UUID, `event` String, `properties` String CODEC(ZSTD(3)), `timestamp` DateTime64(6, 'UTC'), `team_id` Int64, `distinct_id` String, `elements_chain` String, `created_at` DateTime64(6, 'UTC'), `person_id` UUID, `person_created_at` DateTime64(3), `person_properties` String CODEC(ZSTD(3)), `group0_properties` String CODEC(ZSTD(3)), `group1_properties` String CODEC(ZSTD(3)), `group2_properties` String CODEC(ZSTD(3)), `group3_properties` String CODEC(ZSTD(3)), `group4_properties` String CODEC(ZSTD(3)), `group0_created_at` DateTime64(3), `group1_created_at` DateTime64(3), `group2_created_at` DateTime64(3), `group3_created_at` DateTime64(3), `group4_created_at` DateTime64(3), `person_mode` Enum8('full' = 0, 'propertyless' = 1, 'force_upgrade' = 2), `_timestamp` DateTime, `_offset` UInt64, `_partition` UInt64, `inserted_at` Nullable(DateTime64(6, 'UTC')) DEFAULT now64(), `_timestamp_ms` DateTime64(3)) ENGINE = Distributed('posthog_single_shard', 'posthog', 'events_recent', sipHash64(distinct_id))
as_select:
has_own_data: 1
Row 4:
──────
name: events_batch_export_recent
engine: View
dependencies_table: []
create_table_query: CREATE VIEW posthog.events_batch_export_recent AS SELECT team_id AS team_id, timestamp AS timestamp, event AS event, distinct_id AS distinct_id, toString(uuid) AS uuid, inserted_at AS _inserted_at, created_at AS created_at, elements_chain AS elements_chain, toString(person_id) AS person_id, nullIf(properties, '') AS properties, nullIf(person_properties, '') AS person_properties, nullIf(JSONExtractString(properties, '$set'), '') AS set, nullIf(JSONExtractString(properties, '$set_once'), '') AS set_once FROM posthog.events_recent PREWHERE (events_recent.inserted_at >= {interval_start:DateTime64}) AND (events_recent.inserted_at < {interval_end:DateTime64}) WHERE (team_id = {team_id:Int64}) AND ((length({include_events:Array(String)}) = 0) OR (event IN ({include_events:Array(String)}))) AND ((length({exclude_events:Array(String)}) = 0) OR (event NOT IN ({exclude_events:Array(String)}))) ORDER BY _inserted_at ASC, event ASC LIMIT 1 BY team_id, event, cityHash64(events_recent.distinct_id), cityHash64(events_recent.uuid) SETTINGS optimize_aggregation_in_order = 1
as_select: SELECT team_id AS team_id, timestamp AS timestamp, event AS event, distinct_id AS distinct_id, toString(uuid) AS uuid, inserted_at AS _inserted_at, created_at AS created_at, elements_chain AS elements_chain, toString(person_id) AS person_id, nullIf(properties, '') AS properties, nullIf(person_properties, '') AS person_properties, nullIf(JSONExtractString(properties, '$set'), '') AS set, nullIf(JSONExtractString(properties, '$set_once'), '') AS set_once FROM posthog.events_recent PREWHERE (events_recent.inserted_at >= {interval_start:DateTime64}) AND (events_recent.inserted_at < {interval_end:DateTime64}) WHERE (team_id = {team_id:Int64}) AND ((length({include_events:Array(String)}) = 0) OR (event IN ({include_events:Array(String)}))) AND ((length({exclude_events:Array(String)}) = 0) OR (event NOT IN ({exclude_events:Array(String)}))) ORDER BY _inserted_at ASC, event ASC LIMIT 1 BY team_id, event, cityHash64(events_recent.distinct_id), cityHash64(events_recent.uuid) SETTINGS optimize_aggregation_in_order = 1
has_own_data: 0
Row 5:
──────
name: events_recent
engine: ReplicatedReplacingMergeTree
dependencies_table: []
create_table_query: CREATE TABLE posthog.events_recent (`uuid` UUID, `event` String, `properties` String CODEC(ZSTD(3)), `timestamp` DateTime64(6, 'UTC'), `team_id` Int64, `distinct_id` String, `elements_chain` String, `created_at` DateTime64(6, 'UTC'), `person_id` UUID, `person_created_at` DateTime64(3), `person_properties` String CODEC(ZSTD(3)), `group0_properties` String CODEC(ZSTD(3)), `group1_properties` String CODEC(ZSTD(3)), `group2_properties` String CODEC(ZSTD(3)), `group3_properties` String CODEC(ZSTD(3)), `group4_properties` String CODEC(ZSTD(3)), `group0_created_at` DateTime64(3), `group1_created_at` DateTime64(3), `group2_created_at` DateTime64(3), `group3_created_at` DateTime64(3), `group4_created_at` DateTime64(3), `person_mode` Enum8('full' = 0, 'propertyless' = 1, 'force_upgrade' = 2), `_timestamp` DateTime, `_offset` UInt64, `_partition` UInt64, `inserted_at` DateTime64(6, 'UTC') DEFAULT now64(), `_timestamp_ms` DateTime64(3)) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/noshard/posthog.events_recent', '{replica}-{shard}', _timestamp) PARTITION BY toStartOfHour(inserted_at) ORDER BY (team_id, toStartOfHour(inserted_at), event, cityHash64(distinct_id), cityHash64(uuid)) TTL toDateTime(inserted_at) + toIntervalDay(7) SETTINGS index_granularity = 8192
as_select:
has_own_data: 1
Row 6:
──────
name: events_recent_json_mv
engine: MaterializedView
dependencies_table: []
create_table_query: CREATE MATERIALIZED VIEW posthog.events_recent_json_mv TO posthog.writable_events_recent (`uuid` UUID, `event` String, `properties` String, `timestamp` DateTime64(6, 'UTC'), `team_id` Int64, `distinct_id` String, `elements_chain` String, `created_at` DateTime64(6, 'UTC'), `person_id` UUID, `person_created_at` DateTime64(3), `person_properties` String, `group0_properties` String, `group1_properties` String, `group2_properties` String, `group3_properties` String, `group4_properties` String, `group0_created_at` DateTime64(3), `group1_created_at` DateTime64(3), `group2_created_at` DateTime64(3), `group3_created_at` DateTime64(3), `group4_created_at` DateTime64(3), `person_mode` Enum8('full' = 0, 'propertyless' = 1, 'force_upgrade' = 2), `_timestamp` Nullable(DateTime), `_timestamp_ms` Nullable(DateTime64(3)), `_offset` UInt64, `_partition` UInt64) AS SELECT uuid, event, properties, timestamp, team_id, distinct_id, elements_chain, created_at, person_id, person_created_at, person_properties, group0_properties, group1_properties, group2_properties, group3_properties, group4_properties, group0_created_at, group1_created_at, group2_created_at, group3_created_at, group4_created_at, person_mode, _timestamp, _timestamp_ms, _offset, _partition FROM posthog.kafka_events_recent_json
as_select: SELECT uuid, event, properties, timestamp, team_id, distinct_id, elements_chain, created_at, person_id, person_created_at, person_properties, group0_properties, group1_properties, group2_properties, group3_properties, group4_properties, group0_created_at, group1_created_at, group2_created_at, group3_created_at, group4_created_at, person_mode, _timestamp, _timestamp_ms, _offset, _partition FROM posthog.kafka_events_recent_json
has_own_data: 0
Row 7:
──────
name: kafka_events_recent_json
engine: Kafka
dependencies_table: ['events_recent_json_mv']
create_table_query: CREATE TABLE posthog.kafka_events_recent_json (`uuid` UUID, `event` String, `properties` String CODEC(ZSTD(3)), `timestamp` DateTime64(6, 'UTC'), `team_id` Int64, `distinct_id` String, `elements_chain` String, `created_at` DateTime64(6, 'UTC'), `person_id` UUID, `person_created_at` DateTime64(3), `person_properties` String CODEC(ZSTD(3)), `group0_properties` String CODEC(ZSTD(3)), `group1_properties` String CODEC(ZSTD(3)), `group2_properties` String CODEC(ZSTD(3)), `group3_properties` String CODEC(ZSTD(3)), `group4_properties` String CODEC(ZSTD(3)), `group0_created_at` DateTime64(3), `group1_created_at` DateTime64(3), `group2_created_at` DateTime64(3), `group3_created_at` DateTime64(3), `group4_created_at` DateTime64(3), `person_mode` Enum8('full' = 0, 'propertyless' = 1, 'force_upgrade' = 2), `historical_migration` Bool) ENGINE = Kafka(msk_cluster, kafka_topic_list = 'clickhouse_events_json', kafka_group_name = 'group1_recent', kafka_format = 'JSONEachRow') SETTINGS kafka_skip_broken_messages = 100, kafka_num_consumers = 2, kafka_thread_per_consumer = 1
as_select:
has_own_data: 0
Row 8:
──────
name: writable_events_recent
engine: Distributed
dependencies_table: []
create_table_query: CREATE TABLE posthog.writable_events_recent (`uuid` UUID, `event` String, `properties` String CODEC(ZSTD(3)), `timestamp` DateTime64(6, 'UTC'), `team_id` Int64, `distinct_id` String, `elements_chain` String, `created_at` DateTime64(6, 'UTC'), `person_id` UUID, `person_created_at` DateTime64(3), `person_properties` String CODEC(ZSTD(3)), `group0_properties` String CODEC(ZSTD(3)), `group1_properties` String CODEC(ZSTD(3)), `group2_properties` String CODEC(ZSTD(3)), `group3_properties` String CODEC(ZSTD(3)), `group4_properties` String CODEC(ZSTD(3)), `group0_created_at` DateTime64(3), `group1_created_at` DateTime64(3), `group2_created_at` DateTime64(3), `group3_created_at` DateTime64(3), `group4_created_at` DateTime64(3), `person_mode` Enum8('full' = 0, 'propertyless' = 1, 'force_upgrade' = 2), `historical_migration` Bool, `_timestamp` DateTime, `_offset` UInt64, `_partition` UInt64, `_timestamp_ms` DateTime64(3)) ENGINE = Distributed('posthog_batch_exports', 'posthog', 'events_recent')
as_select:
has_own_data: 1
Debug info
- [ ] PostHog Cloud, Debug information: [please copy/paste from https://us.posthog.com/settings/project-details#variables or https://eu.posthog.com/settings/project-details#variables]
- [x] PostHog Hobby self-hosted with `docker compose`, version/commit: [please provide]
- [ ] PostHog self-hosted with Kubernetes (deprecated, see [`Sunsetting Kubernetes support`](https://posthog.com/blog/sunsetting-helm-support-posthog)), version/commit: [aefbf67d36dbd290a584d15708336a03a4e8429b]
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by comparing the displayed definitions for kafka_events_recent_json, events_recent_json_mv, writable_events_recent, and events_recent, focusing on the historical_migration column and the upgrade path. Reproduce the Kafka ingestion error and trace the schema mismatch; done means events can be ingested into events_recent without the NO_SUCH_COLUMN error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- clickhouse, python
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 30/100