Does Django's south (migration tool) work for innodb?

Question

$ py manage.py  migrate turkey
Running migrations for turkey:
 - Migrating forwards to 0001_initial.
 > turkey:0001_initial
 ! Error found during real run of migration! Aborting.

 ! Since you have a database that does not support running
 ! schema-altering statements in transactions, we have had 
 ! to leave it in an interim state between migrations.

! You *might* be able to recover with:   = DROP TABLE `turkey_demorecs` CASCADE; []

 ! The South developers regret this has happened, and would
 ! like to gently persuade you to consider a slightly
 ! easier-to-deal-with DBMS.
 ! NOTE: The error which caused the migration to fail is further up.

For some reason I get this when I try it. But my other setups are in MyISAM.

Why doesn't it work in Innodb?

Answer 1

InnoDB has constraints on Foreign Keys which ensure you are not breaking the database model when doing a migration. (see http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html)

MyISAM does not have native support for constraints (although it seems you can implement this if you choose to do do http://dev.mysql.com/tech-resources/articles/mysql-enforcing-foreign-keys.html)

Because MyISAM is not checking your FK relationships, you do not get the error. InnoDB however is doing a check and it seems that you have a problem with your migration.

Answer 2

See also https://code.djangoproject.com/wiki/AlterModelOnSyncDB

I have had the same kind of error happen to me when working with a mysql setup whose default table storage engine is MyISAM and I wanted to use InnoDB (using the recipe found in above link, we used the post_syncdb signal to trigger the conversion code). However, when using South to create new tables they were first created using MyISAM engine then later converted. I was mistakenly believing InnoDB tables weren't doing what they were supposed to, when those were actually MyISAM; because the table were converted by the signal, any migration error would fail to unapply :-/

If you need to use or create InnoDB tables where the default is MyISAM, this be solved with:

# add at the beginning of your migration
if db.backend_name == 'mysql':
   db.execute('SET storage_engine=INNODB')

or if you do not mind the performance hit:

# add this to settings.py
DATABASE_OPTIONS = {
   "init_command": "SET storage_engine=INNODB", # XXX: performance hit...
}

Answer 3

Yes, South does support InnoDB. Can you delete the contents of your "migrations" folder, and re-run schemamigration, migrate, and post the results and contents of the 0001_initial file here? PS: Make sure you have your migrations folder backed up or in source control first.

rm -fr app/migrations/*
./manage.py schemamigration app --initial
./manage.py migrate app

Answer 4

You could try adding to your first migration:

if db.backend_name == 'mysql':
    db.execute('SET foreign_key_checks=0')

This will disable the foreign key check constraints.

You don't have to set it back to 1 since it's a session variable.

By the way, it doesn't work if you set to 0 at the beggining and back to 1 at the end of your migration method, because south generates SQL with them, but executes it when they return.