Mysql2::Error on Migration for mobility_string_translations when encoding is utf8mb4 about mobility HOT 7 CLOSED

I believe you need to set the options key/value passed to create_table so that it has ROW_FORMAT=DYNAMIC in it.

e.g. (haven't tested this, but):

class CreateStringTranslations < ActiveRecord::Migration[5.1]

  def change
    create_table :mobility_string_translations, options: "... ROW_FORMAT=DYNAMIC" do |t|
      t.string  :locale, limit: 15 # NEW limit option
      t.string  :key, limit: 243   # NEW limit option
      t.string  :value
      t.integer :translatable_id
      t.string  :translatable_type
      t.timestamps
    end
    add_index :mobility_string_translations, [:translatable_id, :translatable_type, :locale, :key], unique: true, name: :index_mobility_string_translations_on_keys
    add_index :mobility_string_translations, [:translatable_id, :translatable_type, :key], name: :index_mobility_string_translations_on_translatable_attribute
    add_index :mobility_string_translations, [:translatable_type, :key, :value, :locale], name: :index_mobility_string_translations_on_query_keys
  end
end

where ... would be stuff like COLLATE, etc. Also note that the code above is a bit outdated, the latest version of Mobility creates the table slightly differently (but same idea, just add the ROW_FORMAT option).

Btw, looks like this will become the default in Rails 6, since many people have problems with it: rails/rails#34742

from mobility.

Thanks for the report, yes I do know utf8mb4 and use it in production (not with Mobility however). Limiting the length of locale might make sense, but what is the justifiction for limiting the key to 243 chars? The key holds the name of the attribute, which is just a string without any constraints, so I don't think a limit should be imposed on that one.

I notice that other translation gems, e.g. Globalize, do not impose a limit on the length of locale, so I'm just a little bit concerned that imposing such a limit may impact some edge cases.

@pwim any thoughts?

By the way, for the problem of long indexes, we use this fix:

# /etc/my.cnf

[mysqld]
innodb_file_format=barracuda
innodb_large_prefix = 1

Not sure if that helps for your case though.

from mobility.

I'd be hesitant to set key lengths just so the migration works with some specific mysql configuration, as we'd be making it work in this case, but potentially creating other edge cases.

As for limiting the locale, Rails by default uses iso639-1 to represent locales as two characters. However, it doesn't actually do anything to enforce it. Having a locale + country code might be the most common alternate pattern (such as "en-CA"). Its also possible that people are using other arbitrary strings (there's nothing stopping you from having "English" as your locale).

One option would be for people running into this issue to just manually adjust the migration files themselves. Another would be to use a different backend (I think the table backend for instance shouldn't have this issue).

from mobility.

One option would be for people running into this issue to just manually adjust the migration files themselves.

Yes, my feeling is that this is quite simple and anyone who cares can do this quite easily. None of the code would change as a result of the limit of the locale column.

from mobility.

I'm going to close this issue because I don't think we want to change the current auto-generated migrations to support an edge case -- the user can easily do that themselves. We could perhaps add a note to the readme about this, @eiapopeia if you want to make a PR doing that I'd be glad to merge it.

from mobility.

@shioyama I'm sorry to resurrect an issue this old, but how exactly do I modify the migration file so that I don't get this error on mysql 5.5? I've tried limiting the size of the locale and key fields, but no matter how low I go, I still get the same error on mobility_text_translations.

from mobility.

Thanks for providing that. Using that style of migration and adding the following to the my.cnf makes everything work.

innodb_large_prefix             = 1
innodb_file_format              = barracuda
innodb_file_per_table           = 1

from mobility.