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.

Answer 1

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.