Plausible CE doesn't log pageviews. #4139
Replies: 9 comments 10 replies
-
|
👋 @byemc Would you be able to document the exact steps you took to update from v1.4 to v2.1? It would help me reproduce the issue. Also do you still have access to the logs from the period when you performed the upgrade? |
Beta Was this translation helpful? Give feedback.
-
|
I can try! My memory is not that great :/ I'll try to piece it together using my terminal history, sadly I don't have the logs. I did manage to get the columns added manually though, and it's working now. I changed the git origin ( Sorry if that didn't help at all 😔. |
Beta Was this translation helpful? Give feedback.
-
|
I've got the same "DB::Exception: No such column revenue_source_amount in table plausible_events_db.events_v2" lines in the logs after upgrading from 2.1.0-rc.1 to 2.1.0. |
Beta Was this translation helpful? Give feedback.
-
|
Could you please show the schema for the events_v2 table and list the executed migrations? $ cd hosting
$ docker compose exec plausible_events_db clickhouse client --database plausible_events_db:) show create table events_v2;
:) select * from schema_migrations; |
Beta Was this translation helpful? Give feedback.
-
|
:) select * from schema_migrations where version = 20230417104025;
-- ┌────────version─┬─────────inserted_at─┐
-- 1. │ 20230417104025 │ 2024-02-16 11:29:52 │
-- └────────────────┴─────────────────────┘ |
Beta Was this translation helpful? Give feedback.
-
bye@potato:~$ cd plausible/
bye@potato:~/plausible$ docker compose exec plausible_events_db clickhouse client --database plausible_events_db
ClickHouse client version 24.3.3.102 (official build).
Connecting to database plausible_events_db at localhost:9000 as user default.
Connected to ClickHouse server version 24.3.3.
Warnings:
* Delay accounting is not enabled, OSIOWaitMicroseconds will not be gathered. You can enable it using `echo 1 > /proc/sys/kernel/task_delayacct` or by using sysctl.
88e3ce94ced0 :) show create table events_v2;
SHOW CREATE TABLE events_v2
Query id: 759954e8-d32a-4f97-a9ea-1e3a10b0d144
┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ 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,
`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,
`revenue_reporting_amount` Nullable(Decimal(18, 3)),
`revenue_reporting_currency` FixedString(3),
`revenue_source_amount` Nullable(Decimal(18, 3)),
`revenue_source_currency` FixedString(3)
)
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.016 sec.
88e3ce94ced0 :) select * from schema_migrations;
SELECT *
FROM schema_migrations
Query id: e9e5c6b7-e3c8-43fb-a7f0-2449705bd3af
┌────────version─┬─────────inserted_at─┐
1. │ 20200915070607 │ 2023-06-08 19:36:42 │
2. │ 20200918075025 │ 2023-06-08 19:36:42 │
3. │ 20201020083739 │ 2023-06-08 19:36:42 │
4. │ 20201106125234 │ 2023-06-08 19:36:42 │
5. │ 20210323130440 │ 2023-06-08 19:36:42 │
6. │ 20210712214034 │ 2023-06-08 19:36:42 │
7. │ 20211017093035 │ 2023-06-08 19:36:42 │
8. │ 20211112130238 │ 2023-06-08 19:36:42 │
9. │ 20220310104931 │ 2023-06-08 19:36:43 │
10. │ 20220404123000 │ 2023-06-08 19:36:43 │
11. │ 20220421161259 │ 2023-06-08 19:36:43 │
12. │ 20220422075510 │ 2023-06-08 19:36:43 │
13. │ 20230124140348 │ 2024-05-22 12:45:46 │
14. │ 20230210140348 │ 2024-05-22 12:45:46 │
15. │ 20230214114402 │ 2024-05-22 12:45:46 │
16. │ 20230320094327 │ 2024-05-22 12:45:46 │
17. │ 20230509124919 │ 2024-05-22 12:45:46 │
18. │ 20230417104025 │ 2024-05-23 18:52:34 │
19. │ 20231017073642 │ 2024-05-23 18:52:34 │
20. │ 20240123142959 │ 2024-05-23 18:52:34 │
21. │ 20240209085338 │ 2024-05-23 18:52:34 │
22. │ 20240220123656 │ 2024-05-23 18:52:34 │
23. │ 20240222082911 │ 2024-05-23 18:52:34 │
24. │ 20240305085310 │ 2024-05-23 18:52:34 │
25. │ 20240326134840 │ 2024-05-23 18:52:34 │
26. │ 20240327085855 │ 2024-05-23 18:52:34 │
27. │ 20240419133926 │ 2024-05-23 18:52:34 │
28. │ 20240423094014 │ 2024-05-23 18:52:35 │
29. │ 20240502115822 │ 2024-05-23 18:52:35 │
└────────────────┴─────────────────────┘
29 rows in set. Elapsed: 0.021 sec.
88e3ce94ced0 :) |
Beta Was this translation helpful? Give feedback.
-
|
Beta Was this translation helpful? Give feedback.
-
|
It seems like you might have used Cloud ediition / master version instead of v2.0.0 to run migrations since 20230417104025 was run on 2023-06-15 05:33:20 and the latest release at the time didn't have that migration included. Maybe there was some conditional code at the time that didn't create the tables for some reason. I guess the fix would be to remove these rows from :) rename table schema_migrations to schema_migrations_backup;
:) create table schema_migrations as schema_migrations_backup;
:) insert into schema_migrations select * from schema_migrations_backup where version <= 20230509124919; |
Beta Was this translation helpful? Give feedback.
-
|
i did this and now im getting: |
Beta Was this translation helpful? Give feedback.
-
|
Fwiw, the migrations that do not depend on v2 tables were run already. So you'll also need to whitelist those migrations: insert into plausible.schema_migrations select * from plausible.schema_migrations_backup where version = 20240123142959
insert into plausible.schema_migrations select * from plausible.schema_migrations_backup where version = 20240326134840
insert into plausible.schema_migrations select * from plausible.schema_migrations_backup where version = 20240419133926 As of today, it seems to be the full list of migrations impacting non-2 tables that need to be kept. |
Beta Was this translation helpful? Give feedback.
-
|
Thanks @ruslandoga for the explanation! |
Beta Was this translation helpful? Give feedback.
-
|
I haven't tried it. |
Beta Was this translation helpful? Give feedback.
-
|
It worked for me, but I'm not getting location or device data returned in the panel from yet another missing column, which might not be related, I'm not sure. |
Beta Was this translation helpful? Give feedback.
-
|
It's related in that it's also from a "missed" migration. Please see #4139 (reply in thread) for a "complete" fix. |
Beta Was this translation helpful? Give feedback.
-
|
OK so I added the 3 missing columns and that did the trick 😄 |
Beta Was this translation helpful? Give feedback.
-
|
I still had a few issues database-wise and couldnt upgrade to v2.1.1. I solved it by renaming the database, letting Plausible recreate it, and then manually copying in the data from the old database into the new one. (which was some pain, because the column lists are slightly different, but I managed to get everything migrated over and it seems to be all working ok again! |
Beta Was this translation helpful? Give feedback.
-
|
I did the suggested fix of modifying the migration table, but that resulted in Plausible crashing. Rolling back to Plausible V2 works, but the setting page isn't working due to Postgres having a new schema. |
Beta Was this translation helpful? Give feedback.
-
|
i tried adding the missing tables manually like in #3246 (comment) on another instance, and it seems to have worked. |
Beta Was this translation helpful? Give feedback.
-
|
I rolled back the first instance, it seems that plausible v2.1.0 may override the new table at first, but then it seems to work and page views are getting registered again, however I'm not sure if everything is indeed now working correctly or not. it would be great if there is a way i can verify if i have all tables needed. |
Beta Was this translation helpful? Give feedback.
-
I recently upgraded from v1.4 to v2.1. I managed to migrate my data. However, no pageviews are logged. I downgraded to v2.0 and it was working again!
Looking in my docker logs, I found something like "No such column", alongside errors from Clickhouse saying the same thing.
Did I do something wrong? I found a similar discussion but I don't think the fix worked (I ran it directly in Clickhouse).
My docker-compose (the ports on the two databases were for debugging and are disabled):
Beta Was this translation helpful? Give feedback.
All reactions