Ecto creating unique index failed for Mysql/Mariad

Question

I try to do the following migration:

defmodule Shopper.Repo.Migrations.MakeNameUniqueShopper do
  use Ecto.Migration

  def change do
    create unique_index :shoppers, [:name]
  end
end

Also tried create unique_index :shoppers, [:name], name: :name_unique, create unique_index :shoppers, [:name], name: "name_unique", and create index(:shoppers, [:name], unique: true)

But they failed with similar error:

[info]  == Running Shopper.Repo.Migrations.MakeNameUniqueShopper.change/0 forward

[info]  create index shoppers_name_index
** (Mariaex.Error) (1071): Specified key was too long; max key length is 767 bytes
    (ecto) lib/ecto/adapters/sql.ex:172: Ecto.Adapters.SQL.query!/5
    (elixir) lib/enum.ex:1261: Enum."-reduce/3-lists^foldl/2-0-"/3
...
...

Any help would be very appreciated, to help me with the error.

Note: I'm using ecto 1.02

Following is the first migration created with mix phoenix.gen.model

defmodule Shopper.Repo.Migrations.CreateV1.Shopper do
  use Ecto.Migration

  def change do
    create table(:shoppers) do
      add :name, :string
      add :oauth_token, :string

      timestamps
    end
  end
end

Info: the name field is utf8mb4, specified by my schema

Update: I know the solution is to reduce the name field length, but how to make it work with phoenix model and migration? As it expects a string?

Answer 1

the issue here is InnoDB's key size (767 bytes) which directly maps to the possible size of varchar() columns with respect to the column's charset. if you use the charset utf8, a varchar column can store 255 characters at max. if you use utf8mb4, a varchar column can only store 191 characters.

this blog post goes into full details: https://mathiasbynens.be/notes/mysql-utf8mb4

while applying this rule to the index, like josé suggests, is certainly one possiblity, i'd say you better fix your varchar column sizes and have CREATE INDEX not complain in the first place and have a proper table column layout:

defmodule Shopper.Repo.Migrations.CreateV1.Shopper do
  use Ecto.Migration

  def change do
    create table(:shoppers) do
      add :name, :varchar, size: 191
      add :oauth_token, :varchar, size: 191

      timestamps
    end
    create unique_index(:shoppers, [:name])
  end
end

attention: add :name, :string, size: 191 will not work as ecto maps the :string type directly to varchar(255) in the mysql adapter

my opinion is that ecto's mysql adapter should take the charset into consideration when mapping :string to its native type just like rails does it.

Answer 2

The field "name" is too long. You should either ensure it has size less than 767 bytes by passing the size option when declaring it or index just part of the field:

create unique_index :shoppers, ["name(20)"], name: :shoppers_name_unique

Keep in mind that you will need to give the same name when calling unique_constraint/2 in your changeset.

Answer 3

Thanks to José Valim for helping me through his answer, though this answer is the exact solution for my problem.

Create a new ecto migration script with the following code:

defmodule Shopper.Repo.Migrations.MakeNameUniqueShopper do
  use Ecto.Migration

  def change do
    alter table(:shoppers) do
      modify :name, :string, size: 100
    end

    create unique_index :shoppers, [:name], name: :shopper_name_unique
  end
end

Answer 4

An alternative to creating shorter varchar/text column sizes with utf8mb4 encoding is to configure MySQL to increase the maximum InnoDB index prefix size to 3072 bytes.

defmodule Shopper.Repo.Migrations.CreateV1.Shopper do
  use Ecto.Migration

  def change do
    # just needs to be done once
    execute "SET GLOBAL innodb_file_format = BARRACUDA"
    execute "SET GLOBAL innodb_file_per_table = ON"
    execute "SET GLOBAL innodb_large_prefix = ON"

    # in MySQL 5.7.9 or higher, this sets the default row format
    # otherwise for all new tables you create, you must manually 
    # alter row_format to dynamic before adding any string/text columns
    execute "SET GLOBAL innodb_default_row_format = DYNAMIC"

    # change existing shoppers row format to dynamic
    execute "ALTER TABLE shoppers ROW_FORMAT = DYNAMIC"

    create unique_index :shoppers, [:name], name: :shopper_name_unique         
  end
end