Ezequiel Ramiro Ezequiel Ramiro - 6 months ago 46
Ruby Question

heroku rake migrate db error relation "post" doesn't exist

So.. im trying to migrate my db to heroku for the first time, doing:

heroku rake db:migrate


but im getting this horrible error that i cant resolve !

** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Invoke db:load_config (first_time)
** Execute db:load_config
** Execute db:migrate
ActiveRecord::SchemaMigration Load (1.8ms) SELECT "schema_migrations".* FROM "schema_migrations"
Migrating to CreateComments (20150528110329)
(1.6ms) BEGIN
== 20150528110329 CreateComments: migrating ===================================
-- create_table(:comments)
(9.4ms) CREATE TABLE "comments" ("id" serial primary key, "name" character varying, "body" character varying, "post_id" integer, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL)
(3.8ms) CREATE INDEX "index_comments_on_post_id" ON "comments" ("post_id")
(6.4ms) ALTER TABLE "comments" ADD CONSTRAINT "fk_rails_2fd19c0db7"
FOREIGN KEY ("post_id")
REFERENCES "posts" ("id")

PG::UndefinedTable: ERROR: relation "posts" does not exist
: ALTER TABLE "comments" ADD CONSTRAINT "fk_rails_2fd19c0db7"
FOREIGN KEY ("post_id")
REFERENCES "posts" ("id")

(1.6ms) ROLLBACK
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:


what i can understand is that its trying to reference from a table that doesnt exist yet.. but I dont know how to create my post table first...

schema.rb>

ActiveRecord::Schema.define(version: 20150622053408) do

create_table "comments", force: :cascade do |t|
t.string "name"
t.string "body"
t.integer "post_id"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end

add_index "comments", ["post_id"], name: "index_comments_on_post_id"

create_table "posts", force: :cascade do |t|
t.string "title"
t.text "subtitle"
t.text "content"
t.string "img"
t.string "category"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end

create_table "taggings", force: :cascade do |t|
t.integer "tag_id"
t.integer "taggable_id"
t.string "taggable_type"
t.integer "tagger_id"
t.string "tagger_type"
t.string "context", limit: 128
t.datetime "created_at"
end

add_index "taggings", ["tag_id", "taggable_id", "taggable_type", "context", "tagger_id", "tagger_type"], name: "taggings_idx", unique: true
add_index "taggings", ["taggable_id", "taggable_type", "context"], name: "index_taggings_on_taggable_id_and_taggable_type_and_context"

create_table "tags", force: :cascade do |t|
t.string "name"
t.integer "taggings_count", default: 0
end

add_index "tags", ["name"], name: "index_tags_on_name", unique: true

end


I tried switching in every way possible my code but with no results.

Hope that someone could help me out... thanks for everything!

Answer

I'm of the opinion that somewhere along the line one of your migrations were edited (which would be the problem if not properly rolledback first etc) and is now causing a major problem. And since you need to keep the data and can't do a full db:rollback I would say there is maybe one option I can think of unless someone else has a better idea. I never recommend editing migration files but I've had to do it in the past in certain circumstances. Do the following and make sure you keep in mind that any changes to the db can't be undone with a simple
git checkout .

I would create a new migration file by hand, not by using the rails generator and give it a timestamp that is a couple seconds before the timestamp of the Comments table migration file. I would then create the Posts table in that migration and edit the other existing migration that created the Posts table to just add it's columns. You must then go into your database (Not your rails console but your database which is probably Postgres) and insert this migration into the "schema_migrations" table. This schema_migrations table is automagically setup by rails whenever you generate a migration and run db:migrate. You'll notice the entries in this schema_migrations table are listed by "Version" where the version is the timestamp of the migration in your migration folder. You must then add the migration file timestamp to your schema_migrations table and it should be good to go.

UPDATE: IF you don't care about losing the data you can rollback all migrations, edit your migrations files and then re-run them.

bundle exec rake db:migrate:status #Look at the first version id and copy it.

bundle exec rake db:rollback VERSION=theVersionNumberYouCopiedHere

Then re check the migration statuses with the first command and make sure all migration statuses are listed as "down". This will have dropped all tables etc. Then go into your migration files and remove the referencing of your posts table from the Comments table that was giving you the issues. Then re-run all migrations again. Then add your posts_id reference to the comments table as follows by generating and running the following migration:

rails g migration AddPostToComments post:references

Looking in that migration file you should see something like this:

class AddPostToCommentss < ActiveRecord::Migration
  def change
    add_reference :comments, :post, index: true
    add_foreign_key :comments, :posts
  end
end

Now run bundle exec rake db:migrate and you should be good to go. Commit all the changes to git and then push to heroku!