mysqlerror specified key was too long max key length is 1000 bytes

mysqlerror specified key was too long max key length is 1000 bytes  using -'mysql,sql,ruby-on-rails,indexing,mysql-error-1071'

script/generate acts_as_taggable_on_migration
rake db:migrate


Mysql::Error: Specified key was too long; max key length is 1000 bytes: CREATE  INDEX `index_taggings_on_taggable_id_and_taggable_type_and_context` ON `taggings` (`taggable_id`, `taggable_type`, `context`)

What should I do?

Here is my database encoding:

mysql> SHOW VARIABLES LIKE 'character\_set\_%';
| Variable_name            | Value  |
| character_set_client     | latin1 |
| character_set_connection | latin1 |
| character_set_database   | utf8   |
| character_set_filesystem | binary |
| character_set_results    | latin1 |
| character_set_server     | latin1 |
| character_set_system     | utf8   |
7 rows in set (0.00 sec)


asked Sep 29, 2015 by sameer rathore
0 votes

4 Answers

0 votes

This is solely a MySQL issue -

MySQL has different engines - MyISAM, InnoDB, Memory...

MySQL has different limits on the amount of space you can use to define indexes on column(s) - for MyISAM it's 1,000 bytes; it's 767 for InnoDB. And the data type of those columns matters - for VARCHAR, it's 3x so an index on a VARCHAR(100) will take 300 of those bytes (because 100 characters * 3 = 300).

To accommodate some indexing when you hit the ceiling value, you can define the index with regard to portions of the column data type:

CREATE INDEX example_idx ON YOUR_TABLE(your_column(50))

Assuming that your_column is VARCHAR(100), the index in the example above will only be on the first 50 characters. Searching for data beyond the 50th character will not be able to use the index.

answered Sep 29, 2015 by bhavin
0 votes

I think one of your fields is a varchar with more than 1000 chars. e.g. context?

Think about the meaning of an index. It's quick access to a row when all your indexed fields are within the where clause. If an index is to long (in case of mysql more than 1000 bytes), it makes no sense to use an index, because it's probably slower than accessing the complete table with a full table scan.

I would suggest to shorten the index, e.g to taggable_id and taggable_type, if those both are the shorter once.

Cheers - Gerhard

answered Sep 29, 2015 by girisha
0 votes

I had this problem, so my solution was this:

alter table robs_temp.missing_email change email email varchar(300);
ALTER TABLE robs_temp.missing_email add primary key (email); -- Now it works.

According to wikipedia valid emails can't be over 256 characters. Perhaps your data has some upper limit.

answered Sep 29, 2015 by mca.agarwal
0 votes

This seems to be a bug that was reported here:

If you have tried all the answers on this post and still getting the error, you may want to try to run this command on your SQL query window.

set GLOBAL storage_engine='InnoDb';
answered Sep 29, 2015 by tushar2k6