Converting an existing MyISAM database to InnoDB with Django

Question

Is there a way I can convert a full populated MyISAM database to InnoDB (in a way that will create all foreign key constraints, the same way it would be if I ran the syncdb command from the beginning)?

Answer 1

This might help:

from django.core.management.base import BaseCommand
from django.db import connections


class Command(BaseCommand):

    def handle(self, database="default", *args, **options):

        cursor = connections[database].cursor()

        cursor.execute("SHOW TABLE STATUS")

        for row in cursor.fetchall():
            if row[1] != "InnoDB":
                print "Converting %s" % row[0],
                print cursor.execute("ALTER TABLE %s ENGINE=INNODB" % row[0])

Add that to your app under the folders management/commands/ Then you can convert all your tables with a manage.py command:

python manage.py convert_to_innodb

Answer 2

Converting MyISAM to InnoDB with Django.

Given the old database is in MyISAM.

Dump the data of old database to json with:

$ python manage.py dumpdata contenttypes --indent=4 --natural > contenttype.json
$ python manage.py dumpdata --exclude contenttypes --indent=4 --natural > everything_else.json

Delete the old database, and create it again.

Add InnoDB settings in your settings.py like this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'STORAGE_ENGINE': 'InnoDB',
        'NAME': 'yourdbname',
        'USER': '',
        'PASSWORD': '',
        'HOST': '',
        'PORT': '',
        'OPTIONS': {
            'init_command': 'SET storage_engine=InnoDB',  # better to set this in your database config, otherwise django has to do a query everytime
        }
    }
}

Create tables (django also adds the relations) Make sure you don't add an admin user:

$ python manage.py syncdb --migrate

Now you want to empty all the old tables:

$ python manage.py sqlflush | ./manage.py dbshell

Now you can load the new data into the database like so:

$ python manage.py loaddata contenttype.json
$ python manage.py loaddata everything_else.json

There you go. I used Django==1.4 for this.