Mysql::Error: Specified key was too long; max key length is 1000 bytes

Question

script/generate acts_as_taggable_on_migration
rake db:migrate

causes

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)

http://github.com/mbleigh/acts-as-taggable-on/blob/master/generators/acts_as_taggable_on_migration/templates/migration.rb — amaseuk, Aug 15 '10 at 20:26
Similar problem here http://community.engineyard.com/discussions/problems/420-database-index-problems that got resolved but don't know how. — amaseuk, Aug 16 '10 at 15:07
Fixes for 767 problem: http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes — Rick James, Oct 06 '19 at 01:11
If with **Java Hibernate** refer to answer https://stackoverflow.com/a/76016256/9370144 — Nadun Kulatunge, Apr 14 '23 at 14:40

score 56 · Accepted Answer · edited Oct 04 '19 at 11:38

56

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.

edited Oct 04 '19 at 11:38

Nik

2,885
2
25
25

answered Aug 15 '10 at 21:24

OMG Ponies

325,700
82
523
502

2

Do you know of a type of MySQL engine that would not cause the error above? – amaseuk Aug 16 '10 at 07:46
6

Resolved by changing the default engine from MyISAM to InnoDB. Thank you. – amaseuk Aug 16 '10 at 19:42
3

"Resolved by changing the default engine from MyISAM to InnoDB." Sounds impossible as InnoDB actually just has a shorter limit. – Stijn de Witt Dec 05 '16 at 10:36
Sounds impossible but changing from MyISAM to InnoDB also worked for me. WAMP, Win10 – Howdy_McGee May 20 '20 at 03:53

score 4 · Answer 2 · answered Oct 12 '15 at 10:12

4

if this error occur in some proccess like migration, it could be solved by changing config file of MySql (*.ini)

default-storage-engine=InnoDB

answered Oct 12 '15 at 10:12

user3410311

582
4
6

score 3 · Answer 3 · answered Jul 17 '15 at 18:25

3

This seems to be a bug that was reported here: http://bugs.mysql.com/bug.php?id=4541

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 Jul 17 '15 at 18:25

Pabinator

1,601
1
21
25

2

Unknown system variable 'storage_engine'. does not work as of mysql 5.7 – astroanu Feb 14 '18 at 09:47
The storage_engine system variable was removed in MySQL 8.0, which is why you are receiving the Unknown system variable error. To set the default storage engine for newly created tables in MySQL 8.0 and later versions, you can use the default_storage_engine system variable instead of storage_engine and by using below command you can achieve this. `SET GLOBAL default_storage_engine = 'InnoDB';` – Asad Khan Mar 24 '23 at 09:21
If with **Java Hibernate** refer to answer https://stackoverflow.com/a/76016256/9370144 – Nadun Kulatunge Apr 14 '23 at 14:40

score 0 · Answer 4 · answered Aug 15 '10 at 20:29

0

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 Aug 15 '10 at 20:29

BitKFu

3,649
3
28
43

Hi, This is a common Rails plugin/gem and I have not seen this error message quoted on the Internet - not sure whether it is the migration file, or my database, that needs amending...? – amaseuk Aug 15 '10 at 20:32
Which MySql Version are you using? The current one or Mysql 4.x? – BitKFu Aug 15 '10 at 20:40

Mysql::Error: Specified key was too long; max key length is 1000 bytes

4 Answers4

Linked