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?

score 23 · Accepted Answer · edited May 23 '17 at 12:06

23

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.

edited May 23 '17 at 12:06

Community

1
1

answered Mar 11 '11 at 07:17

Xixi

329
2
3

To clarify, this goes in settings.py, not in the migrations. – Daniel Roseman Mar 11 '11 at 15:14
2

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:` – Eron Villarreal Mar 14 '11 at 08:04
2

What's the proper storage_engine setting for MyISAM? Is it all upper-case or is it mixed case, or something else? – Joe J Apr 16 '14 at 22:58
storage_engine is now depreciated from MySQL 5.7.x and above. Use default_storage_engine instead. – Abhinav May 14 '16 at 11:06

score 14 · Answer 2 · edited Mar 10 '16 at 04:56

14

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'
   }
}

edited Mar 10 '16 at 04:56

laffuste

16,287
8
84
91

answered Aug 26 '11 at 01:55

dnozay

23,846
6
82
104

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. – George Lund Jun 08 '12 at 10:58
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'`. – MisterRios Sep 14 '16 at 19:36

Explicitly set MySQL table storage engine using South and Django

2 Answers2