Missing columns country, source after upgrade 1.5 to 3

[mart-e]

Hello,

I have a self-hosted instance in 1.5.1 that I am trying to upgrade in 3.0.1 but, after the upgrade, I miss some columns like country or source in clickhouse.

Steps:

• checkout 3.0.1 source code
• docker compose up
• run docker compose exec plausible bin/plausible rpc Plausible.DataMigration.NumericIDs.run, answer 'y' to all

Got the following migration settings:                                                                                                                                                                                                         
                                                                                                                                                                                                                                              
  - max_threads: 16                                                                                                                                                                                                                           
  - table_settings:                                                                                                                                                                                                                           
  - db url: http://plausible:xxx@yyy:8123/plausible_events_db                                                                                                                                             
  - cluster?: false                                                                                                                                                                                                                           
  - partitions to do: ["202210", "202211", "202212", "202301", "202302", "202303", "202304", "202305",                                                                                                                                        
 "202306", "202307", "202308", "202309", "202310", "202311", "202312", "202401",                                                                                                                                                              
 "202402", "202403", "202404", "202405", "202406", "202407", "202408", "202409",                                                                                                                                                              
 "202410", "202411", "202412", "202501", "202502", "202503", "202504", "202505",                                                                                                                                                              
 "202506", "202507", "202508", "202509", "202510"]                                                                                                                                                                                            
  - start from: 202210                                                                                                                                                                                                                        
  - stop at:                                                                                                                                                                                                                                  

After the migration, when opening the stats on my webiste, I get errors

plausible-1  | Server: 127.0.0.1:8000 (http)
plausible-1  | Request: GET /api/stats/xxx/sources/?period=28d&date=2025-10-09&filters=%5B%5D&with_imported=true&limit=9
plausible-1  | ** (exit) an exception was raised:
plausible-1  |     ** (Ch.Error) Code: 47. DB::Exception: Identifier 's0.source' cannot be resolved from table with name s0. In scope SELECT toUInt64(round(uniq(s0.user_id) * any(_sample_factor))) AS visitors, if(empty(s0.source), 'Direct / None', s0.source) AS dim0 FROM sessions_v2 AS s0 WHERE (s0.site_id = _CAST(154, 'Int64')) AND (s0.start >= _CAST(1756929600, 'DateTime')) AND (s0.timestamp >= _CAST(1757534400, 'DateTime')) AND (s0.start <= _CAST(1759953599, 'DateTime')) GROUP BY dim0 ORDER BY visitors DESC, dim0 ASC LIMIT _CAST(0, 'Int64'), _CAST(9, 'Int64'). (UNKNOWN_IDENTIFIER) (version 25.8.2.29 (official build))
plausible-1  | 
plausible-1  |         (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1096: Ecto.Adapters.SQL.raise_sql_call_error/1
plausible-1  |         (ecto_ch 0.6.0) lib/ecto/adapters/clickhouse.ex:323: Ecto.Adapters.ClickHouse.execute/5
plausible-1  |         (ecto 3.12.5) lib/ecto/repo/queryable.ex:232: Ecto.Repo.Queryable.execute/4
plausible-1  |         (ecto 3.12.5) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
plausible-1  |         (plausible 0.0.1) lib/plausible/stats/query_runner.ex:48: Plausible.Stats.QueryRunner.execute_main_query/1
plausible-1  |         (plausible 0.0.1) lib/plausible/stats/query_runner.ex:40: Plausible.Stats.QueryRunner.run/2
plausible-1  |         (plausible 0.0.1) lib/plausible/stats/breakdown.ex:40: Plausible.Stats.Breakdown.breakdown/5
plausible-1  |         (plausible 0.0.1) lib/plausible_web/controllers/api/stats_controller.ex:481: PlausibleWeb.Api.StatsController.sources/2
plausible-1  | 14:19:30.968 [error] #PID<0.5516.0> running PlausibleWeb.Endpoint (connection #PID<0.5515.0>, stream id 1) terminated
plausible-1  | Server: 127.0.0.1:8000 (http)
plausible-1  | Request: GET /api/stats/xxx/countries/?period=28d&date=2025-10-09&filters=%5B%5D&with_imported=true&limit=300
plausible-1  | ** (exit) an exception was raised:
plausible-1  |     ** (Ch.Error) Code: 47. DB::Exception: Identifier 's0.country' cannot be resolved from table with name s0. In scope SELECT toUInt64(round(uniq(s0.user_id) * any(_sample_factor))) AS visitors, s0.country AS dim0, (SELECT toUInt64(round(uniq(se0.user_id) * any(_sample_factor))) AS result FROM events_v2 AS se0 WHERE (se0.site_id = _CAST(154, 'Int64')) AND (se0.timestamp >= _CAST(1757534400, 'DateTime')) AND (se0.timestamp <= _CAST(1759953599, 'DateTime')) AND (NOT (se0.country IN ('\0\0', 'ZZ')))) AS total_visitors, if(total_visitors > 0, round((visitors / total_visitors) * 100, 1), NULL) AS percentage FROM sessions_v2 AS s0 WHERE (s0.site_id = _CAST(154, 'Int64')) AND (s0.start >= _CAST(1756929600, 'DateTime')) AND (s0.timestamp >= _CAST(1757534400, 'DateTime')) AND (s0.start <= _CAST(1759953599, 'DateTime')) AND (NOT (s0.country IN ('\0\0', 'ZZ'))) GROUP BY dim0 ORDER BY visitors DESC, dim0 ASC LIMIT _CAST(0, 'Int64'), _CAST(300, 'Int64'). (UNKNOWN_IDENTIFIER) (version 25.8.2.29 (official build))
plausible-1  | 
plausible-1  |         (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1096: Ecto.Adapters.SQL.raise_sql_call_error/1
plausible-1  |         (ecto_ch 0.6.0) lib/ecto/adapters/clickhouse.ex:323: Ecto.Adapters.ClickHouse.execute/5
plausible-1  |         (ecto 3.12.5) lib/ecto/repo/queryable.ex:232: Ecto.Repo.Queryable.execute/4
plausible-1  |         (ecto 3.12.5) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
plausible-1  |         (plausible 0.0.1) lib/plausible/stats/query_runner.ex:48: Plausible.Stats.QueryRunner.execute_main_query/1
plausible-1  |         (plausible 0.0.1) lib/plausible/stats/query_runner.ex:40: Plausible.Stats.QueryRunner.run/2
plausible-1  |         (plausible 0.0.1) lib/plausible/stats/breakdown.ex:40: Plausible.Stats.Breakdown.breakdown/5
plausible-1  |         (plausible 0.0.1) lib/plausible_web/controllers/api/stats_controller.ex:983: PlausibleWeb.Api.StatsController.countries/2

show table sessions_v2

:) show table sessions_v2

SHOW CREATE TABLE sessions_v2

Query id: 6f5b2d79-d9c9-4b1c-8f7c-0b03d946f27f

   ┌─statement─────────────────────────────────────────────────┐
1. │ 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)                     ↴│
   │↳)                                                        ↴│
   │↳ENGINE = CollapsingMergeTree(sign)                       ↴│
   │↳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.006 sec. 

Do I need to run a specific additional steps? Or upgrade from 1.5 to 2.0 first before jumping to 3.0?

My understanding is that, when running the migration NumericIDs, we drop the table events_v2 (first question) and the one that is recreated won’t get the new columns (as we already have the latest schema_migrations when we first launched the server in 3.0.1).
I tried answering no (the default) for those drop queries but I then get an error

Run partition: 202204? [Y/n]: 
** (MatchError) no match of right hand side value: {:error, %Ch.Error{code: 16, message: "Code: 16. DB::Exception: No such column transferred_from in table plausible_events_db.tmp_events_v2 (870a8926-fe37-41b4-bf03-8e986ccc2f12). (NO_SUCH_COLUMN_IN_TABLE) (version 25.8.2.29 (official build))\n"}}
    (plausible 0.0.1) lib/plausible/data_migration/numeric_ids.ex:142: anonymous fn/1 in Plausible.DataMigration.NumericIDs.run/1
    (plausible 0.0.1) lib/plausible/data_migration/numeric_ids.ex:141: anonymous fn/3 in Plausible.DataMigration.NumericIDs.run/1
    (elixir 1.18.3) lib/enum.ex:2546: Enum."-reduce/3-lists^foldl/2-0-"/3
    (plausible 0.0.1) lib/plausible/data_migration/numeric_ids.ex:138: anonymous fn/2 in Plausible.DataMigration.NumericIDs.run/1
    (stdlib 6.2.2) erl_eval.erl:919: :erl_eval.do_apply/7

Thank you

[mart-e]

Oh, I think I have figured it out! What I think worked was :

• checkout in 3.0
• modify docker-compose to remove the && /entrypoint.sh db migrate part
• start the server (ignore errors)
• run docker compose exec plausible bin/plausible rpc Plausible.DataMigration.NumericIDs.run
• stop the server
• modify docker-compose to add the && /entrypoint.sh db migrate part
• start the server

I am not sure if this is the expected steps? Or am I supposed to do 1.5 -> 2 first?