# MySQL Schema Error **URL:** \ **Category:** Rails\ **Created:** [June 22, 2008, 8:12pm UTC](https://www.ruby-forum.com/t/mysql-schema-error/141148 "2008-06-22T20:12:02Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![fusi-eon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/2acd7d/32.png) [@fusi-eon](https://www.ruby-forum.com/u/fusi-eon)\ **Post date:** [June 22, 2008, 8:12pm UTC](https://www.ruby-forum.com/t/mysql-schema-error/141148/1 "2008-06-22T20:12:02Z")
I’ve been stuck on this from the past 2-3 days. I deleted the folder, did everything right from the start, but it’s still showing the error. I’ve been following this (=\>[Ruby on Rails 2.1 - Migrations | Tutorialspoint](http://www.tutorialspoint.com/ruby-on-rails-2.1/rails-migrations.htm)) tutorial to at least get one rails app working but to no avail. This is the error I get when I enter the command rake db:migrate: 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’\> * * * Also, is there any way I can set MySQL as the default database rather than sqlite3 instead of entering the command ‘rails -d sql library’ wheneve i want to create an app with mysql as database ? ---
**Author:** ![fusi-eon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/2acd7d/32.png) [@fusi-eon](https://www.ruby-forum.com/u/fusi-eon)\ **Post date:** [June 22, 2008, 8:36pm UTC](https://www.ruby-forum.com/t/mysql-schema-error/141148/2 "2008-06-22T20:36:01Z")
Hi, Fusi Eon wrote: > Also, is there any way I can set MySQL as the > default database rather than sqlite3 You can specify mysql, but not set it as the default afaik. > instead of entering the command ‘rails -d sql library’ > wheneve i want to create an app with mysql as > database ? I don’t think I’ve ever seen that command used to create a Rails app. The one I know about that causes Rails to use MySQL instead of sqlite is: rails -d mysql your\_app\_name HTH, Bill ---
**Author:** ![fusi-eon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/2acd7d/32.png) [@fusi-eon](https://www.ruby-forum.com/u/fusi-eon)\ **Post date:** [June 22, 2008, 9:33pm UTC](https://www.ruby-forum.com/t/mysql-schema-error/141148/3 "2008-06-22T21:33:46Z")
Bill W. wrote: > I don’t think I’ve ever seen that command used to create a Rails app. > The > one I know about that causes Rails to use MySQL instead of sqlite is: > > rails -d mysql your\_app\_name Yep. Sorry. That’s what I meant to write. The ‘m’ alphabet on the keyboard is giving me trouble. What about the MySQL error? ---
**Author:** ![fusi-eon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/2acd7d/32.png) [@fusi-eon](https://www.ruby-forum.com/u/fusi-eon)\ **Post date:** [June 23, 2008, 1:16am UTC](https://www.ruby-forum.com/t/mysql-schema-error/141148/4 "2008-06-23T01:16:13Z")
Fusi Eon wrote: > What about the MySQL error? > > 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’\> hmm what version of MySQL do you have? works fine on mine, should just be running SQL equivalent to; CREATE TABLE `schema_migrations` ( `version` varchar(255) NOT NULL, UNIQUE KEY `unique_schema_migrations` (`version`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 does that work directly in your MySQL command client? ---
**Author:** ![fusi-eon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/2acd7d/32.png) [@fusi-eon](https://www.ruby-forum.com/u/fusi-eon)\ **Post date:** [July 1, 2008, 10:40am UTC](https://www.ruby-forum.com/t/mysql-schema-error/141148/5 "2008-07-01T10:40:57Z")
Jon Smillie wrote: > The problem is that MySQL has a limit of 767 bytes on the length of > columns used for keys. In UTF8 a varchar(255) consumes 1080 bytes. The > fix is to have the indexing done on a the first part of the key, by > limiting the key to so (say) the first 100 characters, by: > > CREATE UNIQUE INDEX `unique_schema_migrations` > ON `schema_migrations` (`version` (100) ) if that were the case, and MySQL required that to be set explicitly, then surely everyone using rails and utf8 would have this problem. That doesn’t seem to be the case. mysql\> CREATE TABLE `schema_migrations` ( `version` varchar(255) NOT NULL, UNIQUE KEY `unique_schema_migrations` (`version`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.04 sec) mysql\> show create table schema\_migrations; ±------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | ±------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------+ | schema\_migrations | CREATE TABLE `schema_migrations` ( `version` varchar(255) NOT NULL, UNIQUE KEY `unique_schema_migrations` (`version`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | ±------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql\> show variables where Variable\_name = “version”; ±--------------±--------+ | Variable\_name | Value | ±--------------±--------+ | version | 5.0.51b | ±--------------±--------+ 1 row in set (0.00 sec) ---
**Author:** ![fusi-eon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/2acd7d/32.png) [@fusi-eon](https://www.ruby-forum.com/u/fusi-eon)\ **Post date:** [July 2, 2008, 2:37am UTC](https://www.ruby-forum.com/t/mysql-schema-error/141148/6 "2008-07-02T02:37:03Z")
So what do you think is going on then? The fix I propose certainly fixed the problem for me. If I either (a) reduce the length of the ‘version’ field, or (b) reduce the number of chars the index takes into account (as proposed), I no longer get the ‘Specified key was too long’ error when running rake db:migrate or rake test:units. If you have a better fix, I could use it right now as I’m not too keen on having to tinker with the rails framework code to get my apps to run. By way of clarification - I’m using Rails 2.1 and MySQL 6.0.4-alpha for Win32. On Jul 1, 6:40 pm, Matthew R. Jacobs \ **Author:** ![fusi-eon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/2acd7d/32.png) [@fusi-eon](https://www.ruby-forum.com/u/fusi-eon)\ **Post date:** [July 2, 2008, 10:42am UTC](https://www.ruby-forum.com/t/mysql-schema-error/141148/7 "2008-07-02T10:42:24Z") Jon Smillie wrote: > By way of clarification - I’m using Rails 2.1 and MySQL 6.0.4-alpha > for Win32. > > On Jul 1, 6:40 pm, Matthew R. Jacobs \ **Author:** ![fusi-eon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/2acd7d/32.png) [@fusi-eon](https://www.ruby-forum.com/u/fusi-eon)\ **Post date:** [August 19, 2008, 1:47pm UTC](https://www.ruby-forum.com/t/mysql-schema-error/141148/8 "2008-08-19T13:47:37Z") Hi guys, was having trouble with the same issue using Ruby on Rails and thus used Jon Smillie’s idea as well and stuff… but nothing worked. I don’t know this problem is how old but since I came to this forum so can someone else therefore here is the detail that I found out: a. Other than the remedy Jon suggested everything else in his email is true. b. Problem exists for UTF 8 (mostly) because every letter is translated in 3 bytes. Therefore, varchar(255) translate to 765 bytes, which is okay. REMEDY c. If any column in any table of your database has some key set on it and you have specified the size of that column more than 255 then you will get this error. The easiest is to keep that size within that limit i.e. make it 255. I was having error because ‘email’ column of one of my table had the length/size set to 400 characters email varchar(400) --\> this was creating problem I changed this to email varchar(255) --\> this was the remedy. thanks Afras On Jul 2, 10:42 am, Matthew R. Jacobs \ **Author:** ![fusi-eon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/2acd7d/32.png) [@fusi-eon](https://www.ruby-forum.com/u/fusi-eon)\ **Post date:** [July 1, 2008, 4:31am UTC](https://www.ruby-forum.com/t/mysql-schema-error/141148/9 "2008-07-01T04:31:21Z") The problem is that MySQL has a limit of 767 bytes on the length of columns used for keys. In UTF8 a varchar(255) consumes 1080 bytes. The fix is to have the indexing done on a the first part of the key, by limiting the key to so (say) the first 100 characters, by: CREATE UNIQUE INDEX `unique_schema_migrations` ON `schema_migrations` (`version` (100) ) In ActiveRecord 2.1.0 the code which creates the index on the schema\_migrations table is in connection\_adapters/abstract/schema\_statements.rb on line 317. This calls the “add\_index” method defined on line 255. The actual sql statement used to create the index is on line 266. Change this to: execute “CREATE #{index\_type} INDEX #{quote\_column\_name(index\_name)} ON #{quote\_table\_name(table\_name)} (#{quoted\_column\_names} (100) )” ie: add the “(100)” after the column\_name, and this will instruct MySQL to only build the index on the first 100 characters of this column, well under the 767 byte limit. Matthew R. Jacobs wrote: > Fusi Eon wrote: > > > What about the MySQL error? > > > > 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’\> > > hmm > > what version of MySQL do you have? > works fine on mine, > should just be running SQL equivalent to; > > CREATE TABLE `schema_migrations` ( > `version` varchar(255) NOT NULL, > UNIQUE KEY `unique_schema_migrations` (`version`) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 > > does that work directly in your MySQL command client? ---
**Author:** ![fusi-eon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/2acd7d/32.png) [@fusi-eon](https://www.ruby-forum.com/u/fusi-eon)\ **Post date:** [November 17, 2008, 11:58am UTC](https://www.ruby-forum.com/t/mysql-schema-error/141148/10 "2008-11-17T11:58:39Z")
Afrasyab Bashir wrote: > Just to add, sadly enough the solution I wrote was true only for MySQL > version 5.0.6+ ☹ 'cos on MySQL 6.0 it’s still a problem. > > On Jul 2, 10:42�am, Matthew R. Jacobs \ **Author:** ![fusi-eon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/2acd7d/32.png) [@fusi-eon](https://www.ruby-forum.com/u/fusi-eon)\ **Post date:** [August 19, 2008, 1:48pm UTC](https://www.ruby-forum.com/t/mysql-schema-error/141148/11 "2008-08-19T13:48:12Z") Just to add, sadly enough the solution I wrote was true only for MySQL version 5.0.6+ ☹ 'cos on MySQL 6.0 it’s still a problem. On Jul 2, 10:42 am, Matthew R. Jacobs \ **Author:** ![fusi-eon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/2acd7d/32.png) [@fusi-eon](https://www.ruby-forum.com/u/fusi-eon)\ **Post date:** [January 19, 2009, 9:09am UTC](https://www.ruby-forum.com/t/mysql-schema-error/141148/12 "2009-01-19T09:09:56Z") Still facing the issue with MySQL 6.0 and Rails 2.1.2, filed: [http://bugs.mysql.com/bug.php?id=42193](http://bugs.mysql.com/bug.php?id=42193). -Arun Derder Ga wrote: > Afrasyab Bashir wrote: > > > Just to add, sadly enough the solution I wrote was true only for MySQL > > version 5.0.6+ ☹ 'cos on MySQL 6.0 it’s still a problem. > > > > On Jul 2, 10:42�am, Matthew R. Jacobs \ > By now, MySQL 6.0.7 and Rails 2.1.2 still have the problem. > > Mysql::Error: #42000Specified key was too long; max key length is 767 > bytes: CREATE UNIQUE INDEX `unique_schema_migrations` ON > `schema_migrations` (`version`) > > Which one should fix the problem? MySQL or Rails team? > This problem blocks Rails’ test. > ☹ ---
**Author:** ![fusi-eon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/2acd7d/32.png) [@fusi-eon](https://www.ruby-forum.com/u/fusi-eon)\ **Post date:** [February 5, 2009, 12:01am UTC](https://www.ruby-forum.com/t/mysql-schema-error/141148/13 "2009-02-05T00:01:22Z")
And still a problem today wih rails 2.2.2… Arun G. wrote: > Still facing the issue with MySQL 6.0 and Rails 2.1.2, filed: > [http://bugs.mysql.com/bug.php?id=42193](http://bugs.mysql.com/bug.php?id=42193). > > -Arun > > Derder Ga wrote: > > > Afrasyab Bashir wrote: > > > > > Just to add, sadly enough the solution I wrote was true only for MySQL > > > version 5.0.6+ ☹ 'cos on MySQL 6.0 it’s still a problem. > > > > > > On Jul 2, 10:42�am, Matthew R. Jacobs \ > > > By now, MySQL 6.0.7 and Rails 2.1.2 still have the problem. > > > > Mysql::Error: #42000Specified key was too long; max key length is 767 > > bytes: CREATE UNIQUE INDEX `unique_schema_migrations` ON > > `schema_migrations` (`version`) > > > > Which one should fix the problem? MySQL or Rails team? > > This problem blocks Rails’ test. > > ☹