Rails Migration Max Key Length Error in MySQL
I created a has_many_polymorphs table for a project that contains announcer_id, announcer_type, announeable_id and announceable_type. All those types are a necessity since we’re working double-polymorphic here. I wanted an index, which is unique on those four combined values. This was in my migration:
add_index :announcements, [:announcer_id, :announcer_type, :announceable_id, :announceable_type], :unique => true, :name => 'index_announcement_polymorphs'
Didn’t have any errors migrating this locally (mysql 5.0.41 on OSX) or on my cohort’s machine (presumably 5.x). However, we received this error when deploying to production (mysql 4.1.11 on Debian):
Mysql::Error: Specified key was too long; max key length is 1024 bytes: CREATE UNIQUE INDEX `index_announcement_polymorphs` ON announcements (`announcer_id`, `announcer_type`, `announceable_id`, `announceable_type`)
I suspected the issue is described here:
I guess you are using version 4.1 with UTF8 as default character set. Each char will used upto 3 bytes, so need to multiply your length with 3 and this will exceed 1024 bytes.
See, by default Rails creates 256-character strings. So just in terms of indexed strings, of which I had two, there were 1536 [(256+256)3] bytes. So I went back and updated my table migration to limit string length on the type fields to 30 characters ().]