Rails migration - Mysql2::Error: Specified key was too long; max key length is 767 bytes

Question

I am working on Ruby on Rails project.

I am using MySQL2 as an adapter.

This is the one of migrations file.

class CreatePages < ActiveRecord::Migration[6.0]
  def change
    create_table :pages do |t|
      t.string :base_url, null: false
      t.string :html, null: false
      t.string :styles, null: false
      t.text :images, null: false

      t.timestamps
    end

    add_index :pages, [:base_url, :html, :styles, :images], length: { images: 767 }, unique: true
  end
end

rake db:migrate

I am getting this error.

Mysql2::Error: Specified key was too long; max key length is 767 bytes
/home/ubuntu/project/db/migrate/20200504001308_create_pages.rb:12:in `change'

Caused by:
ActiveRecord::StatementInvalid: Mysql2::Error: Specified key was too long; max key length is 767 bytes
/home/ubuntu/project/db/migrate/20200504001308_create_pages.rb:12:in `change'

Caused by:
Mysql2::Error: Specified key was too long; max key length is 767 bytes
/home/ubuntu/project/db/migrate/20200504001308_create_pages.rb:12:in `change'

Can anyone help me?

Answer 1

Depending on your table encoding certain character sets can use more than one byte per character. The utf8 encoding in MySQL requires 3 bytes per character, so the string limit should be 255 for this encoding (767 / 3). If the encoding is utf8mb4, which requires 4 bytes per character, the limit should be set to 191 (767 / 4).

So your migration file may look like this:

class CreatePages < ActiveRecord::Migration[6.0]
  def change
    create_table :pages do |t|
      t.string :base_url, limit: 191, null: false
      t.string :html, limit: 191, null: false
      t.string :styles, limit: 191, null: false

      # etc ...
    end
  end
end

This appears to be a limitation of older MySQL versions, which is why Rails doesn't handle this case by default.