CODEWITHSUNDEEP
ruby-on-railsheroku
superbot
superbot

Reputation: 461

Heroku db:migrate says a column that does not exist does not exist and recommends renaming it

I ran: 

heroku run rake db:migrate

And the error is:

ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column "book" does not exist

And it recommends:

: ALTER TABLE "books" RENAME COLUMN "book" TO "want"

"books" is a table. "want" is a column of that table.

This is printed three times.

My schema:

  create_table "books", force: :cascade do |t|
    t.text "want"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.bigint "user_id"
    t.text "starting"
    t.index ["user_id"], name: "index_books_on_user_id"
  end

In the middle of the error, it says:

/app/db/migrate/20190801032030_change_book_book_column_name.rb:3:in `change'

This is something I used long time ago to change the column name. The content is:

  def change
    rename_column :books, :book, :want
  end

I can't remember what I may have done with this so that I am getting this error now. Can you please help?

UPDATE

Frustrated, I deleted that migration file. Then I ran again. This time, I got this error: 

Table 'posts' has no foreign key for {:to_table=>"books", :column=>"books_id"}

It also mentions a file: 

/app/db/migrate/20190806222313_remove_book_ref_to_posts.rb:3:in `change'

Whose content is:

  def change
    remove_reference :posts, :books, foreign_key: true
  end

Schema for posts:

  create_table "posts", force: :cascade do |t|
    t.text "text"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.bigint "book_id"
    t.index ["book_id"], name: "index_posts_on_book_id"
  end

...what can I do?

Upvotes: 1

Views: 1484

Answers (2)

Erowlin
Erowlin

Reputation: 10287

This may happens for (at least) two different reasons:

  • The column / table was renamed manually using a SQL client, by connecting directly to the database
  • The migration 20190806222313_remove_book_ref_to_posts was deployed and "migrated", but you somewhat updated the file and re-deployed to Heroku.

For the first point, I suggest to not update the database schema outside a migration. This makes your application being outside the 12 factors scope, which makes replayability hard (a key point for 12 factors applications). For example, if you re-deploy your application to a new environment and a new database, you would have to run the migrations once, see them fails, re-do the manual schema update you did on your previous database, then run again the migrations. Very painful.

For the second point, I've learned the hard way and have been in this situation more than once in the past. Here is what I do now that is 100% effective:

  1. If you work with multiple branches, and deploy a specific branch to Heroku for testing, always rollback first your database migrations. (heroku run rails db:rollback STEP=XXX) where STEP is the number of migrations to be rollbacked.
  2. NEVER update a migration file once it's been deployed or shared with a co-worker. It makes re-playing the previous migration painful and can introduce downtime.
  3. If you are in one of 1) or 2) situation and you still need to re-run the migration for any reason, you will have to manually remove the corresponding row in the table schema_migrations. This table has a single column which is the ID of all the migrations that were applied (migrated) to your database. So, let's say I have the migration file "123_my_migration.rb" that I deployed to Heroku and migrated, a new row will exists in the schema_migrations table with 123 as value. It means that your migration will never be executed again, unless you remove this row from the table.

It's a bit of discipline, but once you got it, you will really enjoy every minute of your discipline and will be confident enough with every deployment and migration you have to do. Good luck!

Upvotes: 1

superbot
superbot

Reputation: 461

The solution was to delete that migration also and then creating another migration to delete the existing want column, and then creating another migration to create a new want column, and then deleting that migration which deleted the want column. Basically getting rid of all discrepancies -- giving no excuse for Heroku to say a column doesn't exist.

Upvotes: 0

Related Questions