PostHog / PostHog/posthog

Missing column historical_migration in table `events_recent`

Open
#46,020 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug
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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.