I have a new migration file created that adds some tables and fills one
with some data:
create_table :sound_styles do |t|
t.column :name, :string, :null => false
end
create_table :show_types do |t|
t.column :name, :string, :null => false
end
create_table :countries do |t|
t.column :name, :string, :null => false
end
ActionView::Helpers::FormOptionsHelper::COUNTRIES.each do |c|
Country.create :name => c # Fill in some content
end
So far so good… but I experienced that when the code, that fills in
the countries into the table throws an exception, then the whole
migration is only particularly succeeded, what means, that the stuff,
that succeeded, remains in the DB, and the rest just doesn’t happen.
This is very annoying, because I have to correct the DB schema by hand
(the down method is also not reliable because maybe it relies on some
stuff in the up method that failed before).
My question: why is the migration not reverted to the previous state
when an exception occurs? I know, this is maybe very difficult to do
because of all the possible random coding stuff that’s likely to be in
the up method…
But how can I avoid such problems in the future? Should I only create a
single table per migration, or something like that? Or shouldn’t I do
custom coding stuff in the migrations file as I did it now with the
countries?
t.column :name, :string, :null => false
the countries into the table throws an exception, then the whole
the up method…
But how can I avoid such problems in the future? Should I only create a
single table per migration, or something like that? Or shouldn’t I do
custom coding stuff in the migrations file as I did it now with the
countries?
Thanks for help.
Joshua
Personally, I prefer to keep it to one change per migration… but that
said, since a migration is only Ruby + Rails code, can’t you put in a
transaction block? I have no idea if transaction blocks will prevent
the problem you mention… but hopefully someone else will be able to
answer that.
But, like I said, I prefer to keep it to one change per migration. I’m
not sure what the ‘best practice’ is, though…
DDL[1](create, alter, drop) cannot be held under transactions via any
database I know. For this reason it’s almost impossible to place the
DDL under a failure - rails would have to reverse the .up. If you
properly code your .down, you should be able to use it to correct the
migration.
DML[2] (updates, inserts and such should be able to be rolled back).
I think (those more rails-fluent can confirm) that migrations operate
under a transaction. So any DML you run should be rolled back upon
exception.
Thanks so far, guys. About the 1-table-per-migration-file-thingie:
When I think about the fact that newer versions of Rails automatically
create a migration file when using script/generate model xxx then I
guess it’s assumed to use only one table per migration file.
DDL[1](create, alter, drop) cannot be held under transactions via any
database I know.
I’m not sure about other databases, but with PostgreSQL most DDL
statements are transaction safe:
test=> BEGIN;
BEGIN
test=> \d blah
Did not find any relation named “blah”.
test=> CREATE TABLE blah (id INT);
CREATE TABLE
test=> \d blah
Table “public.blah”
Column | Type | Modifiers
--------±--------±----------
id | integer |
test=> ROLLBACK;
ROLLBACK
test=> \d blah
Did not find any relation named “blah”.
test=> BEGIN;
BEGIN
test=> \d division
Table “public.division”
Column | Type | Modifiers
--------±----------------------±------------------------------------------------------
id | integer | not null default
nextval(‘division_id_seq’::regclass)
name | character varying(30) | not null
Indexes:
“division_pkey” PRIMARY KEY, btree (id)
“division_name_key” UNIQUE, btree (name)
test=> DROP TABLE division;
DROP TABLE
test=> \d division
Did not find any relation named “division”.
test=> ROLLBACK;
ROLLBACK
test=> \d division
Table “public.division”
Column | Type | Modifiers
--------±----------------------±------------------------------------------------------
id | integer | not null default
nextval(‘division_id_seq’::regclass)
name | character varying(30) | not null
Indexes:
“division_pkey” PRIMARY KEY, btree (id)
“division_name_key” UNIQUE, btree (name)
test=> BEGIN;
BEGIN
test=> ALTER TABLE division ADD COLUMN blah INT;
ALTER TABLE
test=> \d division
Table “public.division”
Column | Type | Modifiers
--------±----------------------±------------------------------------------------------
id | integer | not null default
nextval(‘division_id_seq’::regclass)
name | character varying(30) | not null
blah | integer |
Indexes:
“division_pkey” PRIMARY KEY, btree (id)
“division_name_key” UNIQUE, btree (name)
test=> ROLLBACK;
ROLLBACK
test=> \d division
Table “public.division”
Column | Type | Modifiers
--------±----------------------±------------------------------------------------------
id | integer | not null default
nextval(‘division_id_seq’::regclass)
name | character varying(30) | not null
Indexes:
“division_pkey” PRIMARY KEY, btree (id)
“division_name_key” UNIQUE, btree (name)
What I’m wondering about is where this is documented. I’ve looked for it
in the PostgreSQL docs, but was unable to find any notice that said
that or which DDL statements are transactional.
Just to add something in–I found that if you have a problem with your
migration, instead of going in and changing to db back by hand, you can
just
set the schema version to the one you just created (the one with the
errors)
and then do rake migrate with the previous (working) working version
Say you’ve created a new migration, and it’s version 5, but when you run
rake migrate, you have errors. Just do the following to revert back to
version 4:
$ ruby script/runner ‘ActiveRecord::Base.connection.execute(“UPDATE
schema_info SET version = 5”)’
…some output…
$ rake migrate VERSION=4
…some output (runs the self.down of version 5)…
In my experience, PostgreSQL properly handled DDL transactions when I
ran
under a Linux distribution, but not under Windows. Did anyone else have
this experience? Just throwing this out to save some frustration if it
doesn’t seem to work for you.
Just to add something in–I found that if you have a problem with your
migration, instead of going in and changing to db back by hand, you can
just
set the schema version to the one you just created (the one with the
errors)
and then do rake migrate with the previous (working) working version
Say you’ve created a new migration, and it’s version 5, but when you run
rake migrate, you have errors. Just do the following to revert back to
version 4:
$ ruby script/runner ‘ActiveRecord::Base.connection.execute(“UPDATE
schema_info SET version = 5”)’
…some output…
$ rake migrate VERSION=4
…some output (runs the self.down of version 5)…
Maybe that will help a bit?
Since that runs the .down in your migration, it will usually fail too,
depending on why your .up failed. For example, if .up fails while adding
a new column to a table, the .down will fail while trying to remove it,
because it does not exist…very frustrating indeed.
To add, my experience with mysql, interbase (firebird) and oracle
didn’t address them - although I’ve used them over a number of years,
and my most recent experiences are with oracle and mysql.
Through some reading it looks like sqlite supports them.
Another thought comes to mind : table creates (and drops) are a
special kind of DML - For example you can create tables in oracle,
mysql, interbase (probably most others) through table inserts (table
and detail column rows) - given this you could do table creates in
mysql under transaction support.
good luck.
Jodi
This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.
