Bug #42193 "max key length is 767 bytes" during db:migrate for a Rails app
Submitted: 19 Jan 2009 8:09 Modified: 19 Jan 2009 18:42
Reporter: Arun Gupta Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:6.0.8-alpha OS:Mac OS X
Assigned to: CPU Architecture:Any
Tags: rails jruby

[19 Jan 2009 8:09] Arun Gupta
Description:
Could not find an appropriate category to file Rails related bugs so filing it here.

Upgraded from MySQL 5.0.x to 6.0.8-alpha, created a simple Rails application, created a scaffold:

runner distance:float minutes:integer

The first invocation of db:migrate always gives the following error:

-- cut here --
~/samples/v3/HelloRest2 >~/tools/jruby/bin/jruby -S rake db:migrate
(in /Users/arungupta/samples/v3/HelloRest2)
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`)

(See full trace by running task with --trace)
-- cut here --

And the second invocation works fine:

~/samples/v3/HelloRest2 >~/tools/jruby/bin/jruby -S rake db:migrate
(in /Users/arungupta/samples/v3/HelloRest2)
== 20090117180023 CreateRunners: migrating ====================================
-- create_table(:runners)
   -> 0.5141s
== 20090117180023 CreateRunners: migrated (0.5153s) ===========================

How to repeat:
Steps listed above.
[19 Jan 2009 10:51] Sveta Smirnova
Thank you for the report.

Please provide output of SHOW CREATE TABLE schema_migrations
[19 Jan 2009 10:58] Arun Gupta
Complete development.log attached.

Attachment: development.log (application/octet-stream, text), 2.37 KiB.

[19 Jan 2009 10:58] Arun Gupta
The log file is now attached.
[19 Jan 2009 18:42] Sveta Smirnova
Thank you for the feedback.

Your schema uses UTF8 character set: there is SET NAMES UTF8 in the log. Table schema_migrations created as "CREATE TABLE `schema_migrations` (`version` varchar(255) NOT NULL) ENGINE=InnoDB" Engine InnoDB has limitation of 767 for key length (see http://dev.mysql.com/doc/refman/6.0/en/innodb-restrictions.html) Additionally maximum size of UTF8 character has been increased in version 6.0 from 3 to 4 bytes. For index is needed 255*4 = 1020 bytes instead of 255*3 = 765 bytes in previous versions. So I close the report as "Not a Bug", because this is not a bug of MySQL.