[YSQL] Elixir ecto migrations workaround without SAVEPOINTs

[ddorian]

Jira Link: DB-1832
ecto package in Elixir is used often for migrations in YSQL but it uses SAVEPOINT which aren't yet implemented #1125.

Consider implementing a workaround patch/custom-driver that doesn't use SAVEPOINT.

Reported in slack.

[davidspek]

I believe SAVEPOINT has been implemented, but I'm still seeing issues with Elixir and Ecto.

[ddorian]

@davidspek what version are you using and can you paste the full error message(s)?

[davidspek]

I'm using the latest version of YugabyteDB (2.15.2.0) and ecto 3.4.3. Initially when running the migration it was running fine until this error occured:

13:49:57.604 [info] making create index for table "alertmanager_incidents" nonconcurrent
13:49:57.604 [info] == Migrated 20211003023016 in 13.0s
13:49:57.642 [error] Could not update schema migrations. This error usually happens due to the following:

  * The database does not exist
  * The "schema_migrations" table, which Ecto uses for managing
    migrations, was defined by another library
  * There is a deadlock while migrating (such as using concurrent
    indexes with a migration_lock)

To fix the first issue, run "mix ecto.create".

To address the second, you can run "mix ecto.drop" followed by
"mix ecto.create". Alternatively you may configure Ecto to use
another table for managing migrations:

    config :console, Console.Repo,
      migration_source: "some_other_table_for_schema_migrations"

The full error report is shown below.

13:49:57.651 [info] Postgrex.Protocol (#PID<0.176.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.380.0> exited
▸  Evaluation failed with: ERROR 40001 (serialization_failure) Query error: The catalog snapshot used for this transaction has been invalidated.
▸      (ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:593: Ecto.Adapters.SQL.raise_sql_call_error/1
▸      (ecto 3.4.6) lib/ecto/repo/schema.ex:661: Ecto.Repo.Schema.apply/4
▸      (ecto 3.4.6) lib/ecto/repo/schema.ex:263: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
▸      (ecto_sql 3.4.5) lib/ecto/migrator.ex:641: Ecto.Migrator.verbose_schema_migration/3
▸      (ecto_sql 3.4.5) lib/ecto/migrator.ex:293: Ecto.Migrator.async_migrate_maybe_in_transaction/6
▸      (ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:875: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
▸      (db_connection 2.3.1) lib/db_connection.ex:1444: DBConnection.run_transaction/4
▸      (ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:702: Ecto.Adapters.SQL.lock_for_migrations/5

After running DROP SCHEMA public CASCADE; to try and do a test run of the migration I get the error:

Running migrations for console
14:12:08.155 [error] Could not create schema migrations table. This error usually happens due to the following:

  * The database does not exist
  * The "schema_migrations" table, which Ecto uses for managing
    migrations, was defined by another library
  * There is a deadlock while migrating (such as using concurrent
    indexes with a migration_lock)

To fix the first issue, run "mix ecto.create".

To address the second, you can run "mix ecto.drop" followed by
"mix ecto.create". Alternatively you may configure Ecto to use
another table for managing migrations:

    config :console, Console.Repo,
      migration_source: "some_other_table_for_schema_migrations"

The full error report is shown below.

▸  Evaluation failed with: ERROR 3F000 (invalid_schema_name) no schema has been selected to create in
▸      (ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:593: Ecto.Adapters.SQL.raise_sql_call_error/1
▸      (elixir 1.11.4) lib/enum.ex:1411: Enum."-map/2-lists^map/1-0-"/2
▸      (ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:686: Ecto.Adapters.SQL.execute_ddl/4
▸      (ecto_sql 3.4.5) lib/ecto/migrator.ex:641: Ecto.Migrator.verbose_schema_migration/3
▸      (ecto_sql 3.4.5) lib/ecto/migrator.ex:484: Ecto.Migrator.lock_for_migrations/4
▸      (ecto_sql 3.4.5) lib/ecto/migrator.ex:406: Ecto.Migrator.run/4
▸      (elixir 1.11.4) lib/enum.ex:798: Enum."-each/2-lists^foreach/1-0-"/2
▸      (piazza_core 0.3.1) lib/ecto/release_tasks.ex:23: Piazza.Ecto.ReleaseTasks.seed/1

[davidspek]

After dropping and recreating the database and adding migration_lock: false to our ecto config for our app the following error happens when running the database migration:

15:57:10.529 [info] create index upgrade_policies_name_index
15:57:10.763 [info] making create index for table "upgrade_policies" nonconcurrent
15:57:10.763 [info] == Migrated 20210811183452 in 0.5s
15:57:10.789 [info] == Running 20210907032057 Console.Repo.Migrations.BuildMessageLength.change/0 forward
15:57:10.790 [info] alter table builds
15:57:10.853 [info] == Migrated 20210907032057 in 0.0s
15:57:10.869 [info] == Running 20211003023016 Console.Repo.Migrations.Alertmanager.change/0 forward
15:57:10.869 [info] create table alertmanager_incidents
15:57:15.276 [info] create index alertmanager_incidents_fingerprint_index
▸  Evaluation failed with: ERROR 40001 (serialization_failure) Query error: The catalog snapshot used for this transaction has been invalidated: MISMATCHED_SCHEMA
▸      (ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:593: Ecto.Adapters.SQL.raise_sql_call_error/1
▸      (elixir 1.11.4) lib/enum.ex:1411: Enum."-map/2-lists^map/1-0-"/2
▸      (ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:686: Ecto.Adapters.SQL.execute_ddl/4
▸      (ecto_sql 3.4.5) lib/ecto/migration/runner.ex:343: Ecto.Migration.Runner.log_and_execute_ddl/3
▸      (ecto_sql 3.4.5) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
▸      (elixir 1.11.4) lib/enum.ex:2193: Enum."-reduce/3-lists^foldl/2-0-"/3
▸      (ecto_sql 3.4.5) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
▸      (stdlib 3.14.1) timer.erl:166: :timer.tc/1