Unable to migrate to CE v2.1 ((CaseClauseError) no case clause matching)

[DrMaxNix]

Hello everyone! I just tried to update my plausible from v2.0 to CE v2.1 by following the official migration guide.
However, after starting the docker containers, the plausible container repeatedly fails to start.

Log excerpt:

plausible-1            | Loading plausible..
plausible-1            | Starting dependencies..
plausible-1            | Starting repos..
plausible-1            | create Plausible.Repo database if it doesn't exist
plausible-1            | create Plausible.IngestRepo database if it doesn't exist
...
plausible-1            | Creation of Db successful!
plausible-1            | Loading plausible..
plausible-1            | Starting dependencies..
plausible-1            | Starting repos..
plausible-1            | Running migrations for Elixir.Plausible.Repo
plausible-1            | Running migrations for Elixir.Plausible.IngestRepo
...
plausible-1            | ** (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-1            |     (plausible 0.0.1) lib/plausible/data_migration/versioned_sessions.ex:52: Plausible.DataMigration.VersionedSessions.run_exchange/1
plausible-1            |     (plausible 0.0.1) lib/plausible/data_migration/versioned_sessions.ex:44: Plausible.DataMigration.VersionedSessions.run/1
plausible-1            |     (ecto_sql 3.11.1) lib/ecto/migration/runner.ex:318: Ecto.Migration.Runner.perform_operation/3
plausible-1            |     (stdlib 5.2) timer.erl:270: :timer.tc/2
plausible-1            |     (ecto_sql 3.11.1) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/8
plausible-1            |     (ecto_sql 3.11.1) lib/ecto/migrator.ex:365: Ecto.Migrator.attempt/8
plausible-1            |     (ecto_sql 3.11.1) lib/ecto/migrator.ex:282: anonymous fn/5 in Ecto.Migrator.do_up/5
plausible-1 exited with code 1

The error seems to be the line with (CaseClauseError) no case clause matching which originates from this function:

analytics/lib/plausible/data_migration/versioned_sessions.ex

Lines 56 to 79 in dc7243f

	defp run_exchange(cluster?) do
	case run_sql("exchange-sessions-tables", cluster?: cluster?) do
	{:ok, _} ->
	nil
	# Docker containers don't seem to support EXCHANGE TABLE, hack around this with a non-atomic swap
	{:error, %Ch.Error{code: 1}} ->
	IO.puts("Exchanging sessions_v2 and sessions_v2_tmp_versioned non-atomically")
	{:ok, _} =
	run_sql("rename-table",
	from: "sessions_v2",
	to: "sessions_v2_backup",
	cluster?: cluster?
	)
	{:ok, _} =
	run_sql("rename-table",
	from: "sessions_v2_tmp_versioned",
	to: "sessions_v2",
	cluster?: cluster?
	)
	end
	end

[ruslandoga]

👋 @DrMaxNix

Thank you for the detailed report! Seems like we'll need to update that script: #4162

As a possible workaround you can mount a custom sessions_v2 migration script with that case clause fixed:

_{my_versioned_sessions.ex}

defmodule Plausible.IngestRepo.Migrations.SessionsV2VersionedCollapsingMergeTree do
  use Ecto.Migration
  
  defmodule FixedVersionedSessions do
  @moduledoc """
  !!!WARNING!!!: This script is used in migrations. Please take special care
  when altering it.

  Sessions CollapsingMergeTree -> VersionedCollapsingMergeTree migration,
  SQL files available at:

  priv/data_migrations/VersionedSessions/sql
  """
  use Plausible.DataMigration, dir: "VersionedSessions", repo: Plausible.IngestRepo

  @suffix_format "{YYYY}{0M}{0D}{h24}{m}{s}"
  @versioned_table_engines [
    "ReplicatedVersionedCollapsingMergeTree",
    "VersionedCollapsingMergeTree"
  ]

  def run(opts \\ []) do
    run_exchange? = Keyword.get(opts, :run_exchange?, true)

    unique_suffix = Timex.now() |> Timex.format!(@suffix_format)

    cluster? = Plausible.MigrationUtils.clustered_table?("sessions_v2")

    {:ok, %{rows: partitions}} = run_sql("list-partitions")
    partitions = Enum.map(partitions, fn [part] -> part end)

    {:ok, %{rows: [[current_table_engine, table_settings]]}} =
      run_sql("get-sessions-table-settings")

    if Enum.member?(@versioned_table_engines, current_table_engine) do
      IO.puts("sessions_v2 table is already versioned, no migration needed")
    else
      {:ok, _} = run_sql("drop-sessions-tmp-table", cluster?: cluster?)

      {:ok, _} =
        run_sql("create-sessions-tmp-table",
          cluster?: cluster?,
          table_settings: table_settings,
          unique_suffix: unique_suffix
        )

      for partition <- partitions do
        {:ok, _} = run_sql("attach-partition", partition: partition)
      end

      if run_exchange? do
        run_exchange(cluster?)
      end

      IO.puts("Migration done!")
    end
  end

  defp run_exchange(cluster?) do
    case run_sql("exchange-sessions-tables", cluster?: cluster?) do
      {:ok, _} ->
        nil

      # Docker containers don't seem to support EXCHANGE TABLE, hack around this with a non-atomic swap
      {:error, %Ch.Error{code: code}} when code in [1, 48] ->
        IO.puts("Exchanging sessions_v2 and sessions_v2_tmp_versioned non-atomically")

        {:ok, _} =
          run_sql("rename-table",
            from: "sessions_v2",
            to: "sessions_v2_backup",
            cluster?: cluster?
          )

        {:ok, _} =
          run_sql("rename-table",
            from: "sessions_v2_tmp_versioned",
            to: "sessions_v2",
            cluster?: cluster?
          )
    end
  end
end

  def up do
    FixedVersionedSessions.run(run_exchange?: true)

    # After this migration a `sessions_v2_tmp_versioned` backup table is left behind, to be cleaned up manually
  end

  def down do
    raise "Irreversible"
  end
end

_{docker-compose.yml}

plausible:
  image: ghcr.io/plausible/community-edition:v2.1.0
+ volumes:
+   - ./my_versioned_sessions.ex:/app/lib/plausible-0.0.1/priv/ingest_repo/20240222082911_sessions_v2_versioned_collapsing_merge_tree.exs

[ruslandoga]

Oops, you are right. The correct path is

/app/lib/plausible-0.0.1/priv/ingest_repo/migrations/20240222082911_sessions_v2_versioned_collapsing_merge_tree.exs

I missed migrations somehow when copying it.

[ruslandoga]

You can double check it by running like this before the volume mount:

$ docker compose exec plausible cat /app/lib/plausible-0.0.1/priv/ingest_repo/migrations/20240222082911_sessions_v2_versioned_collapsing_merge_tree.exs
defmodule Plausible.IngestRepo.Migrations.SessionsV2VersionedCollapsingMergeTree do
  use Ecto.Migration

  def up do
    Plausible.DataMigration.VersionedSessions.run(run_exchange?: true)

    # After this migration a `sessions_v2_tmp_versioned` backup table is left behind, to be cleaned up manually
  end

  def down do
    raise "Irreversible"
  end
end

[DrMaxNix]

With the correct path for the override file it works now!

plausible-1            | Exchanging sessions_v2 and sessions_v2_tmp_versioned non-atomically
plausible-1            |     rename-table Done!
plausible-1            | 
plausible-1            | ------------------------------------------------------------------------------
plausible-1            |     rename-table Done!
plausible-1            | 
plausible-1            | ------------------------------------------------------------------------------
plausible-1            | Migration done!
plausible-1            | Migrations successful!

[DrMaxNix]

So to sum it up in one answer comment:

create file my_versioned_sessions.ex:

defmodule Plausible.IngestRepo.Migrations.SessionsV2VersionedCollapsingMergeTree do
  use Ecto.Migration
  
  defmodule FixedVersionedSessions do
  @moduledoc """
  !!!WARNING!!!: This script is used in migrations. Please take special care
  when altering it.

  Sessions CollapsingMergeTree -> VersionedCollapsingMergeTree migration,
  SQL files available at:

  priv/data_migrations/VersionedSessions/sql
  """
  use Plausible.DataMigration, dir: "VersionedSessions", repo: Plausible.IngestRepo

  @suffix_format "{YYYY}{0M}{0D}{h24}{m}{s}"
  @versioned_table_engines [
    "ReplicatedVersionedCollapsingMergeTree",
    "VersionedCollapsingMergeTree"
  ]

  def run(opts \\ []) do
    run_exchange? = Keyword.get(opts, :run_exchange?, true)

    unique_suffix = Timex.now() |> Timex.format!(@suffix_format)

    cluster? = Plausible.MigrationUtils.clustered_table?("sessions_v2")

    {:ok, %{rows: partitions}} = run_sql("list-partitions")
    partitions = Enum.map(partitions, fn [part] -> part end)

    {:ok, %{rows: [[current_table_engine, table_settings]]}} =
      run_sql("get-sessions-table-settings")

    if Enum.member?(@versioned_table_engines, current_table_engine) do
      IO.puts("sessions_v2 table is already versioned, no migration needed")
    else
      {:ok, _} = run_sql("drop-sessions-tmp-table", cluster?: cluster?)

      {:ok, _} =
        run_sql("create-sessions-tmp-table",
          cluster?: cluster?,
          table_settings: table_settings,
          unique_suffix: unique_suffix
        )

      for partition <- partitions do
        {:ok, _} = run_sql("attach-partition", partition: partition)
      end

      if run_exchange? do
        run_exchange(cluster?)
      end

      IO.puts("Migration done!")
    end
  end

  defp run_exchange(cluster?) do
    case run_sql("exchange-sessions-tables", cluster?: cluster?) do
      {:ok, _} ->
        nil

      # Docker containers don't seem to support EXCHANGE TABLE, hack around this with a non-atomic swap
      {:error, %Ch.Error{code: code}} when code in [1, 48] ->
        IO.puts("Exchanging sessions_v2 and sessions_v2_tmp_versioned non-atomically")

        {:ok, _} =
          run_sql("rename-table",
            from: "sessions_v2",
            to: "sessions_v2_backup",
            cluster?: cluster?
          )

        {:ok, _} =
          run_sql("rename-table",
            from: "sessions_v2_tmp_versioned",
            to: "sessions_v2",
            cluster?: cluster?
          )
    end
  end
end

  def up do
    FixedVersionedSessions.run(run_exchange?: true)

    # After this migration a `sessions_v2_tmp_versioned` backup table is left behind, to be cleaned up manually
  end

  def down do
    raise "Irreversible"
  end
end

And add a volume to docker-compose.yml file:

volumes:
      - ./my_versioned_sessions.ex:/app/lib/plausible-0.0.1/priv/ingest_repo/migrations/20240222082911_sessions_v2_versioned_collapsing_merge_tree.exs:ro

This fix will become obsolete when this PR has been merged: #4162

[ruslandoga]

You can also try running migrations on an earlier ClickHouse version, like v23 and then switching back to v24 once done.

[ruslandoga]

v2.1.1 has been released with the case clause fix.