v2.0 to v2.1.0 Migration error: Column with this name already exists

[Dmoulart]

Hi,
I tried to migrate from v2.0 to v2.1.0 and I'm stuck with a bug when the plausible container is starting.

Starting dependencies..
Starting repos..
Running migrations for Elixir.Plausible.Repo
Running migrations for Elixir.Plausible.IngestRepo
warning: code block contains unused literal "Migration adds a ALIAS columns needed to keep DB schema and api\nproperty naming in sync to reduce overhead in code.\n" (remove the literal or assign it to _ to avoid warnings)
└─ lib/plausible-0.0.1/priv/ingest_repo/migrations/20240502115822_alias_api_prop_names.exs: Plausible.IngestRepo.Migrations.AliasApiPropNames (module)

    warning: variable "column_name" is unused (if the variable is not meant to be used, prefix it with an underscore)
    │
 56 │     for {alias_name, column_name} <- @sessions_prop_names do
    │                      ~
    │
    └─ lib/plausible-0.0.1/priv/ingest_repo/migrations/20240502115822_alias_api_prop_names.exs:56:22: Plausible.IngestRepo.Migrations.AliasApiPropNames.down/0

    warning: variable "column_name" is unused (if the variable is not meant to be used, prefix it with an underscore)
    │
 64 │     for {alias_name, column_name} <- @events_prop_names do
    │                      ~
    │
    └─ lib/plausible-0.0.1/priv/ingest_repo/migrations/20240502115822_alias_api_prop_names.exs:64:22: Plausible.IngestRepo.Migrations.AliasApiPropNames.down/0

** (Ch.Error) Code: 15. DB::Exception: Cannot add column `utm_medium`: column with this name already exists. (DUPLICATE_COLUMN) (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
    (elixir 1.16.0) lib/enum.ex:1700: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:1161: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.11.1) lib/ecto/migration/runner.ex:348: Ecto.Migration.Runner.log_and_execute_ddl/3
    (elixir 1.16.0) lib/enum.ex:1700: Enum."-map/2-lists^map/1-1-"/2
    (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

This "utm_medium" column is already present in both events_v2 and sessions_v2 tables.
Is there a way to bypass this ? Or did I miss something else ? thanks

[ruslandoga]

👋 @Dmoulart

There are several quick fixes possible but it might not solve everything as the root cause might be more complicated than just a failed migration.

The first fix I can think of is to insert the failing migration version into schema_versions table in ClickHouse and that would make it skip it since it would think it has already run it.

Another possible fix is to drop that column if it doesn’t have any data.

The real fix though would be to get to the root cause of this and find put why this column already exists in your tables :)

[thibault-resalocal]

Hello @ruslandoga,

Thanks for your answer, we do have some data in this column.
So we tried to renammed this column in the events_v2 table, but we kept having the same error.

If I have correctly understand we can try to insert this migration id : 20240502115822 to skip it.

Here are the contents of the table: schema_migrations, in case someone see something missing.
`clickhouse-0.clickhouse.default.svc.cluster.local :) select * from schema_migrations

SELECT *
FROM schema_migrations

Query id: 53e61f89-71fb-499c-8a59-14fadcf8d5c0

┌────────version─┬─────────inserted_at─┐
│ 20200915070607 │ 2021-12-08 14:19:14 │
│ 20200918075025 │ 2021-12-08 14:19:14 │
│ 20201020083739 │ 2021-12-08 14:19:14 │
│ 20201106125234 │ 2021-12-08 14:19:14 │
│ 20210323130440 │ 2021-12-08 14:19:14 │
│ 20210712214034 │ 2023-03-26 23:08:32 │
│ 20211017093035 │ 2023-03-26 23:08:32 │
│ 20211112130238 │ 2023-03-26 23:08:32 │
│ 20220310104931 │ 2023-03-26 23:08:32 │
│ 20220404123000 │ 2023-03-26 23:08:32 │
│ 20220421161259 │ 2023-03-26 23:08:33 │
│ 20220422075510 │ 2023-03-26 23:08:33 │
│ 20230124140348 │ 2023-08-03 11:03:36 │
│ 20230210140348 │ 2023-08-03 11:03:36 │
│ 20230214114402 │ 2023-08-03 11:03:36 │
│ 20230320094327 │ 2023-08-03 11:03:36 │
│ 20230509124919 │ 2023-08-03 11:03:36 │
└────────────────┴─────────────────────┘

17 rows in set. Elapsed: 0.008 sec.`

We have started to use custom props with the Plausible version 2.0.0 even (if we weren’t able to see them in the web version) to collect usage data. Do you think it can be the reason why the migration is failling ?

Thanks in advance for your help.

[ruslandoga]

I don't see utm_medium columns in https://github.com/plausible/analytics/blob/master/priv/ingest_repo/migrations/20240502115822_alias_api_prop_names.exs so it's unlikely to be the failing migration.

[ruslandoga]

Judging by clickhouse-0.clickhouse.default.svc.cluster.local you might be running a ClickHouse cluster. That might also explain the error if the events or sessions tables are replicated but schema_migrations is not. In that scenario Plausible might attempt to run migrations that have already been run before. The fix might be to make schema_migrations replicated. The fact that utm_medium columns are only defined in very old migrations (way way pre-v2) seems to support it: https://github.com/search?q=repo%3Aplausible%2Fanalytics+path%3A%2F%5Epriv%5C%2Fingest_repo%5C%2Fmigrations%5C%2F%2F+utm_medium&type=code

[Dmoulart]

We finally managed to solve the problem !
The migration table was not synchronized with the state of the database for an unknown reason.
To solve the problem, we inserted, one by one, the migrations that had already been made, but not saved, into the table 'schema_migrations'.

Thank you very much @ruslandoga ! You put us on the trail and we would not have made it without your replies.

[thibault-resalocal]

@ruslandoga thanks a lot ;)