MyXQL.Error ER_TOO_LONG_KEY on custom primary key

[1player]

Creating the following table on mysql:5.6.37:

create table("ratings", primary_key: false) do
  add(:id, :string, primary_key: true)
end

fails with error:

** (MyXQL.Error) (1071) (ER_TOO_LONG_KEY) Specified key was too long; max key length is 767 bytes
    (ecto_sql 3.3.3) lib/ecto/adapters/sql.ex:612: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.10.1) lib/enum.ex:1396: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql 3.3.3) lib/ecto/adapters/sql.ex:699: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.3.3) lib/ecto/migration/runner.ex:343: Ecto.Migration.Runner.log_and_execute_ddl/3
    (ecto_sql 3.3.3) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
    (elixir 1.10.1) lib/enum.ex:2111: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto_sql 3.3.3) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
    (stdlib 3.11.2) timer.erl:166: :timer.tc/1

No idea what key MyXQL is trying to use, and I'm not sure how to manually enter a different key name. In any case, the name of the table and field should be short enough to fit in 767 I reckon.

[josevalim]

Please call mix ecto.migrate --log-sql. It should log all commands and hopefully reveal what is going wrong. But maybe it is not an issue with the key name being long, but rather that the string column is too long and you need to limit it as primary key. Passing something like size: 256 should fix it.

[1player]

Thanks for the heads up, that fixed it (almost).

MyXQL was already limiting the field to 255 varchars, which is below the 767 byte limit (each UTF-8 character in MySQL is 3 bytes).

But in my case I'm using the UTF8mb4 charset, and I had to pass size = 767/4 = 191 for it to work.

[rkorzeniec]

I just encountered the same issue:

20:37:39.317 [info]  == Running 20201225192349 Athletistic.Repo.Migrations.CreateUsers.change/0 forward

20:37:39.321 [info]  create table users

20:37:39.335 [debug] QUERY OK db=12.1ms queue=0.5ms idle=36.5ms
CREATE TABLE `users` (`id` bigint unsigned not null auto_increment, `username` varchar(255), `access_token` varchar(255), `refresh_token` varchar(255), `scopes` varchar(255), `expires_at` datetime, `inserted_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`)) ENGINE = INNODB []

20:37:39.335 [info]  create index users_username_index

20:37:39.343 [debug] QUERY ERROR db=6.8ms queue=0.2ms idle=1.8ms
CREATE UNIQUE INDEX `users_username_index` ON `users` (`username`) []
** (MyXQL.Error) (1071) (ER_TOO_LONG_KEY) Specified key was too long; max key length is 767 bytes
    (ecto_sql 3.5.3) lib/ecto/adapters/sql.ex:751: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.11.1) lib/enum.ex:1399: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql 3.5.3) lib/ecto/adapters/sql.ex:838: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.5.3) lib/ecto/migration/runner.ex:343: Ecto.Migration.Runner.log_and_execute_ddl/3
    (ecto_sql 3.5.3) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
    (elixir 1.11.1) lib/enum.ex:2181: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto_sql 3.5.3) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
    (stdlib 3.13.2) timer.erl:166: :timer.tc/1
    (ecto_sql 3.5.3) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/8
    (ecto_sql 3.5.3) lib/ecto/migrator.ex:349: Ecto.Migrator.attempt/8
    (ecto_sql 3.5.3) lib/ecto/migrator.ex:250: anonymous fn/5 in Ecto.Migrator.do_up/5
    (ecto_sql 3.5.3) lib/ecto/migrator.ex:327: Ecto.Migrator.run_maybe_in_transaction/6
    (elixir 1.11.1) lib/task/supervised.ex:90: Task.Supervised.invoke_mfa/2
    (elixir 1.11.1) lib/task/supervised.ex:35: Task.Supervised.reply/5
    (stdlib 3.13.2) proc_lib.erl:226: :proc_lib.init_p_do_apply/3

Thanks, @1player for your suggestion, after setting my strings to size: 191 it did the trick:

mix ecto.migrate                                                                                
20:39:38.185 [info]  == Running 20201225192349 Athletistic.Repo.Migrations.CreateUsers.change/0 forward
20:39:38.189 [info]  create table users
20:39:38.205 [info]  create index users_username_index
20:39:38.220 [info]  == Migrated 20201225192349 in 0.0s

Definitely do not want to spray hate here (just freshly started with elixir + phoenix), but never encountered this issue before on rails + mysql, when setting unique index, leading me to believe that this it is still a bug.

[1player]

It is not a bug, it's MySQL having very weird and anachronistic limitations. I don't think there's any way the library can show a warning or raise an error to let you know MySQL indexes are limited to 767 bytes, which means 767 chars on a single byte encoding (i.e. ISO8859-1), 255 chars on 3 byte encodings (MySQL's flavour of UTF8) or 191 chars on 4 byte encodings (MySQL's UTF8mb4 encoding).

As the error is being raised by the MySQL server itself, you'll find you'll get the same error on Rails or Django or creating the table manually via mysqlclient, given the same preconditions (same DB version, same encoding, etc.)

[josevalim]

As the error is being raised by the MySQL server itself, you'll find you'll get the same error on Rails or Django or creating the table manually via mysqlclient, given the same preconditions (same DB version, same encoding, etc.)

Just to add an example that this issue does happen exactly the same elsewhere and help avoid future reports: https://stackoverflow.com/questions/63158705/rails-migration-mysql2error-specified-key-was-too-long-max-key-length-is-7

You need to either limit the column or limit the index by telling it to index only part of your column.