search

elixir-ecto / myxql

MySQL 5.5+ driver for Elixir
Apache License 2.0
273 stars 67 forks source link

`(MyXQL.Error) (1071) Specified key was too long; max key length is 3072 bytes` for any index size #139

Closed thiagomajesk closed 3 years ago

thiagomajesk commented 3 years ago

I was playing with the idea of migrating a Postgres database to MySQL 8 but the experience hasn't been pleasant so far. After running the migrations (mix ecto.migrate --log-sql) I'm having this error:

17:41:08.793 [info]  create table locations

17:41:08.835 [debug] QUERY OK db=41.7ms queue=0.5ms idle=50.6ms
CREATE TABLE `locations` (`id` binary(16) NOT NULL, `country` varchar(255), `country_slug` varchar(255), `city` varchar(255), `city_slug` varchar(255), `state` varchar(255), `state_slug` varchar(255), `district` varchar(255), `district_slug` varchar(255), `city_summary` varchar(255), `city_summary_slug` varchar(255), `inserted_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`)) ENGINE = INNODB []

17:41:08.835 [info]  create index locations_country_state_city_district_index

17:41:08.883 [debug] QUERY ERROR db=1.1ms queue=46.8ms idle=0.1ms
CREATE UNIQUE INDEX `locations_country_state_city_district_index` ON `locations` (`country`, `state`, `city`, `district`) []
** (MyXQL.Error) (1071) Specified key was too long; max key length is 3072 bytes
    (ecto_sql 3.6.2) lib/ecto/adapters/sql.ex:760: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.12.0) lib/enum.ex:1553: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql 3.6.2) lib/ecto/adapters/sql.ex:848: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.6.2) lib/ecto/migration/runner.ex:343: Ecto.Migration.Runner.log_and_execute_ddl/3
    (ecto_sql 3.6.2) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
    (elixir 1.12.0) lib/enum.ex:2356: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto_sql 3.6.2) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
    (stdlib 3.15) timer.erl:166: :timer.tc/1
    (ecto_sql 3.6.2) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/8
    (ecto_sql 3.6.2) lib/ecto/migrator.ex:324: Ecto.Migrator.attempt/8
    (ecto_sql 3.6.2) lib/ecto/migrator.ex:251: anonymous fn/5 in Ecto.Migrator.do_up/5
    (ecto_sql 3.6.2) lib/ecto/migrator.ex:295: anonymous fn/6 in Ecto.Migrator.async_migrate_maybe_in_transaction/7
    (ecto_sql 3.6.2) lib/ecto/migrator.ex:310: Ecto.Migrator.run_maybe_in_transaction/4
    (elixir 1.12.0) lib/task/supervised.ex:90: Task.Supervised.invoke_mfa/2
    (elixir 1.12.0) lib/task/supervised.ex:35: Task.Supervised.reply/5
    (stdlib 3.15) proc_lib.erl:226: :proc_lib.init_p_do_apply/3

Even if I override the index name to be shorter, the problem still persists: create unique_index(:locations, [:country, :state, :city, :district], name: "i").

After searching a little bit on the internet it seems there's no obvious solution, problems vary between changing engine, charset, or collation. There's also this other issue that seems related, but as you can see from the logs, the table columns are already limited by default to 255.

I just did a clean installation of the MySQL package on WSL (mysql Ver 8.0.25-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))). It seems that the experience out-of-the-box is not as smooth as I thought it would be for Postgres.

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.

The @rkorzeniec's fix of setting the size opt to 191 does the trick, but what would be a long-term solution for this? It's really counterintuitive and it's the first time I've encountered this problem as well.

josevalim commented 3 years ago

Yes, the comment in the issue you linked explains it. The issue is not the index name. The issue is that you are indexing data that is longer than the maximum index size. This is a MySQL behaviour, there is nothing Ecto can do. Either change the column or index only part of the data.

josevalim commented 3 years ago

I also recommend reading the reply to the comment you quoted in full: https://github.com/elixir-ecto/myxql/issues/102#issuecomment-752053310 - there is no way Ecto can address a limitation that is in your database. Maybe we could silently truncate the data for you, but if silently truncating was a good idea, then MySQL would do it instead of erroring. Instead you should be explicit which part of your data you want to index in the limited size.