Ruby Forum Ruby on Rails > single transaction migrations

Hi!

It seems that migration doesn't use single transcation to execute the 
needed migrations for the database upgrade, so if I have database at 
version 5, and I wrote some migrations 6..10, and error occurs while 
executing migration 7, the database stays in state 6?

I also think that migration taks could use some more verbosity, for 
example if migration fails, there's no reference to migration number nor 
statement, only something like:

rake aborted!
uninitialized constant SalePoint

Are there any plans for improving on this or maybe even some code in 
trunk already? ;-)

Greetings,

Esad

As I discovered last night, you can wrap your up and down methods in a
transaction like so..

def up
  transaction do
    stuff
  end
end

It's not done automatically because some actions don't work in a
transaction (like creating a table in MySQL, I think).

Better error handling would be nice, but it probably needs to be handled
by the author.  Perhaps a 'rescue' at the end of the up or down method
would be sufficient.

_Kevin

Esad Hajdarevic wrote:

> It seems that migration doesn't use single transcation to execute the 
> needed migrations for the database upgrade, so if I have database at 
> version 5, and I wrote some migrations 6..10, and error occurs while 
> executing migration 7, the database stays in state 6?

DDL (table operations) each happen inside a single COMMIT.  You can wrap 
data operations inside a transaction.do .  This is an argument for 
testing your migrations one at a time and keeping steps simple.  Once 
you are confident in them, you can try running them in a big series.

> I also think that migration taks could use some more verbosity, for 
> example if migration fails, there's no reference to migration number nor 
> statement, only something like:
Install this:

http://svn.jamisbuck.org/rails-plugins/verbose_migrations/README

You'll be a little happier.  A little more spring in your step.

-damon
http://damonclinkscales.com/

Hi!

It is really true that MySQL/Inno doesn't support transactions on DDL 
statements (I've read somewhere that pg and mssql do!), but why not 
simply offer the user the possibility to go back to original version if 
an error occurs? If I add migrations 5,6 and migration 7 fails, I could 
simply call down() on 6,5 and be there where I began? Of course, there 
are IrreversibleMigrations, but they could be skipped or used could be 
warned?

I checked the ActiveRecords::Migration and ::Migrator source, they seem 
pretty clear and straightforward, this wouldn't be much of a problem to 
implement. What do you think would be the best thing to do? To submit 
patch or to make a plugin?

Greetings,

Esad

Esad Hajdarevic wrote:
> It is really true that MySQL/Inno doesn't support transactions on DDL 
> statements (I've read somewhere that pg and mssql do!), but why not 
> simply offer the user the possibility to go back to original version if 
> an error occurs? If I add migrations 5,6 and migration 7 fails, I could 
> simply call down() on 6,5 and be there where I began? Of course, there 
> are IrreversibleMigrations, but they could be skipped or used could be 
> warned?
My understanding is that each DDL statement is in its own transaction, 
even for non-MySQL databases.  But I'm not sure on that.   For MySQL, 
even with InnoDB, the DDL-based statements are executed one at a time.

> I checked the ActiveRecords::Migration and ::Migrator source, they seem 
> pretty clear and straightforward, this wouldn't be much of a problem to 
> implement. What do you think would be the best thing to do? To submit 
> patch or to make a plugin?
If you can make it work, I would suggest starting with a plugin.  This 
is a great way to test new functionality and prove to the core team that 
it could work as a patch.

I believe that it is this way in Rails is because the underlying 
databases aren't offering DDL statements to be grouped into a 
transaction and rolled back.  If you find a good reference which says 
otherwise, please post back!

-damon
http://damonclinkscales.com/

Damon Clinkscales wrote:
> My understanding is that each DDL statement is in its own transaction, 
> even for non-MySQL databases.  But I'm not sure on that.   For MySQL, 
> even with InnoDB, the DDL-based statements are executed one at a time.

(snip)

> I believe that it is this way in Rails is because the underlying 
> databases aren't offering DDL statements to be grouped into a 
> transaction and rolled back.  If you find a good reference which says 
> otherwise, please post back!

I was involved in a similair discussion (to which replies stopped...) a 
while back. Postgres seems to put alter table in a transaction just 
fine. The search is disabled (of the web-bridge), but my post about it 
should be findable through other archives. Just search for posts by me, 
there aren't many :)

Anyway, I just tried this quick test with psql 8.0.4:

===========
sicirec_dev=# begin;
BEGIN
sicirec_dev=# create table bla();
CREATE TABLE
sicirec_dev=# select * from bla;

--
(0 rows)

sicirec_dev=# rollback;
ROLLBACK
sicirec_dev=# select * from bla;
ERROR:  relation "bla" does not exist
sicirec_dev=#
===========

We (my collegue and me) load migrations from seperate .pgsql files, 
which we start and end with BEGIN and COMMIT. The corresponding "up" 
method, to run a .psql file with the same name as the .rb file (without 
extension of course), would be:

def self.up
  # Execute the contents of the accompanying .pgsql file
  execute File.open( __FILE__.gsub(/\.rb$/, '.pgsql') ).read
end