Search code examples
ruby-on-railsdatabaserails-migrationsacts-as-taggable-on

Acts_as_taggable_on index too long

I am trying to use the acts_as_taggable_gem but I am having issues with the migration.

in the automatically generated ActsAsTaggableOnMigration file I am getting the following error

Mysql2::Error: Specified key was too long; max key length is 767 bytes: CREATE INDEX 'index_taggings_on_taggable_id_and_taggable_type_and_context' ON 'taggings' ('taggable_id', 'taggable_type', 'context')

on the line add_index :taggings, [:taggable_id, :taggable_type, :context]

I have used the gem on another project successfully in the past. The only difference is this time the database charset is utf8mb4, This is a requirement to support emojis.

I have tried reducing the :limit (default is 128) on the :context to no avail

Solution

  • You can solve this issue in two ways:

    Changing your storage engine (MySQL) from InnoDB to MyISAM, MyISAM supports 1000 bytes long prefix for indexing, whereas InnoDB support 767 bytes.

    Or by specifying context length:

    add_index :taggings, [:taggable_id, :taggable_type, :context], name: 'by_id_type_context', length: {context: 128}

 # => CREATE INDEX by_id_type_context ON taggings(taggable_id, taggable_type, context(128))

    Note: SQLite doesn’t support index length.