Rails creating schema_migrations - Mysql2::Error: Specified key was too long

Question

I am using Rails 3.2.6 and Mysql 6.0.9 (but I have exactly the same error on MySQL 5.2.25)

When I create new database (rake db:create) and then when I try to load the schema (rake schema:load) I get this error:

Mysql2::Error: Specified key was too long; max key length is 767 bytes: CREATE UNIQUE INDEX `unique_schema_migrations` ON `schema_migrations` (`version`)

After hours and hours of research I found these solutions:

1. Change MySQL variable innodb_large_prefix to true (or ON)

This didn't work. I tried it on my Linux server, my Mac and even on Windows - it just doesn't work.

2. Monkeypatch ActiveRecord::SchemaMigration.create_table

I do not need the version column to be 255 long (when it is UTF-8, then it takes 4*255 = 1020 bytes and exceeds the MySQL limit of 767 byte for keys). I do not need it to be UTF-8 either, but all other tables in the DB are UTF-8 and I have set utf8_czech_ci to be the default collation.

The method that actually creates the schema_migrations table looks like this:

def self.create_table
  unless connection.table_exists?(table_name)
    connection.create_table(table_name, :id => false) do |t|
      t.column :version, :string, :null => false
    end
    connection.add_index table_name, :version, :unique => true, :name => index_name
  end
end

You can read the whole file on Github rails/rails

So I tried to add :limit => 100 to the t.column statement, but I did not succeed with this solution either. The problem is that I cannot make this patch load when the originial is already in place. In other words - my patch loads before ActiveRecord::SchemaMigration so it is overwritten.

When I put this in config/initializers/patches/schema_migration.rb:

require 'active_record/scoping/default'
require 'active_record/scoping/named'
require 'active_record/base'

module ActiveRecord
  class SchemaMigration < ActiveRecord::Base
    def self.create_table
      unless connection.table_exists?(table_name)
        connection.create_table(table_name, :id => false) do |t|
          t.column :version, :string, :null => false, :limit => 100
        end
        connection.add_index table_name, :version, :unique => true, :name => index_name
      end
    end
  end
end

It is successfully loaded, but the it is overwritten when the original ActiveRecord::SchemaMigration is loaded.

I tried to mess up with ActiveSupport.on_load(:active_record) but that doesn't seem to work either.

Is there a way to load this file after the originial ActiveRecord::SchemaMigration is in place and make this patch work?

Do you have any suggestions? I can clarify any part of this question, if it makes no sense to you. Just ask me. I've been stuck with this for too long.

Have you considered creating `schema_migrations` (complete with a sensible column size) by hand before doing any Rails stuff at all? — mu is too short, Jul 25 '12 at 07:43
Yes I have considered that, but I am afraid that this will not work with `rake db:test:clone`? Also, It will complicate setup on production servers/developer machines, because I will have to run some script to pre-initialize the db and create this table. — Lukáš Voda, Jul 25 '12 at 08:20

score 9 · Answer 1 · edited Oct 30 '12 at 17:22

9

767 key should work. Make sure you use utf8 encoding, and not utf16. I had same problem, and my mistake was that I accidently created utf16 database

edited Oct 30 '12 at 17:22

Boeckm

3,264
4
36
41

answered Oct 30 '12 at 16:55

dpa

427
4
16

2

Add>> "encoding: utf8" in database.yml – Atul Jan 06 '13 at 13:32
7

This error is caused by using `utf8mb4` encoding for database which was made specifically for allowing emoji and other utf8 characters. What you are offering is going back to `utf8` with no support for extended unicode. – firedev Mar 13 '14 at 10:28
You can limit your indexed column to 191 or less using utf8mb4. THe problem happens because in uft8mb4 maxlength is in bytes. – William Weckl Aug 08 '14 at 13:20
Worked! +1 And `collation` is is set to `utf8_general_ci` – ray Mar 01 '19 at 12:49

score 5 · Answer 2 · answered Sep 22 '17 at 23:56

5

I suggest you to drop your database and recreate a new one with the following instructions :

 mysql -u root -p -e "CREATE DATABASE {DB_NAME} DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;"

answered Sep 22 '17 at 23:56

Tracy LOISEL

81
1
1

anquegi · Answer 3 · 2019-10-30T16:12:13.607

I have the same problem with a column named version for varchar of length 2000

class AddVersionToUsers < ActiveRecord::Migration
  def change
    add_column :users, :version, :string, limit:2000
    add_index  :users, :version
  end
end

I was using this latin 1 1 character 1 byte, but now I want to use utf8mb4 1 character 4 bytes.

Configuring your databse like this you can get index until 3072 bytes:

docker run -p 3309:3306 --name test-mariadb -e MYSQL_ROOT_PASSWORD=Cal1mero. -d mariadb:10.2 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --innodb-large-prefix=1 --innodb-file-format=barracuda --innodb-file-per-table=1 --innodb-strict-mode=1 --innodb-default-row-format=dynamic

this is enough for latin_1, (will be 2000 bytes), but for utf8mb4 it will be 8000 bytes. In this keys you have some options

Add a column named hash_version and implement the index on that column.

Consistent String#hash based only on the string's content

Make the string shorter, it should work , but depernds on your needs

or use fulltext in your migrations, like this:

class AddVersionToUsers < ActiveRecord::Migration
  def change
    add_column :users, :version, :string, limit:2000
    add_index  :users, :version, type: :fulltext
  end
end

references:

Rails creating schema_migrations - Mysql2::Error: Specified key was too long

1. Change MySQL variable innodb_large_prefix to true (or ON)

2. Monkeypatch ActiveRecord::SchemaMigration.create_table

3 Answers3

Linked