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

[oszfer]

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

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]

👋 @oszfer

Thank you for the report! It should be fixed with #4162

[ruslandoga]

👋 @oszfer

v2.1.1 has been released with the fix.

[Showfom]

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]

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

analytics/lib/plausible/data_migration/site_imports.ex

Lines 150 to 155 in b096057

	query =
	Enum.reduce(schemas, max_date_query(first_schema, site_id, import_ids), fn schema, query ->
	from(s in subquery(union_all(query, ^max_date_query(schema, site_id, import_ids))))
	end)
	dates = ClickhouseRepo.all(from(q in query, select: q.max_date), log: false)

[ruslandoga]

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]

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]

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

[ruslandoga]

👋 @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]

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.