Error creating a table executing rake db:migrate in Rails 2.0.2 and MySql 5.1

I am trying to implement a rails Recipe application from the site http://oreilly.com/ruby/archive/rails-revisited.html . They have given a good example highlighting the significance of Rails for Agile Development. The example in this post is implemented in Rails version lesser than 1.x.

I am trying to implement the same in version 2.0.2. They seem to have implement it using MySql 4.1 Database. I am currently using mysql-client 5.1 on the Ubuntu 10.04 platform. I am facing certain errors When I am trying to use rake db:migrate.

I initially encountered errors even while manually creating the database tables. Seems that the syntax for creating the DB tables has changed for certain attributes. For e.g. The command in the post to create the Recipes Table didn't work for me

create table recipes (
    id                     int            not null auto_increment,
    category_id            int            not null,
    title                  varchar(100)   not null default '',
    description            varchar(255)   null,
    date                   date           null,
    instructions           text           null,
    constraint fk_recipes_categories foreign key (category_id) references categories(id),
    primary key(id)
) engine=InnoDB;

Until I changed few things in the syntax to suit version 5.1. The command which did the trick for me was:

create table recipes ( id int not null auto_increment, category_id int not null, title varchar(100) not null default '', description varchar(255) null, date date null, instructions text null, primary key (id), constraint fk_recipes_categories foreign key (category_id) references categories(id)) engine=InnoDB;

Now back to my question. I was actually trying to do some kind of reverse engineering just in order to understand how scaffold works. Inititally I wanted to understand its significance so I tried doing things manually..

I then tried doing things using the scaffold command and I am currently stuck with rake db:migrate. I know it might seem a bit too much a reference and a too big a question , but I guess you would need to check this out so that you could probably tell me what change do I need to incorporate in order to get my tables created using rake db:migrate

mohnish@pc146724-desktop:~/railsprg/2011/Jan11/12jan11_recipes/cookbook2$ script/generate scaffold category id:integer name:string
/usr/lib/ruby/gems/1.8/gems/rails-2.0.2/lib/rails_generator/lookup.rb:207:Warning: Gem::SourceIndex#search support for Regexp patterns is deprecated, use #find_name
      exists  app/models/
      exists  app/controllers/
      exists  app/helpers/
      create  app/views/categories
      exists  app/views/layouts/
      exists  test/functional/
      exists  test/unit/
      create  app/views/categories/index.html.erb
      create  app/views/categories/show.html.erb
      create  app/views/categories/new.html.erb
      create  app/views/categories/edit.html.erb
      create  app/views/layouts/categories.html.erb
      create  public/stylesheets/scaffold.css
  dependency  model
/usr/lib/ruby/gems/1.8/gems/rails-2.0.2/lib/rails_generator/lookup.rb:207:Warning: Gem::SourceIndex#search support for Regexp patterns is deprecated, use #find_name
      exists    app/models/
      exists    test/unit/
      exists    test/fixtures/
      create    app/models/category.rb
      create    test/unit/category_test.rb
      create    test/fixtures/categories.yml
      create    db/migrate
      create    db/migrate/001_create_categories.rb
      create  app/controllers/categories_controller.rb
      create  test/functional/categories_controller_test.rb
      create  app/helpers/categories_helper.rb
       route  map.resources :categories
mohnish@pc146724-desktop:~/railsprg/2011/Jan11/12jan11_recipes/cookbook2$ script/generate scaffold recipe id:integer category_id:integer title:string description:text date:date instructions:text
/usr/lib/ruby/gems/1.8/gems/rails-2.0.2/lib/rails_generator/lookup.rb:207:Warning: Gem::SourceIndex#search support for Regexp patterns is deprecated, use #find_name
      exists  app/models/
      exists  app/controllers/
      exists  app/helpers/
      create  app/views/recipes
      exists  app/views/layouts/
      exists  test/functional/
      exists  test/unit/
      create  app/views/recipes/index.html.erb
      create  app/views/recipes/show.html.erb
      create  app/views/recipes/new.html.erb
      create  app/views/recipes/edit.html.erb
      create  app/views/layouts/recipes.html.erb
   identical  public/stylesheets/scaffold.css
  dependency  model
/usr/lib/ruby/gems/1.8/gems/rails-2.0.2/lib/rails_generator/lookup.rb:207:Warning: Gem::SourceIndex#search support for Regexp patterns is deprecated, use #find_name
      exists    app/models/
      exists    test/unit/
      exists    test/fixtures/
      create    app/models/recipe.rb
      create    test/unit/recipe_test.rb
      create    test/fixtures/recipes.yml
      exists    db/migrate
      create    db/migrate/002_create_recipes.rb
      create  app/controllers/recipes_controller.rb
      create  test/functional/recipes_controller_test.rb
      create  app/helpers/recipes_helper.rb
       route  map.resources :recipes
mohnish@pc146724-desktop:~/railsprg/2011/Jan11/12jan11_recipes/coo开发者_开发技巧kbook2$ rake db:create
(in /home/mohnish/railsprg/2011/Jan11/12jan11_recipes/cookbook2)
"db/development.sqlite3 already exists"
mohnish@pc146724-desktop:~/railsprg/2011/Jan11/12jan11_recipes/cookbook2$ rake db:create
(in /home/mohnish/railsprg/2011/Jan11/12jan11_recipes/cookbook2)
mohnish@pc146724-desktop:~/railsprg/2011/Jan11/12jan11_recipes/cookbook2$ rake db:migrate
(in /home/mohnish/railsprg/2011/Jan11/12jan11_recipes/cookbook2)
== 1 CreateCategories: migrating ==============================================
-- create_table(:categories)
rake aborted!
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), `name` varchar(255) DEFAULT NULL, `created_at` datetime DEFAULT NULL, `upd' at line 1: CREATE TABLE `categories` (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY(11), `name` varchar(255) DEFAULT NULL, `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL) ENGINE=InnoDB

(See full trace by running task with --trace)
mohnish@pc146724-desktop:~/railsprg/2011/Jan11/12jan11_recipes/cookbook2$ rake db:migrate --trace
(in /home/mohnish/railsprg/2011/Jan11/12jan11_recipes/cookbook2)
** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:migrate
== 1 CreateCategories: migrating ==============================================
-- create_table(:categories)
rake aborted!
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), `name` varchar(255) DEFAULT NULL, `created_at` datetime DEFAULT NULL, `upd' at line 1: CREATE TABLE `categories` (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY(11), `name` varchar(255) DEFAULT NULL, `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL) ENGINE=InnoDB
/usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract_adapter.rb:150:in `log'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/mysql_adapter.rb:281:in `execute'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/schema_statements.rb:104:in `create_table'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/mysql_adapter.rb:416:in `create_table'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb:285:in `send'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb:285:in `method_missing'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb:265:in `say_with_time'
/usr/lib/ruby/1.8/benchmark.rb:293:in `measure'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb:265:in `say_with_time'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb:281:in `method_missing'
./db/migrate//001_create_categories.rb:3:in `up_without_benchmarks'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb:219:in `send'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb:219:in `migrate'
/usr/lib/ruby/1.8/benchmark.rb:293:in `measure'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb:219:in `migrate'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb:348:in `migrate'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb:339:in `each'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb:339:in `migrate'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb:307:in `up'
/usr/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/migration.rb:298:in `migrate'
/usr/lib/ruby/gems/1.8/gems/rails-2.0.2/lib/tasks/databases.rake:85
/usr/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:636:in `call'
/usr/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:636:in `execute'
/usr/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:631:in `each'
/usr/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:631:in `execute'
/usr/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:597:in `invoke_with_call_chain'
/usr/lib/ruby/1.8/monitor.rb:242:in `synchronize'
/usr/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:590:in `invoke_with_call_chain'
/usr/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:583:in `invoke'
/usr/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2051:in `invoke_task'
/usr/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2029:in `top_level'
/usr/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2029:in `each'
/usr/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2029:in `top_level'
/usr/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2068:in `standard_exception_handling'
/usr/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2023:in `top_level'
/usr/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2001:in `run'
/usr/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:2068:in `standard_exception_handling'
/usr/lib/ruby/gems/1.8/gems/rake-0.8.7/lib/rake.rb:1998:in `run'
/usr/lib/ruby/gems/1.8/gems/rake-0.8.7/bin/rake:31
/usr/bin/rake:19:in `load'
/usr/bin/rake:19
mohnish@pc146724-desktop:~/railsprg/2011/Jan11/12jan11_recipes/cookbook2$

The db/migrate/001_create_categories.rb & db/migrate/002_create_recipes.rb looks like this:

class CreateCategories < ActiveRecord::Migration
  def self.up
    create_table :categories do |t|
      t.integer :id
      t.string :name

      t.timestamps
    end
  end

  def self.down
    drop_table :categories
  end
end

class CreateRecipes < ActiveRecord::Migration
  def self.up
    create_table :recipes do |t|
      t.integer :id
      t.integer :category_id
      t.string :title
      t.text :description
      t.date :date
      t.text :instructions

      t.timestamps
    end
  end

  def self.down
    drop_table :recipes
  end
end

Thanks for you help..

You don't need t.integer :id in your migrations, you get that for free. Remove it.

http://guides.rubyonrails.org/v2.3.8/migrations.html is a great resource.