Duplicate Table Error on Heroku - Table already exists but migration says down - works locally

Question

I created a users table and migrated it to Heroku and it worked fine. I tried to add an admin table but something went wrong and I'm not sure what happened. Both the users and admins table are working fine in my local environment but when I push the migration to Heroku I get an error that the users table was already created:

Migrating to DeviseCreateUsers (20151119150443) == 20151119150443 DeviseCreateUsers: migrating ================================ -- create_table(:users) PG::DuplicateTable: ERROR: relation "users" already exists : CREATE TABLE "users" ("id" serial primary key, "email" character varying(255) DEFAULT '' NOT NULL, "encrypted_password" character varying(255) DEFAULT '' NOT NULL, "reset_password_token" character varying(255), "reset_password_sent_at" timestamp, "remember_created_at" timestamp, "sign_in_count" integer DEFAULT 0 NOT NULL, "current_sign_in_at" timestamp, "last_sign_in_at" timestamp, "current_sign_in_ip" character varying(255), "last_sign_in_ip" character varying(255), "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL) rake aborted! StandardError: An error has occurred, this and all later migrations canceled:

PG::DuplicateTable: ERROR: relation "users" already exists : CREATE TABLE "users" ("id" serial primary key, "email" character varying(255) DEFAULT '' NOT NULL, "encrypted_password" character varying(255) DEFAULT '' NOT NULL, "reset_password_token" character varying(255), "reset_password_sent_at" timestamp, "remember_created_at" timestamp, "sign_in_count" integer DEFAULT 0 NOT NULL, "current_sign_in_at" timestamp, "last_sign_in_at" timestamp, "current_sign_in_ip" character varying(255), "last_sign_in_ip" character varying(255), "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL) /app/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.8/lib/active_record/connection_adapters/postgresql/database_statements.rb:128:in `async_exec'

This is what is says when I checked the status of the migrations locally:

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20151103020350  Create restaurants
   up     20151119150443  Devise create users
   up     20160809004949  Devise create admins

Status of migrations on heroku:

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20151030033303  ********** NO FILE **********
   up     20151102165952  ********** NO FILE **********
   up     20151102170119  ********** NO FILE **********
   up     20151103020350  Create restaurants
   up     20151103160832  ********** NO FILE **********
   up     20151103170143  ********** NO FILE **********
   up     20151105172014  ********** NO FILE **********
   up     20151114043509  ********** NO FILE **********
  down    20151119150443  Devise create users
  down    20160809004949  Devise create admins

When I go into the Heroku console it says I have 2 users so the users table was migrated yet the migration status is showing it as down.

I don't mind losing the data from the users table but I really don't want to lose the data from the restaurants table. Not sure how to fix this.

Devise creates the users table for you. You're only supposed to add whatever columns your application needs on top of what Devise creates on its own. In this case, you are indeed attempting to create a table that Devise already created. — MarsAtomic
@MarsAtomic - I used Devise to create the users table and it worked fine. I tried to add an admin table but it won't work because it is showing the user migration down for some reason even though I ran that migration. I don't know why this happened or how to fix it without losing all of my data. — Jennifer Rosse
The point is that you shouldn't have any migration for creating the Users table other than rails g devise user. If you have additional fields to add to the users table, then you'd have'rails g migration AddWhateverToUsers followed by rails g devise admin to add Devise for admins. It does appear that you're trying to create the users table twice according to the migrations you posted. Please review the Devise documentation. — MarsAtomic

Marco Sanfilippo Marco Sanfilippo · Accepted Answer · 2016-09-18T11:03:08

I think your DB already has the table, due to different migration or custom action.

Try this by command line, it worked for me!

rake db:drop
rake db:create
rake db:migrate

Duplicate Table Error on Heroku - Table already exists but migration says down - works locally

1 Answers