MySQL Bugs: #42193: "max key length is 767 bytes" during db:migrate for a Rails app

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:	MacOS
Assigned to:		CPU Architecture:	Any
Tags:	rails jruby

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.

Thank you for the report.

Please provide output of SHOW CREATE TABLE schema_migrations

Complete development.log attached.

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

The log file is now attached.

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.