Migrate: question about safeup

Theorically, SafeUp is transaction-safe.

I make several table creation and inserts into a SafeUp. What puzzles me is that if one of these fails the previous create or insert are kept valid, so the DB is in a state where I cannot do mirgate down because the migration is not applied, but some tables are created, so I cannot even redo the same migration!

Isn’t it supposed to work different?

Does your migration script feature SafeDown’s as well?

yes.

for what I’ve seen when the SafeUp throws an exception the migration is interrupted at the wrong statement

Are you using MySQL? Cause MySQL, by default -in my case- sets autocommit=1.

Btw, I tried "SET autocommit=1" and gave me the same problem as yours.

I looked at the CDbMigration.php and saw a code error in up() function.

I’ll issue it.

What’s the status on this? Did any create a bug report or something? If so, where is it? :)

Upon further investigation it seems that one cannot turn off MySQL’s implicit commits for DDL statements :confused: If so, I guess that the error was having is hard to get away from. If a statement fails and a DDL one has been issued before that, then the transaction will have been committed. And there’s nothing we can do about that to preserve the transaction, right?

I’ll try filing a bug…

It’s like you said:

so I guess the best pratice is to create a migration for every create table.

Uncomfortble but at least the database will remain consistent in case of error.

Yeah…

Regarding consistent, it’ll of course only be consistent in the context of individual tables. If you are adding a feature or change that involves multiple tables you are simply screwed if you run MySQL even if you do one migration for each table (also, there’re so many other things you might need to do as well, other than creating tables). There’s no nice way to migrate MySQL AFAIK.

The best we can do is probably to not use MySQL in the first place if we need migration support. Personally I can’t stand the PoS. Anyone who has still not given PostgreSQL a chance definately should, this is a good reminder of that.

:slight_smile:

cheers