Rolling back phx.gen.schema migration with references constraint

Phoenix Forum Questions / Help
, ,
1

I’m getting started with Phoenix and Ecto. From the Ecto.Migration docs, this section sounds very promising:

Having to write both up/0 and down/0 functions for every migration is tedious and error prone. For this reason, Ecto allows you to defined a change/0 callback with all of the code you want to execute when migrating and Ecto will automatically figure out the down/0 for you.

However, when I create a relationship between two resources with the phoenix generators:

mix phx.gen.html Context Foo foos name
mix phx.gen.html Context Bar bars foo_id:references:foos

The generated migration for bars:

  def change do
    create table(:bars, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :foo_id, references(:foos, on_delete: :nothing, type: :binary_id)

      timestamps()
    end

    create index(:bars, [:foo_id])
  end

Uses change by default and creates an index, nice.

mix ecto.migrate
19:43:20.788 [info]  create table bars
19:43:20.819 [info]  create index bars_foo_id_index

So far, so good. However, trying to roll back:

mix ecto.rollback
19:44:14.631 [info]  == Running 20210114104254 MyApp.Repo.Migrations.CreateBars.change/0 backward
19:44:14.634 [info]  drop index bars_foo_id_index
** (MyXQL.Error) (1553) (ER_DROP_INDEX_FK) Cannot drop index 'bars_foo_id_index': needed in a foreign key constraint

If I manually remove the constraint, the rollback works.

Does this mean that when using generators with references, I need to re-write the generated migration to use up/down if I want these operations to be reversible? Is there a reason the generator doesn’t use up/down for this by default, which would make it reversible? Or are constraints generally treated as irreversible?

2

This seems like a bug based on the differences of mysql to postgres. mysql automatically creates an index for foreign keys and it might be the case that both references(:foos, on_delete: :nothing, type: :binary_id) and create index(:bars, [:foo_id]) do therefore create the same index. On rollback create index(:bars, [:foo_id]) then tries to remove it without knowing anything about it being part of a foreign key.

3

Thanks for the quick reply. Here’s the SQL:

CREATE TABLE `bars` (
  `id` binary(16) NOT NULL,
  `foo_id` binary(16) DEFAULT NULL,
  `inserted_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `bars_foo_id_index` (`foo_id`),
  CONSTRAINT `bars_foo_id_fkey` FOREIGN KEY (`foo_id`) REFERENCES `foos` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The CONSTRAINT there is separate to the index - I’m guessing that’s created by the references in the migration and is unrelated to the create index part.

Let me try it with Postgres and see what happens (although for this project I need to use MySQL).

4

MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created.

https://dev.mysql.com/doc/refman/8.0/en/constraint-foreign-key.html

contrary to postgres.

Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns.

2 Likes
5

I see, so trying to remove the index in MySql would cause the constraint to exist without an index, which isn’t allowed. I think I understand now. Is this forshadowing a world of pain trying to use Ecto with MySQL? :sunglasses:

I wonder if I can work around by re-writing the migration to use constraint after creating the index, instead of references before the index. I’ll try it.

6

I mean removing the create index might just be enough. Letting mysql autocreate the index and the rollback of create table should be drop table, which should work.

2 Likes
7

That did it, thanks!

The index was created automatically by MySQL as you expected. And as there was no seperate drop index command to execute, running it backwards to drop the table worked too. :ok_hand:

8

This might be worth opening an issue for phoenix however. Maybe it has (or can keep) knowledge about the used db and adjust the template.

9

Yeah, it looks like currently it treats all associations as requiring an index and doesn’t have context of which adapter is being used. I’ll look into raising an issue in the weekend. Thanks again for your help!