Mysql::Error: Specified key was too long; max key length is 767 bytes: CREATE INDEX

Question

I'm working with rails 2.3.5 application, in witch I have this field

t.string   "trip_cities",             :limit => 256

And this index

add_index "bookings", ["trip_cities"], :name => "trip_cities"

When I try to execute:

bundle exec rake db:test:load

I receive this error Mysql::Error: Specified key was too long; max key length is 767 bytes: CREATE INDEX 'trip_cities' ON 'bookings' ('trip_cities') and don't quite know how to resolve this.

score 14 · Accepted Answer · answered Nov 12 '13 at 22:04

It sounds like the default collation uses the UTF8 character set.

MySQL limits the length of keys by bytes, not characters. Since the UTF8 implementation MySQL uses allows for 3 bytes per character, the max length of a key on a UTF8 column is 3 times the key length in characters (the key length is the full length of the field if not explicitly specified).

In this case the max key length would be 256 * 3 which is 768. You need to either limit the length of the key or change the collation of the column.

This is actually a working "update" : https://github.com/rails/rails/issues/9855#issuecomment-28874587 — Ben, Aug 06 '14 at 08:40

score 9 · Answer 2 · answered Nov 12 '13 at 21:58

9

how about changing the migration itself:

remove_index "bookings", :name => :trip_cities
add_index "bookings", ["trip_cities"], :name => :trip_cities, :length => { :trip_cities => 255 }

answered Nov 12 '13 at 21:58

Andrey Yasinishyn

1,851
2
23
36

This is the most useful answer -- I assume this is what the accepted answer means by "change the collation of the column", but this actually shows how to do it. – David Moles Jan 06 '17 at 17:06
@DavidMoles This doesn't change the collation, it just limits the data so it fits within the constraints imposed by the existing collation. Changing the collation would need to happen in the definition of the table on the server. However, this may still be a better way to get past this issue if the structure cannot be changed. – G-Nugget Mar 07 '17 at 17:23
@G-Nugget Aha. Do you think you could update your answer with an example of how you change the collation? – David Moles Mar 07 '17 at 19:07

score 5 · Answer 3 · answered Sep 02 '18 at 00:56

5

You should recreate your database with correct options.

This helped to me with same problem

CREATE DATABASE ${DB} DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

answered Sep 02 '18 at 00:56

Kein

977
2
12
32

Helped me too! Thanks! – Fernando Kosh Jan 03 '20 at 21:34

score 0 · Answer 4 · answered Apr 21 '19 at 10:38

0

Set a limit on the length of the string to 191 characters, example:

t.string :project, index: { unique: true }, limit: 191

OR

Use this patch: https://github.com/rails/rails/issues/9855#issuecomment-390366184

answered Apr 21 '19 at 10:38

artamonovdev

2,260
1
29
33

score 0 · Answer 5 · answered Jul 22 '19 at 10:02

Adding a length option to the index in schema.rb worked for me:

t.index ["your_index", "fields"], name: "index_your_index_fields", unique: true, length: 191

I'd noticed when migrating Rails was doing this automatically, thought this wasn't committed to the repo. I had to run rails schema:load on another machine and it wasn't working, though adding the above solved this.

Hope that helps someone.

Mysql::Error: Specified key was too long; max key length is 767 bytes: CREATE INDEX

5 Answers5

Linked

Related