Triplex with transactions always returns error on migrate

Narven

Hi,

For the last 2 days, I’ve be trying to implement Triplex to add multi-tenancy to my application, and I use transactions a lot, usually with Ecto.Multi.

From the documentation it looks like with triplex I need to use:

    Triplex.create_schema(slug, Project.Repo, fn(tenant, repo) ->
      Repo.transaction(fn ->
        mig = Triplex.migrate(tenant, repo)
        IO.inspect(mig, label: "MIGRATE")

        // Add any ecto operation here...

        tenant
      end)
    end)

My problem is that Triplex.migrate(tenant, repo) is always, always returning:

 {:error,
 "ERROR 42P01 (undefined_table) relation \"prj_c24.schema_migrations\" does not exist\n\n    query: INSERT INTO \"prj_c24\".\"schema_migrations\" (\"version\",\"inserted_at\") VALUES ($1,$2)"}

For me this is an extreme confusing error. It tells nothing about what is happening, (at least for me).

My only migration is this:

defmodule Project.Repo.Migrations.CreateUsersTables do
  use Ecto.Migration

  def change do
    # execute "CREATE EXTENSION IF NOT EXISTS citext", ""

    create table(:users, primary_key: false) do
      add(:id, :uuid, primary_key: true, null: false)
      # add(:email, :citext, null: false)
      add(:hashed_password, :string, null: false)
      add(:first_name, :string, null: false)
      add(:last_name, :string, null: false)
      add(:is_enabled, :boolean, null: false, default: true)
      add(:dob, :date)
      add(:photo, :string, default: "/images/empty.png")
      add(:confirmed_at, :naive_datetime)
      timestamps()
    end

    # create unique_index(:users, [:email])
  end
end

Any help on that error? thanks

PS: The prefix is being created in the database. But migrations are not running.

❯ elixir --version
Erlang/OTP 24 [erts-12.1.5] [source] [64-bit] [smp:16:16] [ds:16:16:10] [async-threads:1] [jit] [dtrace]
Elixir 1.13.0 (compiled with Erlang/OTP 24)

{:triplex, "~> 1.3.0-rc.1"}

11 comments

#ecto #triplex

1 1656 11

2024-05-20 18:42:15 UTC

First 10 of 11 Posts

al2o3cr

The error is complaining because the schema_migrations table isn’t present in the tenant schema.

That doesn’t make a ton of sense - Triplex.migrate calls Ecto.Migrator.run which ensures that table exists

Is there anything useful in the Elixir or DB logs?

Post #1

Narven

Yeah.. I was just looking at that. it looks like for some reason that “schema_migrations” is not being created on the tenants side.

Could it be because i have both “migrations” and “tenant_migrations” inside of “repo” folder ?

I was trying to see if there was a way to prevent the rollback. so that I could see the last state.

Post #2

Narven

From the logs:

[debug] QUERY OK db=3.1ms queue=2.0ms idle=1959.2ms
CREATE SCHEMA "vsk_bn" []
[debug] QUERY OK db=1.2ms idle=967.2ms
begin []
TENANT: "bn"
REPO: Project.Repo
[info] == Running 20211010180955 Project.Repo.Migrations.CreateUsersTables.change/0 forward
[info] execute "CREATE EXTENSION IF NOT EXISTS citext"
[info] extension "citext" already exists, skipping
[info] create table vsk_bn.users
[info] create index vsk_bn.users_username_index
[info] == Migrated 20211010180955 in 0.0s
MIGRATE: {:error,
 "ERROR 42P01 (undefined_table) relation \"vsk_bn.schema_migrations\" does not exist\n\n    query: INSERT INTO \"vsk_bn\".\"schema_migrations\" (\"version\",\"inserted_at\") VALUES ($1,$2)"}

Post #3

al2o3cr

Getting to the == Running 2021etcetcetc bit suggests that the code believes it has created the schema_migrations table.

One thought: this pattern looks like the one mentioned in the Triplex docs, but I wonder if that actually works. Maybe the schema_migrations table is being inserted in a separate transaction somehow; that would produce the observed behavior:

start transaction with Repo.transaction in your posted code
Triplex.migrate calls into Ecto.Migrator, which starts a transaction in another process (the migration lock holder?)
other transaction commits successfully, creating the table - but that isn’t visible to the first transaction that was already opened
Ecto.Migrator tries to read from schema_migrations and fails

Post #4

Narven

One thing I dont understad is that if I go and run iex -S mix phx.server

and on iex i run:

iex(4)> Triplex.create("we")
[debug] QUERY OK db=1.6ms decode=1.5ms queue=1.4ms idle=928.7ms
CREATE SCHEMA "vsk_we" []
[info] == Running 20211010180955 Project.Repo.Migrations.CreateUsersTables.change/0 forward
[info] execute "CREATE EXTENSION IF NOT EXISTS citext"
[info] create table vsk_we.users
[info] create index vsk_we.users_username_index
[info] == Migrated 20211010180955 in 0.0s
{:ok, "we"}
iex(5)>

It works flawless.

If I run Triplex.create("foo") directly on the code… Does do nothing.

Because for some weird reason according to the docs we have to run create_schema. But on IEX just running create works perfectly.

Post #5

Narven

If I do something simple like:

    slug = "foo"
    mig = Triplex.create(slug, Project.Repo)
    IO.inspect(mig, label: "MIG")

    Repo.insert(%User{
      first_name: attrs["first_name"],
      last_name: attrs["last_name"],
      username: attrs["username"],
      email: attrs["email"],
      hashed_password: Bcrypt.hash_pwd_salt(password)
    }, prefix: Triplex.to_prefix(slug))

it works. I think that the issue is only when they are running inside a migration.

Post #6

al2o3cr

Yeah, that’s what makes me suspicious about the Repo.transaction in the middle - Triplex.create is defined in a way that’s equivalent to:

Triplex.create_schema(slug, Project.Repo, fn(tenant, repo) ->
  Triplex.migrate(tenant, repo)
end)

Re: your last message about running inside a migration - if this code is inside a migration’s def up, then there’s already a transaction wrapped around the whole thing. You may need to disable that with @disable_ddl_transaction true.

Post #7

Narven

It looks like adding @disable_ddl_transaction true to the migration and doing like this:

Triplex.create_schema(slug, Project.Repo, fn(tenant, repo) ->
      repo.transaction(fn ->

        mig = Triplex.migrate(tenant, repo)

        IO.inspect(mig, label: "~> Versions")
        repo.insert!(%User{
          first_name: attrs["first_name"],
          last_name: attrs["last_name"],
          email: attrs["email"],
          username: Timex.now |> to_string,
          hashed_password: Bcrypt.hash_pwd_salt(password)
        }, prefix: Triplex.to_prefix(slug))

        IO.inspect(tenant, label: "~> TENANT")
        tenant
      end)
    end)

returns:

[debug] QUERY OK db=1.8ms queue=1.4ms idle=1556.8ms
CREATE SCHEMA "vsk_e3" []
[debug] QUERY OK db=0.9ms idle=1560.1ms
begin []
[info] == Running 20211010180955 Project.Repo.Migrations.CreateUsersTables.change/0 forward
[info] execute "CREATE EXTENSION IF NOT EXISTS citext"
[info] extension "citext" already exists, skipping
[info] create table vsk_e3.users
[info] create index vsk_e3.users_username_index
[info] == Migrated 20211010180955 in 0.0s
~> Versions: {:error,
 "ERROR 42P01 (undefined_table) relation \"vsk_e3.schema_migrations\" does not exist\n\n    query: INSERT INTO \"vsk_e3\".\"schema_migrations\" (\"version\",\"inserted_at\") VALUES ($1,$2)"}
[debug] QUERY OK db=1.6ms
INSERT INTO "vsk_e3"."users" ("email","first_name","hashed_password","is_enabled","last_name","username","id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9) ["e3@gmail.com", "e3", "$2b$12$c2/QHSJVJz3r9m6IzqZJI.ZDJWQnTYIIZrYW1EjJdUtcXBLCpUFUm", true, "e3", "2021-12-08 22:09:58.266144Z", <<92, 43, 112, 180, 231, 134, 79, 54, 175, 253, 215, 250, 153, 244, 217, 123>>, ~N[2021-12-08 22:09:58], ~N[2021-12-08 22:09:58]]
~> TENANT: "e3"
[debug] QUERY OK db=2.6ms
commit []

Still shows the error, which technally is an error, but in this case I dont think it should show has an error.

~> {:error,
 "ERROR 42P01 (undefined_table) relation \"vsk_e3.schema_migrations\" does not exist\n\n    query: INSERT INTO \"vsk_e3\".\"schema_migrations\" (\"version\",\"inserted_at\") VALUES ($1,$2)"}

I think this is more like… I cannot find schema_migrations… so I’m going to create it. Or maybe not.

But for now it looks like is working… I will add tomorrow all the extra removed migrations and add the rest of the transactions to see how it goes.

Thanks @al2o3cr

Post #8

Narven

After more testing… this lib does not work at all.
This working if there is only one migration, after that for some reason it throws the error

 {:error,
 "ERROR 42P01 (undefined_table) relation \"vsk_p1.schema_migrations\" does not exist\n\n    query: INSERT INTO \"vsk_p1\".\"schema_migrations\" (\"version\",\"inserted_at\") VALUES ($1,$2)"}

and no other migration get’s run:

i’ve currently have 2 migrations and this is what happens:

[info] == Running 20211011183604 Project.Repo.Migrations.CreateSchools.change/0 forward
[info] create table vsk_p1.schools
[info] create index vsk_p1.schools_slug_index
[info] == Migrated 20211011183604 in 0.0s
§ TENANT: "p1"
§ REPO: Project.Repo
§ EXCEPTION: %Postgrex.Error{
  connection_id: 925,
  message: nil,
  postgres: %{
    code: :undefined_table,
    file: "parse_relation.c",
    line: "1139",
    message: "relation \"vsk_p1.schema_migrations\" does not exist",
    pg_code: "42P01",
    position: "13",
    routine: "parserOpenTable",
    severity: "ERROR",
    unknown: "ERROR"
  },
  query: "INSERT INTO \"vsk_p1\".\"schema_migrations\" (\"version\",\"inserted_at\") VALUES ($1,$2)"
}
~> MIGRATIONS: {:error,
 "ERROR 42P01 (undefined_table) relation \"vsk_p1.schema_migrations\" does not exist\n\n    query: INSERT INTO \"vsk_p1\".\"schema_migrations\" (\"version\",\"inserted_at\") VALUES ($1,$2)"}

PS: added some extra debug lines to the actual library §

Post #9

riebeekn

I think the inner transaction is the issue. As mentionned in the Triplex docs the way migrations run was changed in Ecto 3. Since that change I haven’t ever been able to get Triplex.migrate to run within a transaction. Considering create_schema will do a schema drop on any {:error, some_err_message} result… not using a transaction seems an alright way of going about things.

So I would try something along the lines of:

    Triplex.create_schema(slug, Repo, fn tenant, repo ->
      {:ok, _} = Triplex.migrate(tenant, repo)
  
      Repo.transaction(fn ->
        # do other inserts etc. here... such as...
        with {:ok, user} <- insert_some_default_user_on_schema_creation(),
             {:ok, _something_else} <- do_something_else() do
          {:ok, user}
        else
          {:error, error} ->
            Repo.rollback(error)
        end
      end)
    end)

Post #10

Load more posts (1 remaining)