Migrations with MySQL - (MyXQL.Error) (1005) (ER_CANT_CREATE_TABLE) Can't create table

Using a fresh install of Phoenix LiveView for the first time and onboarding is not going as expected.

Excerpt of commands on a new project with MySQL …

mix phx.new reepx --database mysql
mix phx.gen.auth Users User users

Run the migrations, build, it just works.

Now, add another table to it:

mix phx.gen.live Links Link links url:text

Generates

defmodule Reepx.Repo.Migrations.CreateLinks do
  use Ecto.Migration

  def change do
    create table(:links) do
      add :url, :text
      add :user_id, references(:users, type: :id, on_delete: :delete_all)

      timestamps(type: :utc_datetime)
    end

    create index(:links, [:user_id])
  end
end

Running the migration for links gives me errors:

09:18:34.824 [info] create table links
** (MyXQL.Error) (1005) (ER_CANT_CREATE_TABLE) Can't create table `reepx_dev`.`links` (errno: 150 "Foreign key constraint is incorrectly formed")

Generated query is:

CREATE TABLE `links` (`id` bigint unsigned not null auto_increment, `url` text, `user_id` integer, CONSTRAINT `links_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE, `inserted_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`)) ENGINE = INNODB

Obviosuly the automatically added field user_id in not generated as bigint unsigned to match the users.id.

What am I doing wrong?

Using MariaDB 11.7.2 with

{:phoenix, "~> 1.8.0"},
{:phoenix_ecto, "~> 4.5"},
{:ecto_sql, "~> 3.13"},
{:myxql, ">= 0.0.0"},
1 Like

That might be a bug. Looking at the code it seems like type references for those columns changed with the work of introducing scopes in 1.8.

2 Likes

Thanks for looking into it. I am generating the tables by hand and moving on.

I would be great if you can create an issue on the github repo, so this can be fixed going forward. There will be other people running into that as well I’d imagine.

Sure. Just please point me to the right repo as I am really fresh here. Is it the phoenix_ecto integration or something else?

The generators are all maintained in

Alternatively you can also manually put another type: ... there.

Dang, I’m sorry, I probably should have pushed an issue up to ecto_sql a long time ago. I’ve know about this for years.

By default tables are created with a primary key of bigserial.

In mysql bigserial is not a real type. It’s an alias to bigint unsigned.

Anywhere that you do a references you should pass a type of bigint.

add :thing_id, references(:thing, type: :bigint)

Although there may be a better way to do it, I’m not sure. I wouldn’t be surprised if mysql or ecto_sql has a toggle somewhere. I haven’t set up a new ecto repo with MySQL in a while

1 Like

Thanks. I’ve managed to create the tables manually as said before, but it’ll help in the future. Closing as I moved on with the project and this is not a deal breaker.