Rails DB Migration Error: relation already exists

Question

I am getting an error when I try to migrate my db. I don't entirely remember how I got here, but I believe I:

1. created new branch, scaffolded 'Requests', db:migrated, switched back to master, and merged branch
2. created another branch, did some stuff, db:migrated, and everything was working fine.

3. pulled from heroku postgres database so i could test out if things worked with actual data. then tried db migrating, but gave me this error:

   rake db:migrate
   ==  CreateRequests: migrating =================================================
   -- create_table(:requests)
   NOTICE:  CREATE TABLE will create implicit sequence "requests_id_seq1" for serial column "requests.id"
   rake aborted!
   An error has occurred, this and all later migrations canceled:
   
   PG::Error: ERROR:  relation "requests" already exists
   : CREATE TABLE "requests" ("id" serial primary key, "title" character varying(255), "content" text, "category" character varying(255), "status" character varying(255), "requested_track_id" integer, "created_at" timestamp, "updated_at" timestamp) 
   

Any ideas?

Answer 1

I'm not sure exactly what pull strategy you used, but if we make two reasonable assumptions about your pull strategy:

1. it doesn't drop the database but just overwrites tables, since this requires less permissions.
2. it is operating in a sort of 'archive mode', meaning it doesn't drop tables on the destination just because they don't exist on the source. Think rsync; you have to specify --delete to get what might be your expected behavior with that utility.

If your steps are correct, then what happened is you overwrote the schema_migrations table, so Rails thinks you haven't added the table yet, but neither did your heroku pull drop the table because of #2 above.

Don't create another migration!!! This will fail on everyone elses' computer except yours, but will only run on yours once.

Instead, run rails dbconsole and execute something like DROP TABLE 'requests' (I forget the postgres syntax, might not be exactly that). Then you can run your migrations.

Answer 2

There is another way to avoid dropping a table with data in it.

What I do in those cases is to check which migration is failing.

Suppose you have a file db/migrate/20130908214222_create_requests.rb, and for some reason, ActiveRecord failed in the past when stored this migration in its "tracking system".

To be sure that this is the case, just find, in the schema_migrations table, a row containing a number like this example: 20130908214222

If that row does not exist, you just have to insert a new one:

INSERT INTO schema_migrations(
    version
) VALUES (
    20130908214222
);

Next time you run rake db:migrate, ActiveRecord will omit this step, and will continue migrating to end without complications.