Migrations don't really support transactions

Rails
1

I read in various places that although migrations aren’t transactional,
all
you need to do is wrap you migration method in “ActiveRecord::
Base.connection.transaction do”
to make the self.up or self.down transactional. In my experience (Rails
+
PostgreSQL), this doesn’t work very well. If my migration hits an
exception, any tables that were touched remain modified. Even if I fix
the
error in my migration, I can’t just re-run the migration since my schema
doesn’t match what the migration is expecting. I also can’t migrate
down
since it seems like the up migration wasn’t recorded.

Here is a link to another write-up about this same problem.
http://www.height1percent.com/articles/2006/02/18/on-the-need-for-more-robust-rails-migrations

Am I missing something or is there really no way to protect the DB from
failed migrations? It can be a real pain to go into the DB an manually
fix
everything in order to run the migration after an exception. Does
anyone
have any suggestions other than being more careful when I write
migrations?

Thanks,

Eden

2

On May 10, 2006, at 1:59 PM, Eden B. wrote:

Am I missing something or is there really no way to protect the DB
from failed migrations? It can be a real pain to go into the DB an
manually fix everything in order to run the migration after an
exception. Does anyone have any suggestions other than being more
careful when I write migrations?

You could test the migrations against a snapshot of production.

You could use a database that supports transactional DDL, like
PostgreSQL.

–
– Tom M.

3

Hi Tom,

Thanks for the suggestions.

I am working with my production database, so it isn’t really an issue of
lost data as much as it is an issue of lost time and extra frustration.

I know there is the --dry-run option, but I don’t think that will catch
my
runtime exceptions or errors.

What do you mean by “You could use a database that supports
transactional
DDL, like
PostgreSQL.”? I am using PostgreSQL, but I haven’t found a way to get
migrations to respect my request that the changes to the database be
transactional. It seems like once rake hits an exception it just dies
without trying to roll anything back.

Thank you,

Eden

4

No problem at all. I have been “using” transactions with migrations
based
on a blog article for a month now. All the time wondering if I had hit
a
strange bug when the transactions “failed”. Of course it looks like
they
never worked in the first place.

5

On May 10, 2006, at 2:38 PM, Eden B. wrote:

What do you mean by “You could use a database that supports
transactional DDL, like
PostgreSQL.”? I am using PostgreSQL, but I haven’t found a way to
get migrations to respect my request that the changes to the
database be transactional. It seems like once rake hits an
exception it just dies without trying to roll anything back.

I thought I had seen blog entries about using transactions within
migrations.

A quick Google run only found people lamenting the fact that they
cannot.

Weird. Sorry for the misinformation.

–
– Tom M.

6

At this point, I would be really happy if I had a rake task that would
do
the following:

  1. Clear all data and tables from my development db
  2. Reset my migration status to blank (instead of VERSION X)
  3. Optionally run rake migrate with the VERSION=X value I specify

On the surface this doesn’t sound too difficult to do and would save me
the
hassle of manually fixing my db each time migrate fails.

7

Tom,

Thank you for the sh script for resetting my database. I frequently
develop
on Windows, so I decided to figure out how to clear my db using a rake
task. The attached rake task drops all tables in the database and
resets
the version number in schema_info. I haven’t really tested it
carefully, so
you are taking your chances if you use this. It is very bare-bones
right
now, but possible improvements could include:

  1. Take VERSION=X as an input to allow for a quick jump back up to any
    migration level.
  2. Rewrite this as a mixin module so that all my migrations could have
    the
    option to run this code when an exception occurs during a migration. If
    done smartly, the version that failed could be noted, this code
    executed,
    and the migration automatically run up to the last know good version
    (i.e.
    exception happens on version 15, so this clears db and runs migration up
    to
    version 14).