"Identifier name is too long" When Indexing Multiple Columns In MySQL With ActiveRecord

Posted by Tres Thu, 03 Jul 2008 00:29:00 GMT

MySQL has a 100 character limit on the length of the name of an index, which ActiveRecord will cross when creating an index for a table that has multiple foreign keys and other data that is to be indexed. If you’re seeing a message like the following when building indexes:

Mysql::Error: #42000Identifier name 'index_duper_on_us_id_and_hair_id_and_leaf_id_and_number_id_and_super_id_and_created_at_and' is too long: 
CREATE  INDEX `index_duper_on_us_id_and_hair_id_and_leaf_id_and_number_id_and_super_id_and_created_at_and_completed` ON `duper` 
(`us_id`, `hair_id`, `leaf_id`, `number_id`, `super_id`, `created_at`, `completed`)

just manually assign a name to the index by appending :name => “[MANUALINDEXNAME_HERE]” to the index you’re creating. So in our example above, the line in the migration would look like this:

  add_index :duper, [:us_id, :hair_id, :leaf_id, :number_id, :super_id, :created_at, :completed], :name => "duper_index"

Posted in ,  | Tags , ,

RSS feed for this post

Comments are disabled