Migrations and Transactions

Hello,

I'm running .NET 4, MySQL 5.5.9 and LightSpeed 4.0.1280.20255. Every now and then I run into an issue where migrations may "corrupt" my database because a particular statement fails somewhere in the middle of the migration. To reproduce, create several migrations and populate the database with tables. Then create a migration that creates a number of tables and in the middle of it, execute a statement that wil fail, like creating an index of a table that doesn't exist. Execute the migration. You may notice that your migrations are now stuck as one cannot downgrade or upgrade the schema.

Any chance the migration can be wrapped in a transaction if the data provider supports it, or allow me to specify it as an option on the migrator?

Thanks, Werner

We already do this if the database engine supports it. Unfortunately, MySQL does not:

http://dev.mysql.com/doc/refman/5.6/en/cannot-roll-back.html

(But if you're aware of any tricks for getting around this, we'd be delighted to implement them!)

I'm running MySQL in both UNIX and Windows. I was unaware of that issue.

Here is what I can think of:

1. Checking if the table exists before dropping it as in DROP TABLE IF EXISTS posts;
2. Checking if a table exists before creating it CREATE TABLE IF EXISTS posts; and then use ALTER table to modify the table.

Both of this can be internal without breaking existing migrations. Next, reduce the number of ExecuteCommand statements one has to write, which are used to:

1. Change the codepage of the table (so it is in UTF8 and can support unicode characters)
2. Change the codepage of the column (so it is case sensitive for security reasons, while leaving other columns to be case insensitive)
3. Change the codepage of the column (so it can support unicode)
4. Create indexes of one or more columns. For example,
   • Create or drop an index for the CreatedOn column because it is used for sorting
   • Create or drop an index for the "OpenIdIdentifier" column because it is used for lookups
   • Create or drop an unique index for the TenantId and Slug column because a entity with a slug only needs to be unique per tenant, not the whole database.
5. Seed the database with sample data

In the case where the database doesn't support a rollback, provide a method to I can override to manually try to recover from failure during the migration.

Another alternative would be to go the "Rails" route where you no longer need an "Up" or "Down" method, but instead, just a "Change" method. As statements are successfully executed their "opposite" is recorded. Upon failure, just execute the statements in reverse. This is where checking whether a table exists or not can make a big difference.

These ideas are not my own, but come form other sources, like Rails and EF.

Thanks, Werner