Migrations and indexes on failure - db permanently polluted

rubyonrails-talk
1

It's unclear to me whether or not migrations are currently (rails 2.0.2) fully transactional. At least in the case of indexes, they don't appear to be. If the second index create fails below:

class CreateIndexes < ActiveRecord::Migration def self.up   add_index :ontable, :onacolumn   add_index :anothertable, :onanothercolumn end

def self.down   remove_index :ontable, :onacolumn   remove_index :anothertable, :onanothercolumn end

it appears migrations are now permanently stuck (can't move forward, can't rollback), since it will now fail on all forward migrations since the first index is already created, and rollback can't occur since the db never actually rolled fully forward to update the schema version.

Am I missing something (other than manual repair/cleanup of incomplete indexes) that would fix this?

In truth, my primary goal was to use mysql foreign keys (constraints + index), but I was waiting until that's integrated more.

2

It's unclear to me whether or not migrations are currently (rails
2.0.2) fully transactional.

They are not in any way transactional (with mysql at least, they can't
be, because things like alter table implicitly commit the current
transaction)

At least in the case of indexes, they don't appear to be. If the second index create fails below:

class CreateIndexes < ActiveRecord::Migration def self.up add_index :ontable, :onacolumn add_index :anothertable, :onanothercolumn end

def self.down remove_index :ontable, :onacolumn remove_index :anothertable, :onanothercolumn end

it appears migrations are now permanently stuck (can't move forward, can't rollback), since it will now fail on all forward migrations
since the first index is already created, and rollback can't occur since the db never actually rolled fully forward to update the schema version.

Am I missing something (other than manual repair/cleanup of incomplete indexes) that would fix this?

nope.

Fred

3

Thank you. I was hoping there was rails (not nec. db - db support would be something like forced create, similar to create_table) support for this, but I suppose one could break up the above into individual migrations, then slowly increment forward (or back) as necessary.