Error when installing on Vitess MySQL db

[janekwunderlich]

The install is erroring when trying to run the migrations to setup the create_text_translations and create_string_translations tables. Specifically the key length exceeds the max of 3072 bytes

Expected Behavior

Sets up the tables without errors.

Actual Behavior

      create  db/migrate/20230313221231_create_text_translations.rb
      create  db/migrate/20230313221232_create_string_translations.rb
      create  config/initializers/mobility.rb
➜  Steeped git:(janek/translations) ✗ rails db:migrate
== 20230313221231 CreateTextTranslations: migrating ===========================
-- create_table(:mobility_text_translations)
   -> 0.0640s
-- add_index(:mobility_text_translations, [:translatable_id, :translatable_type, :locale, :key], {:unique=>true, :name=>:index_mobility_text_translations_on_keys})
   -> 0.0879s
-- add_index(:mobility_text_translations, [:translatable_id, :translatable_type, :key], {:name=>:index_mobility_text_translations_on_translatable_attribute})
   -> 0.0854s
== 20230313221231 CreateTextTranslations: migrated (0.2377s) ==================

== 20230313221232 CreateStringTranslations: migrating =========================
-- create_table(:mobility_string_translations)
   -> 0.1149s
-- add_index(:mobility_string_translations, [:translatable_id, :translatable_type, :locale, :key], {:unique=>true, :name=>:index_mobility_string_translations_on_keys})
   -> 0.1011s
-- add_index(:mobility_string_translations, [:translatable_id, :translatable_type, :key], {:name=>:index_mobility_string_translations_on_translatable_attribute})
   -> 0.1255s
-- add_index(:mobility_string_translations, [:translatable_type, :key, :value, :locale], {:name=>:index_mobility_string_translations_on_query_keys})
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: target: steeped.-.primary: vttablet: rpc error: code = InvalidArgument desc = Specified key was too long; max key length is 3072 bytes (errno 1071) (sqlstate 42000) (CallerID: kdcw8y8poizstluqfi43): Sql: "alter table mobility_string_translations add INDEX index_mobility_string_translations_on_query_keys (translatable_type, `key`, value, locale)", BindVars: {REDACTED}
/Users/janek/Development/Steeped/db/migrate/20230313221232_create_string_translations.rb:12:in `change'

Caused by:
ActiveRecord::StatementInvalid: Mysql2::Error: target: steeped.-.primary: vttablet: rpc error: code = InvalidArgument desc = Specified key was too long; max key length is 3072 bytes (errno 1071) (sqlstate 42000) (CallerID: kdcw8y8poizstluqfi43): Sql: "alter table mobility_string_translations add INDEX index_mobility_string_translations_on_query_keys (translatable_type, `key`, value, locale)", BindVars: {REDACTED}
/Users/janek/Development/Steeped/db/migrate/20230313221232_create_string_translations.rb:12:in `change'

Caused by:
Mysql2::Error: target: steeped.-.primary: vttablet: rpc error: code = InvalidArgument desc = Specified key was too long; max key length is 3072 bytes (errno 1071) (sqlstate 42000) (CallerID: kdcw8y8poizstluqfi43): Sql: "alter table mobility_string_translations add INDEX index_mobility_string_translations_on_query_keys (translatable_type, `key`, value, locale)", BindVars: {REDACTED}
/Users/janek/Development/Steeped/db/migrate/20230313221232_create_string_translations.rb:12:in `change'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)```

[shioyama]

Hmm ok, well I'm not using vitesse so I can't reproduce this, but you can anyway just adjust the generated migrations to make the index name shorter.

[janekwunderlich]

The problem isn't the length of the index name, it's the key length of the index.

[:translatable_type, :key, :value, :locale] exceeds 3072 bytes apparently.

[h4b00]

This is how I made it work

    create_table :mobility_string_translations do |t|
      t.string :locale, null: false, limit: 10
      t.string :key, null: false, limit: 35
      t.string :value
      t.references :translatable, polymorphic: true, index: false
      t.timestamps null: false
      t.index [:translatable_id, :translatable_type, :locale, :key], unique: true, name: :i_translations_on_id_l_t_k
      t.index [:translatable_id, :translatable_type, :key], name: :i_translations_on_id_type_key
      t.index [:translatable_type, :key, :value, :locale], name: :i_translations_on_k_v_l
    end

[shioyama]

Yeah exactly, just give them a name like @h4b00 showed above.