Deal with a drop_table migration for nonexistent table on heroku

Question

I created a 'campaigns' table in my postgres db in my local env. I never committed the changes and thus never pushed that to production on heroku.

When I changed my mind about needing that table, I foolishly deleted the migration manually (I know now never to do this). However, since the migration already had run, I created a 21041211003219_drop_campaigns_table migration to drop the table from the local db.

Now when I run heroku run rake db:migrate on production I get the following error:

Migrating to DropCampaignsTable (20141211003219)                                                                                                                                                                                                     
== 20141211003219 DropCampaignsTable: migrating ===============================                                                                                                                                                                      
-- drop_table(:campaigns)                                                                                                                                                                                                                            
PG::UndefinedTable: ERROR:  table "campaigns" does not exist                                                                                                                                                                                         
: DROP TABLE "campaigns"                                                                                                                                                                                                                             
rake aborted!                                                                                                                                                                                                                                        
StandardError: An error has occurred, this and all later migrations canceled:                                                                                                                                                                        

PG::UndefinedTable: ERROR:  table "campaigns" does not exist                                                                                                                                                                                         
: DROP TABLE "campaigns"/app/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.0/lib/active_record/connection_adapters/postgresql/database_statements.rb:128:in `async_exec'                                                                            
/app/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.0/lib/active_record/connection_adapters/postgresql/database_statements.rb:128:in `block in execute'             

I understand that it is trying to run a drop table migration for a table that it never knew existed but I'm unsure on how to handle this.

I tried deleting the drop table migration, pushing to production and running it but it still tried to run that migration.

After rolling back that commit I've been thinking about manually creating a new migration with a datestamp prior to the 21041211003219_drop_campaigns_table migration that re-creates the table but then I feel like I'll be left with the table existing in the local postgres db (since the 21041211003219_drop_campaigns_table has already run locally).

Do I go ahead with the manual creation and then try to re-run the drop migration?

Can someone please advise me on the best course of action here?

Answer 1

You can turn off the error PG::UndefinedTable: ERROR: table "campaigns" does not exist when deleting an already non-existent table by adding if_exists: true to drop_table:

def up
  drop_table(:campaigns, if_exists: true)
end

This option is sparsely documented, but exists in Rails since early 2015 and I think it's cleaner than writing SQL by hand as it also handles compatibility with various DB adapters.

Answer 2

Just delete the offending migration and get on with more interesting things. Clearly there's no "create table" migration for that table so the "drop table" migration has no right to exist. If you really don't want to delete the migration, you can rewrite it using raw SQL:

def up
  connection.execute 'drop table if exists campaigns'
end

The if exists does exactly what you'd think: it only tries to drop the table if it is there.

About this stuff:

I thought you weren't supposed to delete migrations so that anyone else that works on the project in the future can run them and have an accurate DB.

Migrations are meant to get an existing instance of an application from state-A to state-B. If you're starting a fresh instance, you'll be using schema.rb (or structure.sql) to initialize the application and no migrations should be needed. Also, once a migration has been applied to every instance of an application, that migration really isn't needed anymore unless you want to try and go backwards. I tend to clear out old migrations a couple weeks after a release to keep the clutter down.