Reverting Migrations (self.drop) Issue

I created an ran a migration today. Here is how my database looked to begin:

mysql> show tables;
+--------------------------------------+
| Tables_in_books_development          |
+--------------------------------------+
| journals                             |
| schema_info                          |
+--------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from schema_info;
+---------+
| version |
+---------+
|      19 |
+---------+
1 row in set (0.00 sec)

Then, I ran this migration:

class CreateThemes < ActiveRecord::Migration
  def self.up
    create_table :themes do |t|
      t.column :name, :string
      t.column :stylesheet_path, :string
      t.column :thumbnail_path, :string
      t.column :journals_count, :integer, :default => 0
    end
    #add_column :journals, :theme_id, :integer
  end

  def self.down
    drop_table :themes
    remove_column :journals, :theme_id
  end
end

Notice how I have the add_column method commented out. Running Rake yields this:

C:\rails_apps\books>rake db:migrate
(in C:/rails_apps/books)
== CreateThemes: migrating ====================================================
-- create_table(:themes)
   -> 0.1200s
== CreateThemes: migrated (0.1200s) ===========================================

As you can see, the column is not added to the journals table. Here's what my database looks like now:

mysql> show tables;
+--------------------------------------+
| Tables_in_books_development          |
+--------------------------------------+
| journals                             |
| schema_info                          |
| themes                               |
+--------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from schema_info;
+---------+
| version |
+---------+
|      20 |
+---------+
1 row in set (0.00 sec)

Well, I really need that column added to journals, so I better revert that migration via Rake, uncomment the add_column method, and re-Rake. Here's what happens:

C:\rails_apps\books>rake db:migrate version=19
(in C:/rails_apps/books)
== CreateThemes: reverting ====================================================
-- drop_table(:themes)
   -> 0.0500s
-- remove_column(:journals, :theme_id)
rake aborted!
Mysql::Error: Can't DROP 'theme_id'; check that column/key exists: ALTER TABLE journals DROP `theme_id`

(See full trace by running task with --trace)

Oops! The self.down method is crapping out because there is no column in journals called theme_id. (Of course, we knew that.) Let's checkout the database now:

mysql> show tables;
+--------------------------------------+
| Tables_in_books_development          |
+--------------------------------------+
| journals                             |
| schema_info                          |
+--------------------------------------+
2 rows in set (0.01 sec)

mysql> select * from schema_info;
+---------+
| version |
+---------+
|      20 |
+---------+
1 row in set (0.00 sec)

Do you see that schema_info still says version 20. I presume that the last thing a migration does is update the schema_info.version with the idea being that we don't modify the version until we sure that the migration has succeeded. The problem with that is that migrations aren't transactional--that is, they can complete or fail partially, yet the schema_info is updated as though it were a transaction. I don't know how Rails fixes this, but it's something worth knowing about.

Anyhow, at this point my database is in limbo...neither in version 19 nor 20. When I run

rake db:migrate

nothing happens because as far as Rake can tell, the DB is up-to-date. But, I can't run

rake db:migrate version=19

either because Rake will error out because there is no theme table to drop.

Okay, so the short-term solution to this problem is to clean-up the database manually. In my case, everything was actually reverted in the self.drop, but Rake doesn't know that. In your case you may have more manual clean-up to do. So, all I needed to do was update schema_info.version to 19.