search

plausible / analytics

Simple, open source, lightweight (< 1 KB) and privacy-friendly web analytics alternative to Google Analytics.
https://plausible.io
GNU Affero General Public License v3.0
19.93k stars 1.06k forks source link

v2.1.0 - incompatible Clickhouse server version (clickhouse/clickhouse-server:24.3.3.102-alpine)? #4167

Closed oszfer closed 4 months ago

oszfer commented 4 months ago

Past Issues Searched

Issue is a Bug Report

Using official Plausible Cloud hosting or self-hosting?

Self-hosting

Describe the bug

Plausible CE 2.1.0 is self-hosted docker environment doesn't start with the "standard" docker-compose.yml from plausible github repo. Is seems to be a DB creation error, incompatible clickhouse-server version (clickhouse/clickhouse-server:24.3.3.102-alpine). Plausible container log: ** (CaseClauseError) no case clause matching: {:error, %Ch.Error{code: 48, message: "Code: 48. DB::Exception: RENAME EXCHANGE is not supported. (NOT_IMPLEMENTED) (version 24.3.3.102 (official build))\n"}} (plausible 0.0.1) lib/plausible/data_migration/versioned_sessions.ex:52: Plausible.DataMigration.VersionedSessions.run_exchange/1 (plausible 0.0.1) lib/plausible/data_migration/versioned_sessions.ex:44: Plausible.DataMigration.VersionedSessions.run/1 (ecto_sql 3.11.1) lib/ecto/migration/runner.ex:318: Ecto.Migration.Runner.perform_operation/3 (stdlib 5.2) timer.erl:270: :timer.tc/2 (ecto_sql 3.11.1) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/8 (ecto_sql 3.11.1) lib/ecto/migrator.ex:365: Ecto.Migrator.attempt/8 (ecto_sql 3.11.1) lib/ecto/migrator.ex:282: anonymous fn/5 in Ecto.Migrator.do_up/5

Expected behavior

ghcr.io/plausible/community-edition:v2.1.0 starts without any error.

Screenshots

No response

Environment

- OS: CentOS 7.6 
- Docker: docker-ce-26.1.2
ruslandoga commented 4 months ago

👋 @oszfer

Thank you for the report! It should be fixed with https://github.com/plausible/analytics/pull/4162

ruslandoga commented 4 months ago

👋 @oszfer

v2.1.1 has been released with the fix.

Showfom commented 4 months ago

Seems we still have the problem when upgrading from Plausible 2.0.0 to 2.1.x with clickhouse/clickhouse-server:24.3.3.102-alpine

Loading plausible..
Starting dependencies..
Starting repos..
create Plausible.Repo database if it doesn't exist
create Plausible.IngestRepo database if it doesn't exist
Creation of Db successful!
Loading plausible..
Starting dependencies..
Starting repos..
Running migrations for Elixir.Plausible.Repo
Backfilling legacy site import across 4 sites (DRY RUN: false)...
Creating legacy site import entry for site ID 25 (1/4)
Creating legacy site import entry for site ID 24 (2/4)
Creating legacy site import entry for site ID 21 (3/4)
Creating legacy site import entry for site ID 26 (4/4)
Finished backfilling sites.
Adjusting end dates of 4 site imports (DRY RUN: false)...
Adjusting end date for site import 117 (1/4) (site ID 25, start date: 2021-11-19, end date: 2022-04-03)
** (Ch.Error) Code: 47. DB::Exception: Identifier 'sssssssssi0.import_id' cannot be resolved from table with name sssssssssi0. In scope SELECT max(sssssssssi0.date) AS max_date FROM imported_visitors AS sssssssssi0 WHERE (sssssssssi0.site_id = _CAST(25, 'Int64')) AND (sssssssssi0.import_id IN (_CAST(0, 'Int64'), _CAST(117, 'Int64'))). Maybe you meant: ['sssssssssi0._part_uuid']. (UNKNOWN_IDENTIFIER) (version 24.3.3.102 (official build))

    (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:1054: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_ch 0.3.5) lib/ecto/adapters/clickhouse.ex:319: Ecto.Adapters.ClickHouse.execute/5
    (ecto 3.11.2) lib/ecto/repo/queryable.ex:232: Ecto.Repo.Queryable.execute/4
    (ecto 3.11.2) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    (plausible 0.0.1) lib/plausible/data_migration/site_imports.ex:155: Plausible.DataMigration.SiteImports.imported_stats_end_date/2
    (plausible 0.0.1) lib/plausible/data_migration/site_imports.ex:93: anonymous fn/4 in Plausible.DataMigration.SiteImports.adjust_site_import_end_dates/2
    (elixir 1.16.0) lib/enum.ex:2528: Enum."-reduce/3-lists^foldl/2-0-"/3
ruslandoga commented 4 months ago

That's a different issue. And a very strange one.

We use Ecto as a query builder and the way it comes up with identifiers would mean that sssssssssi0.import_id comes from a very deeply nested subquery, nine sub-SELECTs. The relevant migration is now https://github.com/plausible/analytics/blob/v2.1.1/priv/repo/migrations/20240528115149_migrate_site_imports.exs which uses https://github.com/plausible/analytics/blob/v2.1.1/lib/plausible/data_migration/site_imports.ex

This is where the migration fails for you https://github.com/plausible/analytics/blob/b096057eef28f25f8223cb625dfd3303ffce521a/lib/plausible/data_migration/site_imports.ex#L150-L155

ruslandoga commented 4 months ago

Ah, actually the query seems correct, but import_id seems to be missing from your tables. Would you be able to show create table for some of the imported_* tables?

console

$ cd hosting # or wherever you cloned this repo
$ docker compose exec plausible_events_db clickhouse client --database plausible_events_db
:) show create table imported_locations;
:) show create table imported_browsers;
:) show create table imported_pages;
Showfom commented 3 months ago

I have successfully upgraded to v2.1.0 by using the following method

my_versioned_sessions.ex

Here is the result for show create table:

# 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:
 * Linux transparent hugepages are set to "always". Check /sys/kernel/mm/transparent_hugepage/enabled

17e1e1511672 :) show create table imported_locations;

SHOW CREATE TABLE imported_locations

Query id: 4ee3197f-a044-4849-adf1-a54be4a935ac

   ┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ CREATE TABLE plausible_events_db.imported_locations
(
    `site_id` UInt64,
    `date` Date,
    `country` String,
    `region` String,
    `city` UInt64,
    `visitors` UInt64,
    `visits` UInt64,
    `visit_duration` UInt64,
    `bounces` UInt32,
    `import_id` UInt64,
    `pageviews` UInt64
)
ENGINE = MergeTree
ORDER BY (site_id, date, country, region, city)
SETTINGS index_granularity = 8192, replicated_deduplication_window = 0 │
   └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.006 sec. 

17e1e1511672 :) show create table imported_browsers;

SHOW CREATE TABLE imported_browsers

Query id: 4c57e5b6-547f-47b9-b7bb-0c8d07d80a67

   ┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ CREATE TABLE plausible_events_db.imported_browsers
(
    `site_id` UInt64,
    `date` Date,
    `browser` String,
    `visitors` UInt64,
    `visits` UInt64,
    `visit_duration` UInt64,
    `bounces` UInt32,
    `import_id` UInt64,
    `pageviews` UInt64,
    `browser_version` String
)
ENGINE = MergeTree
ORDER BY (site_id, date, browser)
SETTINGS index_granularity = 8192, replicated_deduplication_window = 0 │
   └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.004 sec. 

17e1e1511672 :) show create table imported_pages;

SHOW CREATE TABLE imported_pages

Query id: 199a8605-f69b-4317-9289-01fa2e3d5013

   ┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ CREATE TABLE plausible_events_db.imported_pages
(
    `site_id` UInt64,
    `date` Date,
    `hostname` String,
    `page` String,
    `visitors` UInt64,
    `pageviews` UInt64,
    `exits` UInt64,
    `time_on_page` UInt64,
    `import_id` UInt64,
    `visits` UInt64,
    `active_visitors` UInt64
)
ENGINE = MergeTree
ORDER BY (site_id, date, hostname, page)
SETTINGS index_granularity = 8192, replicated_deduplication_window = 0 │
   └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.003 sec. 

17e1e1511672 :)
thomasphilibert commented 3 months ago

Hello same problem here, for 2.1.1. That is strange, i don't find import_id in any table.

ruslandoga commented 3 months ago

👋 @thomasphilibert

Please make sure to upgrade to v2.1.0 first and only then -- to v.2.1.1

Actually, I might have been too fast to jump to conclusions. Could you please share more information on your setup, what steps you took for your upgrade, what version you were upgrading from?

thomasphilibert commented 3 months ago

Hello @ruslandoga,

You are right, i have tried to upgrade from 2.0 to 2.1.1 directly. I have upgraded from 2.0 to 2.1 then 2.1.1 and it's ok now.