Old data invisible in some views after upgrade to v2.0.0

nh2 commented 3 weeks ago

Past Issues Searched

[X] I have searched open and closed issues to make sure that the bug has not yet been reported

Issue is a Bug Report

[X] This is a bug report and not a feature request, nor asking for self-hosted support

Using official Plausible Cloud hosting or self-hosting?

Self-hosting

Describe the bug

After executing the upgrade to v2.0.0 incuding the data migration, I can see old data (good) but only in some views such as Last 12 months (?period=12mo) I cannot view data older than the migration.

All time and Custom range are broken: they start at today (the day I did the migration).

So the historical data is clearly there but Plausible seems to have gotten into a state where older dates cannot be selected in some views, based on the time of the migration (today).

Expected behavior

I can see all historical data in all views.

Screenshots

Last 12 months is working:

All time only shows today:

Custom range only lets me select today:

Output of the v2.0.0 migration script Plausible.DataMigration.NumericIDs.run:

``` check-replicas Done! ------------------------------------------------------------------------------ list-partitions Done! ------------------------------------------------------------------------------ Got the following migration settings: - max_threads: 16 - table_settings: SETTINGS index_granularity = 8192 - db url: http://localhost:8123/default - cluster?: false - partitions to do: ["202107", "202108", "202109", "202110", "202111", "202112", "202201", "202202", "202203", "202204", "202205", "202206", "202207", "202208", "202209", "202210", "202211", "202212", "202301", "202302", "202303", "202304", "202305", "202306", "202307", "202308", "202311", "202312", "202401", "202402", "202403", "202404", "202405", "202406", "202407", "202408", "202409", "202410", "202411"] - start from: 202107 - stop at: Running drop-events-v2 -> Query: DROP TABLE IF EXISTS events_v2 SYNC The table contains 1039 rows. Execute? [y/N]: y drop-events-v2 Done! ------------------------------------------------------------------------------ Running drop-sessions-v2 -> Query: DROP TABLE IF EXISTS sessions_v2 SYNC The table contains 103 rows. Execute? [y/N]: y drop-sessions-v2 Done! ------------------------------------------------------------------------------ Running drop-tmp-events-v2 -> Query: DROP TABLE IF EXISTS tmp_events_v2 SYNC Execute? [Y/n]: y drop-tmp-events-v2 Done! ------------------------------------------------------------------------------ Running drop-tmp-sessions-v2 -> Query: DROP TABLE IF EXISTS tmp_sessions_v2 SYNC Execute? [Y/n]: y drop-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ Running drop-domains-lookup -> Query: DROP TABLE IF EXISTS domains_lookup SYNC; Execute? [Y/n]: y drop-domains-lookup Done! ------------------------------------------------------------------------------ Running create-events-v2 -> Query: CREATE TABLE IF NOT EXISTS events_v2 ( `timestamp` DateTime CODEC(Delta(4), LZ4), `name` LowCardinality(String), `site_id` UInt64, `user_id` UInt64, `session_id` UInt64, `hostname` String, `pathname` String CODEC(ZSTD(3)), `referrer` String, `referrer_source` String, `country_code` FixedString(2), `screen_size` LowCardinality(String), `operating_system` LowCardinality(String), `browser` LowCardinality(String), `utm_medium` String, `utm_source` String, `utm_campaign` String, `meta.key` Array(String), `meta.value` Array(String), `browser_version` LowCardinality(String), `operating_system_version` LowCardinality(String), `subdivision1_code` LowCardinality(String), `subdivision2_code` LowCardinality(String), `city_geoname_id` UInt32, `utm_content` String, `utm_term` String, `transferred_from` String ) ENGINE = MergeTree() PARTITION BY toYYYYMM(timestamp) PRIMARY KEY (site_id, toDate(timestamp), name, user_id) ORDER BY (site_id, toDate(timestamp), name, user_id, timestamp) SAMPLE BY user_id SETTINGS index_granularity = 8192 Execute? [Y/n]: create-events-v2 Done! ------------------------------------------------------------------------------ Running create-sessions-v2 -> Query: CREATE TABLE IF NOT EXISTS sessions_v2 ( `session_id` UInt64, `sign` Int8, `site_id` UInt64, `user_id` UInt64, `hostname` String, `timestamp` DateTime CODEC(DoubleDelta, LZ4), `start` DateTime CODEC(DoubleDelta, LZ4), `is_bounce` UInt8, `entry_page` String, `exit_page` String, `pageviews` Int32, `events` Int32, `duration` UInt32, `referrer` String, `referrer_source` String, `country_code` LowCardinality(FixedString(2)), `screen_size` LowCardinality(String), `operating_system` LowCardinality(String), `browser` LowCardinality(String), `utm_medium` String, `utm_source` String, `utm_campaign` String, `browser_version` LowCardinality(String), `operating_system_version` LowCardinality(String), `subdivision1_code` LowCardinality(String), `subdivision2_code` LowCardinality(String), `city_geoname_id` UInt32, `utm_content` String, `utm_term` String, `transferred_from` String, `entry_meta.key` Array(String), `entry_meta.value` Array(String) ) ENGINE = CollapsingMergeTree(sign) PARTITION BY toYYYYMM(start) PRIMARY KEY (site_id, toDate(start), user_id, session_id) ORDER BY (site_id, toDate(start), user_id, session_id) SAMPLE BY user_id SETTINGS index_granularity = 8192 Execute? [Y/n]: create-sessions-v2 Done! ------------------------------------------------------------------------------ Running create-tmp-events-v2 -> Query: CREATE TABLE IF NOT EXISTS tmp_events_v2 AS events_v2 ENGINE = MergeTree() ORDER BY (site_id, toDate(timestamp), name, user_id, timestamp) PRIMARY KEY (site_id, toDate(timestamp), name, user_id) PARTITION BY toYYYYMM(timestamp) SETTINGS index_granularity = 8192 Execute? [Y/n]: y create-tmp-events-v2 Done! ------------------------------------------------------------------------------ Running create-tmp-sessions-v2 -> Query: CREATE TABLE IF NOT EXISTS tmp_sessions_v2 AS sessions_v2 ENGINE = CollapsingMergeTree(sign) PARTITION BY toYYYYMM(start) PRIMARY KEY (site_id, toDate(start), user_id, session_id) ORDER BY (site_id, toDate(start), user_id, session_id) SETTINGS index_granularity = 8192 Execute? [Y/n]: y create-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ Running create-domains-lookup -> Query: CREATE TABLE IF NOT EXISTS domains_lookup(site_id UInt64, domain String) ENGINE Memory; Execute? [Y/n]: y create-domains-lookup Done! ------------------------------------------------------------------------------ Populate domains-lookup with postgres sites [Y/n]: y Start migration? (starting from partition: 202107) [Y/n]: y start.. 2024-11-06 03:04:35.707406Z Run partition: 202107? [Y/n]: y insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202107 took 2 seconds Run partition: 202108? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202108 took 4 seconds Run partition: 202109? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202109 took 0 seconds Run partition: 202110? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202110 took 1 seconds Run partition: 202111? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202111 took 0 seconds Run partition: 202112? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202112 took 0 seconds Run partition: 202201? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202201 took 0 seconds Run partition: 202202? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202202 took 0 seconds Run partition: 202203? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202203 took 0 seconds Run partition: 202204? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202204 took 0 seconds Run partition: 202205? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202205 took 1 seconds Run partition: 202206? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202206 took 0 seconds Run partition: 202207? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202207 took 0 seconds Run partition: 202208? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202208 took 9 seconds Run partition: 202209? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202209 took 0 seconds Run partition: 202210? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202210 took 0 seconds Run partition: 202211? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202211 took 0 seconds Run partition: 202212? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202212 took 0 seconds Run partition: 202301? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202301 took 0 seconds Run partition: 202302? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202302 took 0 seconds Run partition: 202303? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202303 took 0 seconds Run partition: 202304? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202304 took 0 seconds Run partition: 202305? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202305 took 0 seconds Run partition: 202306? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202306 took 0 seconds Run partition: 202307? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202307 took 0 seconds Run partition: 202308? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202308 took 0 seconds Run partition: 202311? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202311 took 1 seconds Run partition: 202312? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202312 took 1 seconds Run partition: 202401? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202401 took 0 seconds Run partition: 202402? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202402 took 1 seconds Run partition: 202403? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202403 took 0 seconds Run partition: 202404? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202404 took 1 seconds Run partition: 202405? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202405 took 0 seconds Run partition: 202406? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202406 took 1 seconds Run partition: 202407? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202407 took 0 seconds Run partition: 202408? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202408 took 1 seconds Run partition: 202409? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202409 took 1 seconds Run partition: 202410? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202410 took 1 seconds Run partition: 202411? [Y/n]: insert-into-tmp-events-v2 Done! ------------------------------------------------------------------------------ attach-tmp-events-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-events-v2 Done! ------------------------------------------------------------------------------ insert-into-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ attach-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ truncate-tmp-sessions-v2 Done! ------------------------------------------------------------------------------ 202411 took 0 seconds end.. 2024-11-06 03:05:24.328671Z ```

Environment

- OS: NixOS 24.05
- Browser: Firefox
- Browser Version: 131.0.2

ruslandoga commented 3 weeks ago

👋 @nh2

Thank you for the report!

Seems like something went wrong either during "native stats start date" migration or during "stats start date" assignment. The latter one could produce a wrong date if somehow native stats start date was null and ClickHouse ~~didn't have any~~ had some recent data in events_v2.

Would you be able to check what these values are set to in your PostgreSQL database?

$ cd hosting # or wherever you clone https://github.com/plausible/community-edition (previously called https://github.com/plausible/hosting)
$ docker compose exec plausible_db psql -U postgres -h localhost -d plausible_db

select * from sites where domain = 'yourdomain.com';

Ir would also be useful to know min/max dates from events_v2 table in your ClickHouse database

$ docker compose exec plausible_events_db clickhouse client -d plausible_events_db

select min(timestamp), max(timestamp) from events_v2 where site_id = `... the id from sites table in PostgreSQL`;

nh2 commented 3 weeks ago

Thanks for the quick reply!

select * from sites where domain = 'yourdomain.com';

It shows

stats_start_date = 2024-11-06
native_stats_start_at = 2021-07-14 21:03:24
inserted_at = 2021-07-14 21:03:24
updated_at = 2024-11-05 23:48:22

select min(timestamp), max(timestamp) from events_v2 where site_id = ... the id from sites table in PostgreSQL;

:) select min(timestamp), max(timestamp) from events_v2 where site_id = 4;

SELECT
    min(timestamp),
    max(timestamp)
FROM events_v2
WHERE site_id = 4

Query id: 5795b377-2716-4fd9-b28b-1b5274704520

   ┌──────min(timestamp)─┬──────max(timestamp)─┐
1. │ 2021-07-14 21:02:05 │ 2024-11-06 05:14:29 │
   └─────────────────────┴─────────────────────┘

ruslandoga commented 3 weeks ago

So it seems like stats_start_date was set wrong for some reason. I'll look into it later this week but a quick workaround would be to set it to 2021-07-14

$ docker compose exec plausible_db psql -U postgres -h localhost -d plausible_db

UPDATE sites WHERE id = 4 SET stats_start_date = '2021-07-14';

nh2 commented 3 weeks ago

Small SQL syntax ordering issue, should be:

UPDATE sites SET stats_start_date = '2021-07-14' WHERE id = 4;

That fixed it, thank you very much @ruslandoga!

I'll be glad to assist your debugging once you get to it.

ruslandoga commented 3 weeks ago

I checked https://github.com/NixOS/nixpkgs/pull/253687#issuecomment-2458716721 and according to your screenshot it seems like the dashboard has been accessed with some events in events_v2 so it's likely the reason why stats_start_date was set to 2024-11-06. Even though it is a bug I don't think we will be handling this edge case since v2.0.0 was released quite some time ago and I would think most people have upgraded by now.

nh2 commented 3 weeks ago

@ruslandoga Fine by me.

However, I think it might save some people some hours if you update the text in https://github.com/plausible/analytics/releases/tag/v2.0.0 to add a sentence like

Note: If you access the Plausible UI before doing the migration, past dates will not be shown; in that case, perform the steps described in #4779.

ruslandoga commented 3 weeks ago

Thank you for the suggestion! I've updated the release notes and the OP in the discussion for that release.

nh2 commented 3 weeks ago

@ruslandoga Thanks! Also thanks for the extremely quick support, it made a long night a bit shorter!

plausible / analytics