Elixir Ecto rollback migrations with indexes

Question

I am currently having a bunch of migrations that create a few tables with references and a few unique indexes. This seems to be working fine when I want to migrate the tables with mix ecto.migrate but if I want to rollback mix ecto.rollback coughs up the below error. Do I need to change my migrations to add something to handle the indexes during the rollback?

Migration step:

defmodule App.Repo.Migrations.CreateTokens do
  use Ecto.Migration

  def change do
    create table(:tokens) do
      add :token, :string
      add :user_id, references(:users, on_delete: :delete_all, on_update: :update_all)

      timestamps()
    end

    create index(:tokens, [:user_id])
    create unique_index(:tokens, [:token], unique: true, name: :unique_tokens_index)
  end
end

Error Log:

[info] == Running App.Repo.Migrations.CreateTokens.change/0 backward
[info] drop index unique_tokens_index
[info] drop index tokens_user_id_index
** (Mariaex.Error) (1553): Cannot drop index 'tokens_user_id_index': needed in a foreign key constraint
    (ecto) lib/ecto/adapters/sql.ex:200: Ecto.Adapters.SQL.query!/5
    (ecto) lib/ecto/adapters/mysql.ex:118: anonymous fn/4 in Ecto.Adapters.MySQL.execute_ddl/3
    (elixir) lib/enum.ex:1899: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/adapters/mysql.ex:118: Ecto.Adapters.MySQL.execute_ddl/3
    (ecto) lib/ecto/migration/runner.ex:104: anonymous fn/2 in Ecto.Migration.Runner.flush/0
    (elixir) lib/enum.ex:1899: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/migration/runner.ex:102: Ecto.Migration.Runner.flush/0
    (stdlib) timer.erl:181: :timer.tc/2
    (ecto) lib/ecto/migration/runner.ex:26: Ecto.Migration.Runner.run/6
    (ecto) lib/ecto/migrator.ex:128: Ecto.Migrator.attempt/6
    (ecto) lib/ecto/migrator.ex:106: anonymous fn/4 in Ecto.Migrator.do_down/4
    (ecto) lib/ecto/adapters/sql.ex:576: anonymous fn/3 in Ecto.Adapters.SQL.do_transaction/3
    (db_connection) lib/db_connection.ex:1283: DBConnection.transaction_run/4
    (db_connection) lib/db_connection.ex:1207: DBConnection.run_begin/3
    (db_connection) lib/db_connection.ex:798: DBConnection.transaction/3
    (ecto) lib/ecto/migrator.ex:262: anonymous fn/4 in Ecto.Migrator.migrate/4
    (elixir) lib/enum.ex:1294: Enum."-map/2-lists^map/1-0-"/2
    (ecto) lib/mix/tasks/ecto.rollback.ex:79: anonymous fn/4 in Mix.Tasks.Ecto.Rollback.run/2
    (elixir) lib/enum.ex:737: Enum."-each/2-lists^foreach/1-0-"/2
    (elixir) lib/enum.ex:737: Enum.each/2

It seems that you have another table that is referencing the tokens table, using the field token of tokens table as a foreign key. If that is correct, then based on this error I would think your regular migration would not even work. Could you do an ecto.reset for it to drop the entire db and re-migrate the whole thing? I expect it will error out. — Kevin Johnson

Felipe Skinner Felipe Skinner · Accepted Answer · 2018-05-25T11:52:08

ecto.rollback normally works even if you have indexes and such. By reading the error message it seems that the issue is ecto is trying rolling back a table that has a key used by other table as foreign key. That might be an issue with your migration order perhaps.

For example if you have this migration

defmodule App.Repo.Migrations.CreateTokens do
  use Ecto.Migration

  def change do
    create table(:tokens) do
      add :token, :string
      add :user_id, references(:users, on_delete: :delete_all, on_update: :update_all)

      timestamps()
    end

    create index(:tokens, [:user_id])
    create unique_index(:tokens, [:token], unique: true, name: :unique_tokens_index)
  end
end

Your CreateUsers migration should have been created first (this means being first with a lower timestamp on the file name) since the CreateTokens depends on CreateUsers to establish its foreign_keys.

This error is probably due to you generating the migration and trying to alter it by yourself afterwards. Keep in mind that migrations are time sensitive/order sensitive so you can't create a Token table which references users if you haven't created the User table as well

You could try to run mix ecto.reset but since it's getting an error when trying to rollback, it will most likely give you an error when you run the mix ecto.migrate

The solution would be fixing the migrations orders or even splitting into more migrations... Such as: CreateUsers, CreateTokens, CreateForeignKeys

Elixir Ecto rollback migrations with indexes

1 Answers