This project is archived and is in readonly mode.

#522 ✓duplicate
Jon Smillie

Rails 2.1 MySQL 6.0 schema_migrations index problem

Reported by Jon Smillie | July 1st, 2008 @ 04:53 AM | in 2.x

This is the error I get when I enter the command rake db:migrate:

rake aborted!

Mysql::Error: #42000Specified key was too long; max key length is 767

bytes: CREATE UNIQUE INDEX 'unique_schema_migrations' ON

'schema_migrations' <'version'>

The problem is that MySQL has a limit of 767 bytes on the length of

columns used for keys. In UTF8 a varchar(255) consumes 1080 bytes. The

fix is to have the indexing done on a the first part of the key, by

limiting the key to so (say) the first 100 characters, by:

CREATE UNIQUE INDEX `unique_schema_migrations`

ON `schema_migrations` (`version` (100) )

In ActiveRecord 2.1.0 the code which creates the index on the

schema_migrations table is in

connection_adapters/abstract/schema_statements.rb on line 317. This

calls the "add_index" method defined on line 255. The actual sql

statement used to create the index is on line 266. Change this to:

execute "CREATE #{index_type} INDEX #{quote_column_name(index_name)}

ON #{quote_table_name(table_name)} (#{quoted_column_names} (100) )"

ie: add the "(100)" after the column_name, and this will instruct MySQL

to only build the index on the first 100 characters of this column, well

under the 767 byte limit.

Comments and changes to this ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href=""></a>

People watching this ticket