No stats new stats since upgrading from 2.10-RC to 2.1.1

[that-guy-iain]

Everything appears to be working except the stats aren't appearing.

I go to the website and I see the javascript is being loaded, I can see that the payload for analytics is sent and receives an "ok" response. But the stat for my visit isn't there nor is the live visitor count updated. The visitor count total is 0.

[ruslandoga]

👋 @that-guy-iain

Would you be able to share docker compose logs and if there is nothing interesting there, some more information on your setup (do you use the default docker compose file, any extra configuration, any non-UTC TZ env vars?) and the website (is it a new one and awaiting pageviews or did it already have pageviews prior to the upgrade and they disappeared?)

[that-guy-iain]

plausible_events_db-1 | 2024.06.26 23:21:42.375690 [ 341 ] {b20d12df-e4cd-4952-a897-9911fa717b62} executeQuery: Code: 16. DB::Exception: No such column revenue_source_amount in table plausible_events_db.events_v2 (b4f9913d-b163-4d8f-b907-0a8276426e88). (NO_SUCH_COLUMN_IN_TABLE) (version 23.3.7.5 (official build)) (from 172.22.0.6:45560) (in query: INSERT INTO events_v2 (name, site_id, hostname, pathname, user_id, session_id, timestamp, meta.key, meta.value, revenue_source_amount, revenue_source_currency, revenue_reporting_amount, revenue_reporting_currency, referrer, referrer_source, utm_medium, utm_source, utm_campaign, utm_content, utm_term, country_code, subdivision1_code, subdivision2_code, city_geoname_id, screen_size, operating_system, operating_system_version, browser, browser_version) FORMAT RowBinaryWithNamesAndTypes ), Stack trace (when copying this message, always include the lines below):

I'm seeing that in my docker logs. So I assume there is a migration I need to run.

[that-guy-iain]

@ruslandoga I've tried rolling back to v2.1.0 and v2.0. v2.1.0 has the same issue and v2.0 is completely broken.

What migration do I need to run?

[ruslandoga]

#4139 (reply in thread) might help.

If it doesn't please post the results from these queries: #4139 (comment)

And also show create table sessions_v2 and show create table events_v2.

[that-guy-iain]

2024.06.28 08:48:43.912815 [ 48 ] {a0158c35-feca-494b-8896-8d59f125ba03} DynamicQueryHandler: Code: 15. DB::Exception: Cannot add column import_id: column with this name already exists. (DUPLICATE_COLUMN), Stack trace (when copying this message, always include the lines below):

Which appears to be in a migration after the one I need.

1d254634a40c :) select * from schema_migrations;

SELECT *
FROM schema_migrations

Query id: f2d9e860-6b28-497b-8943-ea0de5fc6941

┌────────version─┬─────────inserted_at─┐
│ 20200915070607 │ 2023-06-09 11:58:58 │
│ 20200918075025 │ 2023-06-09 11:58:58 │
│ 20201020083739 │ 2023-06-09 11:58:58 │
│ 20201106125234 │ 2023-06-09 11:58:58 │
│ 20210323130440 │ 2023-06-09 11:58:58 │
│ 20210712214034 │ 2023-06-09 11:58:58 │
│ 20211017093035 │ 2023-06-09 11:58:58 │
│ 20211112130238 │ 2023-06-09 11:58:59 │
│ 20220310104931 │ 2023-06-09 11:58:59 │
│ 20220404123000 │ 2023-06-09 11:58:59 │
│ 20220421161259 │ 2023-06-09 11:58:59 │
│ 20220422075510 │ 2023-06-09 11:58:59 │
│ 20230124140348 │ 2024-04-04 06:44:41 │
│ 20230210140348 │ 2024-04-04 06:44:41 │
│ 20230214114402 │ 2024-04-04 06:44:41 │
│ 20230320094327 │ 2024-04-04 06:44:41 │
│ 20230417104025 │ 2024-04-04 06:44:41 │
│ 20230509124919 │ 2024-04-04 06:44:41 │
│ 20231017073642 │ 2024-06-28 08:48:43 │
└────────────────┴─────────────────────┘

19 rows in set. Elapsed: 0.003 sec.

1d254634a40c :)

SHOW CREATE TABLE events_v2

Query id: 08965969-ee54-4087-9cfb-7e8eaf09e3e0

┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE plausible_events_db.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 CODEC(ZSTD(3)),
    `referrer_source` String CODEC(ZSTD(3)),
    `country_code` FixedString(2),
    `screen_size` LowCardinality(String),
    `operating_system` LowCardinality(String),
    `browser` LowCardinality(String),
    `utm_medium` String CODEC(ZSTD(3)),
    `utm_source` String CODEC(ZSTD(3)),
    `utm_campaign` String CODEC(ZSTD(3)),
    `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 CODEC(ZSTD(3)),
    `utm_term` String CODEC(ZSTD(3)),
    `city` UInt32 ALIAS city_geoname_id,
    `country` LowCardinality(FixedString(2)) ALIAS country_code,
    `device` LowCardinality(String) ALIAS screen_size,
    `os` LowCardinality(String) ALIAS operating_system,
    `os_version` LowCardinality(String) ALIAS operating_system_version,
    `region` LowCardinality(String) ALIAS subdivision1_code,
    `screen` LowCardinality(String) ALIAS screen_size,
    `source` String ALIAS referrer_source
)
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 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.003 sec.

1d254634a40c :)

and

│ CREATE TABLE plausible_events_db.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),
    `exit_page_hostname` String CODEC(ZSTD(3)),
    `city` UInt32 ALIAS city_geoname_id,
    `country` LowCardinality(FixedString(2)) ALIAS country_code,
    `device` LowCardinality(String) ALIAS screen_size,
    `entry_page_hostname` String ALIAS hostname,
    `os` LowCardinality(String) ALIAS operating_system,
    `os_version` LowCardinality(String) ALIAS operating_system_version,
    `region` LowCardinality(String) ALIAS subdivision1_code,
    `screen` LowCardinality(String) ALIAS screen_size,
    `source` String ALIAS referrer_source
)
ENGINE = VersionedCollapsingMergeTree(sign, events)
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 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.003 sec.