Rake aborted... table 'users' already exists

Question

I have created a database with devise and the nifty generator. I'm trying to make a new database with the nifty generator (rails g nifty:scaffold Asset user_id:integer), but when I try to migrate the database (rake db:migrate), I get the following error:

charlotte-dator:showwwdown holgersindbaek$ rake db:migrate
==  DeviseCreateUsers: migrating ==============================================
-- create_table(:users)
rake aborted!
An error has occurred, all later migrations canceled:

Mysql2::Error: Table 'users' already exists: CREATE TABLE `users` (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY, `email` varchar(255) DEFAULT '' NOT NULL, `encrypted_password` varchar(128) DEFAULT '' NOT NULL, `reset_password_token` varchar(255), `reset_password_sent_at` datetime, `remember_created_at` datetime, `sign_in_count` int(11) DEFAULT 0, `current_sign_in_at` datetime, `last_sign_in_at` datetime, `current_sign_in_ip` varchar(255), `last_sign_in_ip` varchar(255), `name` varchar(255), `created_at` datetime, `updated_at` datetime) ENGINE=InnoDB

Tasks: TOP => db:migrate
(See full trace by running task with --trace)

I'm following a tutorial and have quite a hard time understanding why this happens. Can anyone explain what is going on?

Solution 1

The migration is trying to create a table that already exists in your database.

Try to remove the user table from your database. Something went wrong with you migration process. You should also compare your schema.rb version with your db/migrate/*.rb files.

Clarification:

It seems that many SO users don't agree with my reply, either because they consider it inaccurate or not recommended.

Removing a table is always destructive, and I think that everyone understands that.

I should have mentioned add_column, since the table was being created in another migration file.

Solution 2

In your create_users migration (APP_ROOT/db/migrate/..), add drop_table :users right before create_table :users and run rake db:migrate. It will remove the users table before recreating it. You can remove that line of code after running this migration so it doesn't give you errors later on. Just a small fix if you dont have UI access to a database (on heroku, for example).

Solution 3

You need to drop that table from the sql lite console (You will lost all the data contained in it)

1. Access the sql lite console, type in terminal
   mysql <DB NAME HERE>

2. Drop table (dont forget the last ; (semicolon))
   drop table table_name;

3. run db:migrate again
   bin/rake db:migrate

Hope it helps, it worked for me

Solution 4

If you wanna play safe and don't want to lose any data then you can check if the table exists in your database.

class DeviseCreateUsers < ActiveRecord::Migration
  def up
    if table_exists?(:users)
      # update or modify columns of users table here accordingly.
    else
      # create table and dump the schema here
    end
  end

  def down
    # same approach goes here but in the reverse logic
  end
end

Solution 5

If you know the database was created properly, you can just comment out the creation part of the migration code. For example:

Class ActsAsVotableMigration < ActiveRecord::Migration
  def self.up
#    create_table :votes do |t|
#
#      t.references :votable, :polymorphic => true
#      t.references :voter, :polymorphic => true
#
#      t.boolean :vote_flag
#
#      t.timestamps
#    end
#
#    add_index :votes, [:votable_id, :votable_type]
#    add_index :votes, [:voter_id, :voter_type]
  end

  def self.down
    drop_table :votes
  end
end

If the table was created, but later commands weren't completed for some reason, you can just leave the later options for example:

Class ActsAsVotableMigration < ActiveRecord::Migration
  def self.up
#    create_table :votes do |t|
#
#      t.references :votable, :polymorphic => true
#      t.references :voter, :polymorphic => true
#
#      t.boolean :vote_flag
#
#      t.timestamps
#    end

    add_index :votes, [:votable_id, :votable_type]
    add_index :votes, [:voter_id, :voter_type]
  end

  def self.down
    drop_table :votes
  end
end

If you don't have any significant data in your database to preserve however you can just have it drop the table and all the data and create it fresh. For example (notice the "drop_table :votes", in the self.up):

class ActsAsVotableMigration < ActiveRecord::Migration
  def self.up
    drop_table :votes
    create_table :votes do |t|

      t.references :votable, :polymorphic => true
      t.references :voter, :polymorphic => true

      t.boolean :vote_flag

      t.timestamps
    end

    add_index :votes, [:votable_id, :votable_type]
    add_index :votes, [:voter_id, :voter_type]
  end

  def self.down
    drop_table :votes
  end
end

View more solutions