insane.dreamer insane.dreamer - 7 months ago 60
MySQL Question

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

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


== 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 2015, on Rails 4.2.1 and MySQL 5.7, reported by Alejandro Babio in another answer here.