Failure to Create Table on Heroku -- Migration works locally but fails on Heroku

Question

I'm working on a Rails 3 app and am trying to run a migration. I am trying to create a table called Songs and I was able to successfully create the table and migrate locally. However, when I push the code to Heroku and migrate there, the error I encounter is:

** Execute db:migrate
==  CreateSongs: migrating ====================================================
-- create_table(:songs)
NOTICE:  CREATE TABLE will create implicit sequence "songs_id_seq1" for serial column "songs.id"
rake aborted!
An error has occurred, this and all later migrations canceled:

PG::DuplicateTable: ERROR:  relation "songs" already exists
: CREATE TABLE "songs" ("id" serial primary key, "solo_cello" character varying(255), "created_at" timestamp, "updated_at" timestamp) /app/vendor/bundle/ruby/1.9.1/gems/activerecord-3.1.12/lib/active_record/connection_adapters/postgresql_adapter.rb:605:in `async_exec'

I realize this error stems from the table already existing in the database, however when I run

 heroku run "bundle exec rake db:schema:dump && cat db/schema.rb"

I see that the schema version is at ActiveRecord::Schema.define(:version => 20130915155113) and the migration to create the Songs model occurs after this migration (it is 20140506043817).

Here's is a truncated version of my local schema:

 ActiveRecord::Schema.define(:version => 20140511155456) do
 [other tables...] 
create_table "songs", :force => true do |t|
  t.string   "solo_cello"
  t.datetime "created_at"
  t.datetime "updated_at"
  t.string   "solo_violin"
  t.string   "duo_one"
  t.string   "duo_two"
  t.string   "trio_a_one"
  t.string   "trio_a_two"
  t.string   "trio_a_three"
  t.string   "trio_b_one"
  t.string   "trio_b_two"
  t.string   "trio_b_three"
  t.string   "quartet_one"
  t.string   "quartet_two"
  t.string   "quartet_three"
  t.string   "quartet_four"
end

Here are my questions:

How does the table already exist if the schema on the database is at version 20130915155113 if the migration that creates the Song table (20140506043817) comes later?
What do I need to do to run a successful migration? I realize I can drop the database (this is the staging environment) but would greatly prefer not to do that on production.
Why do migrations work fine on my machine locally but not on Heroku?

Navigate to your application folder and run heroku pg:psql and after you are connected list the tables with \dtt and look in the output after your songs table and then use \dts to list sequences. — cristian
– cristian, Commented May 11, 2014 at 17:00
Thanks--I ran those commands and see the table. Still unsure why the table exists if the schema version points to a migration before the creation of the Songs table. — Tom Hanlon
– Tom Hanlon, Commented May 11, 2014 at 17:31
Did you by any chance restore a previous backup of the database on Heroku where the songs table did not yet exist? It would overwrite the content of existing tables, but leave the songs table in place, unless you explicitly recreate the database. — fivedigit
– fivedigit, Commented May 11, 2014 at 19:19
@fivedigit, Good assumption. Yes, I dropped the staging table several days ago and backed up with a backup of production data. What might you recommend for next steps? — Tom Hanlon
– Tom Hanlon, Commented May 11, 2014 at 20:10

fivedigit · Accepted Answer · 2014-05-11 20:25:41Z

2

Sounds like your songs table might have been left behind as effect of restoring a database backup. Restoring a database backup on Heroku will only rewrite tables which exist in the backup, but it won't drop tables which didn't yet exist when the backup was created.

There are several steps to follow to resolve the issue:

Drop the table. You can manually drop the table by running heroku pg:psql in the terminal, and then executing the query DROP TABLE songs;. If this was the only problematic table, you can now run your migrations.
Drop the database. In case you have a database backup and you can afford some downtime, you can also drop the database by running heroku pg:reset from the terminal, then restoring your database backup and running your migrations.

answered May 11, 2014 at 20:25

fivedigit

18.7k6 gold badges59 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Hanlon Over a year ago

Dropping the Songs table fixed the issue.

Collectives™ on Stack Overflow

Failure to Create Table on Heroku -- Migration works locally but fails on Heroku

Here are my questions:

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Here are my questions:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related