Ruby Forum Ruby on Rails > MySQL Schema Error

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
(=>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 ?

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

Bill Walton 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?

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?

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 Rudy 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?

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)

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 Rudy Jacobs <rails-mailing-l...@andreas-

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 Rudy Jacobs <rails-mailing-l...@andreas-

Aah... MySQL 6
maybe that's the issue,
more rigid indexing maybe...

Worth some investigating,

you are using InnoDB and not crazy Falcon?

cos it'd be poo if rails doesnt work with mysql 5.1 / 6.0

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 Rudy Jacobs <rails-mailing-l...@andreas-

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 Rudy Jacobs <rails-mailing-l...@andreas-

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 Rudy Jacobs <rails-mailing-l...@andreas-

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.
:(