I am BARRY HESS > Blog

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 ().]