Rolling back a failed Rails migration

How do you roll back a failed rails migration? I would expect that rake db:rollback would undo the failed migration, but no, it rolls back the previous migration (the failed migration minus one). And rake db:migrate:down VERSION=myfailedmigration doesn't work either. I've ran into this a few times and it's very frustrating. Here's a simple test I made to duplicate the problem:

class SimpleTest < ActiveRecord::Migration
  def self.up
    add_column :assets, :test, :integer
    # the following syntax error will cause the migration to fail
    add_column :asset, :test2, :integer
  end

  def self.down
    remove_column :assets, :test
    remove_column :assets, :test2
  end
end

result:

==  SimpleTest: migrating =====================================================
-- add_column(:assets, :test, :integer)
   -> 0.0932s
-- add_column(:asset, :error)
rake aborted!
An error has occurred, all later migrations canceled:

wrong number of arguments (2 for 3)

ok, lets roll it back:

$ rake db:rollback
==  AddLevelsToRoles: reverting ===============================================
-- remove_column(:roles, :level)
   -> 0.0778s
==  AddLevelsToRoles: reverted (0.0779s) ======================================

huh? that was my last migration before SimpleTest, not the failed migration. (And oh, it would be nice if the migration output included the version number.)

So lets try running the down for the failed migration SimpleTest:

$ rake db:migrate:down VERSION=20090326173033
$

Nothing happens, and no output either. But maybe it ran the migration anyway? So lets fix the syntax error in the SimpleTest migration, and try to run it again.

$ rake db:migrate:up VERSION=20090326173033
==  SimpleTest: migrating =====================================================
-- add_column(:assets, :test, :integer)
rake aborted!
Mysql::Error: Duplicate column name 'test': ALTER TABLE `assets` ADD `test` int(11)

Nope. Obviously the migrate:down didn't work. It's not failing, it's just not executing.

No way to get rid of that duplicate table other than manually going into the database and removing it, and then running the test. There's got to be a better way than that.

Unfortunately, you must manually clean up failed migrations for MySQL. MySQL does not support transactional database definition changes.

Rails 2.2 includes transactional migrations for PostgreSQL. Rails 2.3 includes transactional migrations for SQLite.

This doesn't really help you for your problem right now, but if you have a choice of database on future projects, I recommend using one with support for transactional DDL because it makes migrations much more pleasant.

Update - this is still true in 2017, on Rails 4.2.7 and MySQL 5.7, reported by Alejandro Babio in another answer here.

To go to a specified version just use:

rake db:migrate VERSION=(the version you want to go to)

But if a migration fails part way, you'll have to clean it up first. One way would be:

• edit the down method of the migration to just undo the part of the up that worked
• migrate back to the prior state (where you started)
• fix the migration (including undoing your changes to the down)
• try again

OK, folks, here's how you actually do it. I don't know what the above answers are talking about.

1. Figure out which part of the up migration worked. Comment those out.
2. Also comment out/remove the part of the migration that broke.
3. Run the migration again. Now it will complete the non-broken parts of the migration, skipping the parts that have already been done.
4. Uncomment the bits of the migration you commented out in step 1.

You can migrate down and back up again if you want to verify that you've got it right now.

I agree that you should use PostgreSQL when possible. However, when you are stuck with MySQL, you can avoid most of these problems by trying your migration on your test database first:

rake db:migrate RAILS_ENV=test

You can revert to the previous state and try again with

rake db:schema:load RAILS_ENV=test

At 2015 with Rails 4.2.1 and MySQL 5.7, a failed migration can't be fixed with standard rake actions that Rails provide, as it was at 2009.

MySql does not support rollback of DDL statments (at MySQL 5.7 Manual). And Rails can not do anything with that.

Also, we can check how Rails is doing the job: A migration is wrapped in a transaction depending on how connection adapter respond to :supports_ddl_transactions?. After a search of this action at rails source (v 4.2.1), I found that only Sqlite3 and PostgreSql supports transactions, and by default it is not supported.

Edit Thus the current answer to the original question: A failed MySQL migration must be manually fixed.