# Single transaction migrations **URL:** \ **Category:** Rails\ **Created:** [March 1, 2006, 12:52pm UTC](https://www.ruby-forum.com/t/single-transaction-migrations/75182 "2006-03-01T12:52:50Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Esad\_H](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/3da27b/32.png) [@Esad\_H](https://www.ruby-forum.com/u/Esad_H)\ **Post date:** [March 1, 2006, 12:52pm UTC](https://www.ruby-forum.com/t/single-transaction-migrations/75182/1 "2006-03-01T12:52:50Z")
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 ---
**Author:** ![Esad\_H](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/3da27b/32.png) [@Esad\_H](https://www.ruby-forum.com/u/Esad_H)\ **Post date:** [March 1, 2006, 3:07pm UTC](https://www.ruby-forum.com/t/single-transaction-migrations/75182/2 "2006-03-01T15:07:55Z")
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 ---
**Author:** ![Esad\_H](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/3da27b/32.png) [@Esad\_H](https://www.ruby-forum.com/u/Esad_H)\ **Post date:** [March 1, 2006, 6:42pm UTC](https://www.ruby-forum.com/t/single-transaction-migrations/75182/3 "2006-03-01T18:42:15Z")
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](http://svn.jamisbuck.org/rails-plugins/verbose_migrations/README) You’ll be a little happier. A little more spring in your step. -damon > **[Damon Clinkscales](https://damonc.onuniverse.com/)** > > Damon Clinkscales ---
**Author:** ![Esad\_H](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/3da27b/32.png) [@Esad\_H](https://www.ruby-forum.com/u/Esad_H)\ **Post date:** [March 3, 2006, 9:25am UTC](https://www.ruby-forum.com/t/single-transaction-migrations/75182/4 "2006-03-03T09:25:57Z")
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 > **[Damon Clinkscales](https://damonc.onuniverse.com/)** > > Damon Clinkscales ---
**Author:** ![Esad\_H](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/3da27b/32.png) [@Esad\_H](https://www.ruby-forum.com/u/Esad_H)\ **Post date:** [March 1, 2006, 8:19pm UTC](https://www.ruby-forum.com/t/single-transaction-migrations/75182/5 "2006-03-01T20:19:22Z")
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 ---
**Author:** ![Esad\_H](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/3da27b/32.png) [@Esad\_H](https://www.ruby-forum.com/u/Esad_H)\ **Post date:** [March 3, 2006, 4:59pm UTC](https://www.ruby-forum.com/t/single-transaction-migrations/75182/6 "2006-03-03T16:59:46Z")
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 🙂 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