Lost data after plausible migration (from v.1.5.1 to v.2.0.0)

[Mark24Slides]

Past Issues Searched

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

Issue is a Bug Report

• 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

Lost data (partially) after migration from v.1.5.2 to v.2.0.0.
Used instruction given in releases (2.0.0) page.

Hosting everything with Kubernetes, with official docker image: plausible/analytics:v2.0.0

My steps:

• Updated plausible and clickhouse images (clickhouse uses persistent volume, 10 GBs size, only 4.7Gbs occupied)
• Used bin/plausible rpc Plausible.DataMigration.NumericIDs.run command and started migration (drop v2 table, drop-domains-lookup, create v2 tables, create create-domains-lookup, start migration (starting from partition: 202302 ... 202308 .... end, no errors)).
• With browser, opened and logged in plausible portal, selected random site and noticed that data from March-April and June-July months is missing (February, May and current day, 01 August, are present there).

Used clickhouse-client cli to check db, noticed that new tables were created but not all events and sessions were copied:

~ Select Count(*) from events;

SELECT Count(*)
FROM events

Query id: ceea6192-8888-4aa4-877b-0e85ea3caf94

┌──count()─┐
│ 17250656 │
└────────┘

1 row in set. Elapsed: 0.006 sec.


~ Select Count(*) from events_v2;

SELECT Count(*)
FROM events_v2

Query id: d956210d-caf9-4910-9cd5-1596dbedd6fb

┌─count()─┐
│ 5727585 │
└───────┘

1 row in set. Elapsed: 0.035 sec.

~ Select Count(*) from sessions;

SELECT Count(*)
FROM sessions

Query id: a64f4d47-806b-4209-9d77-a4bba8283711

┌─count()─┐
│ 5592494 │
└───────┘

1 row in set. Elapsed: 0.005 sec.


~ Select Count(*) from sessions_v2;

SELECT Count(*)
FROM sessions_v2

Query id: 0d4aa78b-dbec-421f-b132-adb7ccd21c7d

┌─count()─┐
│ 3471330 │
└───────┘

Expected behavior

Expected to use plausible v2, but lost some data (on the new version) after migration from v.1.5.2 to v2.0.0.

Screenshots

Database structure:

Random website data:

Environment

- Deployment: Kubernetes/Docker
- Plausible: plausible/analytics:v2.0.0
- Clickhouse: clickhouse/clickhouse-server:22.6-alpine
- Browser: Brave

[ruslandoga]

👋 @Mark24Slides

It'd be helpful if you could identify what exact data has been lost, i.e. what events are present in events and are not present in events_v2, also maybe check docker compose logs plausible_events_db and system.mutations table in ClickHouse.

With browser, opened and logged in plausible portal, selected random site and noticed that data from March-April and June-July months is missing (February, May and current day, 01 August, are present there).

That sounds a bit like missed partitions. Would you mind rerunning the data migration and posting the logs here?

[Mark24Slides]

@ruslandoga, thank you a lot, during own instigation and getting outputs for you I found the issue.

During bin/plausible rpc Plausible.DataMigration.NumericIDs.run run, had:

[ 493 ] {e9b3729e-0ed9-4b89-a9f1-863faceeac76} <Error> DynamicQueryHandler: Code: 241. DB::Exception: Memory limit (total) exceeded: would use 1.80 GiB (attempt to allocate chunk of 4456448 bytes), maximum: 1.80 GiB. OvercommitTracker decision: Query was selected to stop by OvercommitTracker.. (MEMORY_LIMIT_EXCEEDED), Stack trace (when copying this message, always include the lines below):

DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int, bool) @ 0xb8a4c1a in /usr/bin/clickhouse
DB::Exception::Exception<char const*, char const*, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, long&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::basic_string_view<char, std::__1::char_traits<char> > >(int, fmt::v8::basic_format_string<char, fmt::v8::type_identity<char const*>::type, fmt::v8::type_identity<char const*>::type, fmt::v8::type_identity<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > >::type, fmt::v8::type_identity<long&>::type, fmt::v8::type_identity<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > >::type, fmt::v8::type_identity<std::__1::basic_string_view<char, std::__1::char_traits<char> > >::type>, char const*&&, char const*&&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >&&, long&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >&&, std::__1::basic_string_view<char, std::__1::char_traits<char> >&&) @ 0xb8bc7b7 in /usr/bin/clickhouse
2. MemoryTracker::allocImpl(long, bool, MemoryTracker*) @ 0xb8bc1ae in /usr/bin/clickhouse
3. MemoryTracker::allocImpl(long, bool, MemoryTracker*) @ 0xb8bbc5a in /usr/bin/clickhouse
4. MemoryTracker::allocImpl(long, bool, MemoryTracker*) @ 0xb8bbc5a in /usr/bin/clickhouse
5. MemoryTracker::allocImpl(long, bool, MemoryTracker*) @ 0xb8bbc5a in /usr/bin/clickhouse
6. Allocator<false, false>::realloc(void*, unsigned long, unsigned long, unsigned long) @ 0xb6ac8a3 in /usr/bin/clickhouse

plausible-events-db container had 2GBs memory (ram usage) limitation (for pod), volume (persistent) used 4.7 GBs (attached to plausible-events-db), after I temporary scaled memory (ram usage) limit to 5Gbs - migration went well.

~ :) Select Count(*) From events;

SELECT Count(*)
FROM events

Query id: d88bc5eb-c561-4c38-ae61-44097c162910

┌──count()─┐
│ 17250656 │
└──────────┘

1 row in set. Elapsed: 0.004 sec.

~ :) Select Count(*) From events_v2;

SELECT Count(*)
FROM events_v2

Query id: c4475bf7-859f-4aad-8a93-93cb652a3c5c

┌──count()─┐
│ 17251374 │
└──────────┘

1 row in set. Elapsed: 0.028 sec.

It will be great to add something like:
Be sure that you have enough amount of free ram or high memory limit for plausible_events_db container, or you will get "DB::Exception: Memory limit (total) exceeded" error by clickhouse database, and missing data in migrated tables.
after
You need enough free disk space available for x2 of the current plausible_events_db's event-data volume size. You can use something like docker system df -v | grep hosting_event-data to check how much space the current volume is occupying.
in the releases page notes.

[ruslandoga]

Thank you for the suggestion! And I'm glad you resolved your issue :)