Single transaction migrations


#1

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? :wink:

Greetings,

Esad


#2

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


#3

Esad H. 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/


#4

Esad H. 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/


#5

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


#6

Damon C. 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 :slight_smile:

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