rails: table already exists on new git branch

I'm trying to play around with different messaging gems that all use devise. Therefore, after installing Devise on master branch of git, I checked out a new branch to try one gem "rails messaging" https://github.com/frodefi/rails-messaging, and when I couldn't get it to work, I committed the changes to that branch, and then I went back to master and checked out a new branch to try another gem "mailboxer" https://github.com/ging/mailboxer/tree/master/lib. In the gemfile of this new branch there was no trace of the gems from the first branch, however when I tried rake db:migrate for this second branch after installing the gems, I got this error message, which seemed to suggest that a table from the first branch was interfering with the rake of the second branch, unless by doing

 rails g mailboxer:install

It automatically runs the rake db:migrate. However, the README says that I have to run rake db:migrate, so I'm not sure...

rake aborted!
An error has occurred, this and all later migrations canceled:

SQLite3::SQLException: table "conversations" already exists: CREATE TABLE "conversations" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "subject" varchar(255) DEFAULT '', "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL

)

Can anyone suggest how I can work around this? I don't really know what commands to run to figure this out.

This is the scheme.rb file. Based on the index "messaging users" i'm guessing it's the setup created by the rails messaging gem (on the first branch) but I'm not totally sure. I looked at the source code on git but got kinda lost because I'm not very experienced.

ActiveRecord::Schema.define(:version => 20120302041333) do

  create_table "conversations", :force => true do |t|
    t.string   "subject",    :default => ""
    t.datetime "created_at",                 :null => false
    t.datetime "updated_at",                 :null => false
  end

  create_table "messaging_users", :force => true do |t|
    t.string   "email",                                 :default => "", :null => false
    t.string   "encrypted_password",     :limit => 128, :default => "", :null => false
    t.string   "reset_password_token"
    t.datetime "reset_password_sent_at"
    t.datetime "remember_created_at"
    t.integer  "sign_in_count",                         :default => 0
    t.datetime "current_sign_in_at"
    t.datetime "last_sign_in_at"
    t.string   "current_sign_in_ip"
    t.string   "last_sign_in_ip"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  add_index "messaging_users", ["email"], :name => "index_messaging_users_on_email", :unique => true
  add_index "messaging_users", ["reset_password_token"], :name => "index_messaging_users_on_reset_password_token", :unique => true

  create_table "notifications", :force => true do |t|
    t.string   "type"
    t.text     "body"
    t.string   "subject",              :default => ""
    t.integer  "sender_id"
    t.string   "sender_type"
    t.integer  "conversation_id"
    t.boolean  "draft",                :default => false
    t.datetime "updated_at",                              :null => false
    t.datetime "created_at",                              :null => false
    t.integer  "notified_object_id"
    t.string   "notified_object_type"
    t.string   "notification_code"
  end

  add_index "notifications", ["conversation_id"], :name => "index_notifications_on_conversation_id"

  create_table "receipts", :force => true do |t|
    t.integer  "receiver_id"
    t.string   "receiver_type"
    t.integer  "notification_id",                                  :null => false
    t.boolean  "read",                          :default => false
    t.boolean  "trashed",                       :default => false
    t.boolean  "deleted",                       :default => false
    t.string   "mailbox_type",    :limit => 25
    t.datetime "created_at",                                       :null => false
    t.datetime "updated_at",                                       :null => false
  end

  add_index "receipts", ["notification_id"], :name => "index_receipts_on_notification_id"

  create_table "users", :force => true do |t|
    t.string   "email",                                 :default => "", :null => false
    t.string   "encrypted_password",     :limit => 128, :default => "", :null => false
    t.string   "reset_password_token"
    t.datetime "reset_password_sent_at"
    t.datetime "remember_created_at"
    t.integer  "sign_in_count",                         :default => 0
    t.datetime "current_sign_in_at"
    t.datetime "last_sign_in_at"
    t.string   "current_sign_in_ip"
    t.string   "last_sign_in_ip"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.string   "name"
  end

  add_index "users", ["email"], :name => "index_users_on_email", :unique => true
  add_index "users", ["reset_password_token"], :name => "index_users_on_reset_password_token", :unique => true

end

Answers


The issue here is that while your code, schemas, gems, and migrations are stored in git, the database itself is not. So when you switch between branches, the database still retains its state.

These solutions will all work:

  1. drop and re-seed your database

    rake db:reset
    
  2. migrate down (while on the old branch) to undo the migration. I'm not sure if the gem installed a migration in db/migrate or ran a migration directly from the gem, so this isn't as easy, but is better for data integrity if you don't want to wipe your data.

  3. reload the schema you have stored in git (this has the same effects as the first one)

    rake db:schema:load
    
  4. use an sqlite database for now - while not appropriate for production, if you check in the database to git, it will behave as you're wishing your current database behaved. I should warn you though that large files like this will be a pain. Better to use one of the other approaches.


Yeah switching branches with different migration levels is a mess. There is no 'one-liner' to do this properly. There are four things that can be out of sync. You'll need to:

1) Determine what migrations your branch has:

(look at the contents of db/migrations)

2) Determine what your app thinks your database looks like:

(look at db/schema.rb)

3) Determine what migrations your database thinks it has by looking at the schema_migrations table:

~> rails db console

(opens up your db console, in my case mysql...)

mysql> SELECT * FROM schema_migrations; # don't forget the ';' at the end!

(outputs big huge list of migration timestamps)

4) Look at the actual tables to see if the migrations have been applied.

mysql> describe table_name;

(outputs the column names, see if they have been updated per the migration)

5) Now the part that requires thinking. There are a couple possible scenarios. I can only speak to the one I ran into:

  • Your migration files (1), your schema.rb(2) and your actual tables (4) match, but your schema_migrations table (3) is missing the migration timestamp. This was my issue.
    • If you don't care about the data: rake db:reset. It will trash your whole database and recreate the structure from scratch
  • Please suggest other scenarios with solutions..

Need Your Help

Prevent an object from being paged out (VirtualLock equivalent)

c# .net windows memory-management

How would one go about keep an object in memory such that it won't be paged out by the OS in .Net?