Explicitly set MySQL table storage engine using South and Django

Question

I'm running into an issue that South creates the DB table for a new model as INNODB when I migrate but creates the table as MYISAM when another developer runs their own migration.

The problem with this is that all my other tables are MYISAM so using the new tables leads to many foreign key constraint errors.

How can I explicitly make sure the table is created using MYISAM?

What could be causing the table to be created using a different storage engine in different environments?

Refute

Answer 1

To be sure that all migrations are always done using INNODB, you should set the storage engine as INNODB in the database definition directly, like this :

DATABASES = {
    'default': {
        ...
        'OPTIONS'  : { 'init_command' : 'SET storage_engine=INNODB', },
    }

If you are using MySQL 5.7.x and above,

DATABASES = {
    'default': {
        ...
        'OPTIONS'  : { 'init_command' : 'SET default_storage_engine=INNODB', },
    }

But you should know that it can have a performance hit. So you may want to set this option only when running migrations.

Doralia

To clarify, this goes in settings.py, not in the migrations. – Dunaway

This was exactly what I was looking for. Thanks. To compensate for the performance hit that you mentioned I added a conditional around it. Now it will only be invoked at times when I want to modify the database (syncdb or modify). import sys if 'migrate' in sys.argv or 'syncdb' in sys.argv: – Refute

What's the proper storage_engine setting for MyISAM? Is it all upper-case or is it mixed case, or something else? – Karylkarylin

storage_engine is now depreciated from MySQL 5.7.x and above. Use default_storage_engine instead. – Ton

Answer 2

If you use South, you can set the STORAGE_ENGINE.

django < 1.2

# add to your settings file
DATABASE_STORAGE_ENGINE = 'INNODB' # django < 1.2

django >= 1.2

# add to your settings file
DATABASES = {
   'default': {
       ...
       'STORAGE_ENGINE': 'INNODB'
   }
}

Allodium

This is the better answer (although there is a typo - should be ":" not "=", see the link for the correct syntax), because it won't have the performance problems mentioned earlier. The storage engine will only be set when south is doing a migration (e.g. creating a table), but not for other database operations, AIUI. – Quechua

Note for those working on legacy projects with older Django versions, but newer MySQL- take heed of Abhinav's comment that storage_engine is deprecated from MySQL 5.7.x and above. Use 'DEFAULT_STORAGE_ENGINE' in the Django settings file, not 'STORAGE_ENGINE'. – Felker