Error after rake migrate:db

Rails
1

I’m getting the error

Mysql::Error: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near ‘(11), first_name varchar(255) DEFAULT NULL, last_name
varchar(255) DEFAULT N’ at line 1: CREATE TABLE undergraduates (id
int(11) DEFAULT NULL auto_increment PRIMARY KEY(11), first_name
varchar(255) DEFAULT NULL, last_name varchar(255) DEFAULT NULL)
ENGINE=InnoDB

and I used --trace and it found

/home/michael/work/student/config/…/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:128:in
log' /home/michael/work/student/config/../vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:243:inexecute’
/home/michael/work/student/config/…/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb:104:in
create_table' /home/michael/work/student/config/../vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb:353:increate_table’
/home/michael/work/student/config/…/vendor/rails/activerecord/lib/active_record/migration.rb:275:in
send' /home/michael/work/student/config/../vendor/rails/activerecord/lib/active_record/migration.rb:275:inmethod_missing’
/home/michael/work/student/config/…/vendor/rails/activerecord/lib/active_record/migration.rb:259:in
say_with_time' /usr/lib/ruby/1.8/benchmark.rb:293:inmeasure’
/home/michael/work/student/config/…/vendor/rails/activerecord/lib/active_record/migration.rb:259:in
say_with_time' /home/michael/work/student/config/../vendor/rails/activerecord/lib/active_record/migration.rb:273:inmethod_missing’
./db/migrate//003_create_undergraduates.rb:3:in real_up' /home/michael/work/student/config/../vendor/rails/activerecord/lib/active_record/migration.rb:212:insend’
/home/michael/work/student/config/…/vendor/rails/activerecord/lib/active_record/migration.rb:212:in
migrate' /usr/lib/ruby/1.8/benchmark.rb:293:inmeasure’
/home/michael/work/student/config/…/vendor/rails/activerecord/lib/active_record/migration.rb:212:in
migrate' /home/michael/work/student/config/../vendor/rails/activerecord/lib/active_record/migration.rb:335:inmigrate’
/home/michael/work/student/config/…/vendor/rails/activerecord/lib/active_record/migration.rb:330:in
each' /home/michael/work/student/config/../vendor/rails/activerecord/lib/active_record/migration.rb:330:inmigrate’
/home/michael/work/student/config/…/vendor/rails/activerecord/lib/active_record/migration.rb:297:in
up' /home/michael/work/student/config/../vendor/rails/activerecord/lib/active_record/migration.rb:288:inmigrate’
/home/michael/work/student/config/…/vendor/rails/railties/lib/tasks/databases.rake:4
/usr/lib/ruby/1.8/rake.rb:387:in call' /usr/lib/ruby/1.8/rake.rb:387:inexecute’
/usr/lib/ruby/1.8/rake.rb:387:in each' /usr/lib/ruby/1.8/rake.rb:387:inexecute’
/usr/lib/ruby/1.8/rake.rb:357:in invoke' /usr/lib/ruby/1.8/rake.rb:350:insynchronize’
/usr/lib/ruby/1.8/rake.rb:350:in invoke' /usr/lib/ruby/1.8/rake.rb:1924:inrun’
/usr/lib/ruby/1.8/rake.rb:1924:in each' /usr/lib/ruby/1.8/rake.rb:1924:inrun’
/usr/bin/rake:4

I wasn’t sure whether the method missing is the reason behind the
error… and I’m not really sure what to do if it is (just learning
rails for a class at UC Berkeley). Any help would be appreciated!

Thanks
Michael

2

OK, figured it out. Apparently MYSQL doesn’t like it when you try to add
an “id” as “id”.

Michael

3

Michael Mike wrote:

OK, figured it out. Apparently MYSQL doesn’t like it when you try to add
an “id” as “id”.

Michael

When having rake db:migrate errors, sending the code for the migration
that it’s choking on helps us too…

But let’s take your problem and discovered solution a step further.

Primary key fields are created by default in your migration by Rails.
They are, also by default, named “id”.

Any field that is a combination of a table name, an underscore and the
string “id” will be seen as a foreign key to Rails.

Ex.

Let’s assume a person owns many things, but a thing can only be owned by
1 person… simplistic but a decent example I think.

Imagine your migrations have these.

001_create_people.rb

class CreatePeople < ActiveRecord::Migration
def self.up
create_table :people do |t|
t.column :name, :string, :null => false;
end
end

def self.down
drop_table :people
end
end

This creates the following table. (id is created automatically and
tagged as a primary auto increment key)

People Table
-=-=-=-=-=-=-=
id
name

002_create_things.rb

class CreateThings < ActiveRecord::Migration
def self.up
create_table :things do |t|
t.column :people_id, :integer, :null => false;
t.column :description :strig, :null => false;
end
end

def self.down
drop_table :things
end
end

Creates this with again id done automatically…

Things Table.
-=-=-=-=-=-=-=-
id
description
people_id

If you have your model defined as (1 to many relationship)… and this
is actual complete and valid code. (Just untested lol)

class Person < ActiveRecord::Base
has_many :things
end

class Thing < ActiveRecord::Base
belongs_to :person
end

This uses the named field ( people.id -> things.people_id ) in your
migration and the relationship mapping (has_many / belongs_to) in your
models to know the primary key and foreign key pairs.

Let’s assume each table has this data:

People Table data.
id: 1
name: John

Things Table data. (2 rows)
id: 1
people_id: 1
description: “A car”
id: 2
people_id: 1
description: “A truck”

In your code (or the script/console which should be your new best friend
while learning rails) you can see the results from this…

person = Person.find(1) # Finds the row in People with id 1.
puts “My name is: #{ person.name }”
person.things.each() { # using lazy loading it get’s the things for a
person to satisfy the relationship.
puts “I own: #{ t.description }”
}

Being this:

My name is: John
I own: A car
I own: A truck

I hope this was not a waste of time expanding on WHY Mysql (actually
RAILS) didn’t like your column named ID. When you dig deeper in to rails
you will learn ways to change the ‘id’ name for your primary to use
something else and also how to supress the creating of surrogate keys in
your models.

John

4

I hope this was not a waste of time expanding on WHY Mysql (actually
RAILS) didn’t like your column named ID. When you dig deeper in to rails
you will learn ways to change the ‘id’ name for your primary to use
something else and also how to supress the creating of surrogate keys in
your models.

John

Definately not a waste of time, very thankful for any help I can get,

Michael