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)?

Edwyna

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

Formwork

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.

Antifebrile

you can truncate all your tables using ./manage.py sqlflush | ./manage.py dbshell – Technic

The django docs says to remove the init_command when your table has been created, since it adds a SQL query to every database connection. Instead, change the default table type in your MySQL config. – Uriel

To whomever comes here to copy/paste code: you probably tried this and it gave an error "ERROR 1064 (42000) at line 1". That's because there were initial warnings when running python manage.py sqlflush. To circumvent it: python manage.py sqlflush > flushdump.txt, delete the initial warnings, save, then python manage.py dbshell < flushdump.txt – Wolfson

Answer 3

This really has nothing to do with Django. It's entirely a MySQL thing, and there's documentation on just this type of thing directly from them: http://dev.mysql.com/doc/refman/5.5/en/converting-tables-to-innodb.html

Torn

Is it really? I mean, MyISAM tables have no Foreign Key constraints stored in their definitions. Django on the other hand, has information about tables relationships, doesn't it? Perhaps the OP is asking about a way to convert the tables and at the same time adding the FKs (that correspond to relationships) defined in Django? – Bechance

@ypercube - You are right, I do not know why Chris thought it is not suitable for Django.. – Edwyna

Where in Chris's comment did he say it is not suitable for Django? He just said that migrating the tables has nothing to do with Django, which is true. Follow his instructions, and if FK constraints aren't there already you can get them with manage.py sqlindexes. – Rate

I got confused when he mentioned Django. It's MySQL question, not Django! – Eskisehir

Answer 4

I had a similar situation where I didn't realize my hosting provider had such an old MySQL version that it still defaulted to MyISAM, but I'd already set up the basic Django tables.

This was posted about 2 months after the original question: Convert Legacy Django MySQL DBS from MyISAM to InnoDB.

That and leech/Trey's answer describe the ALTER TABLE ___ ENGINE=INNODB command that converts the table in MySQL, but the problem is that even though the tables are converted, they don't have the foreign key constraints that would have been set up if the tables had been INNODB in the first place.

I found, on django-admin.py and manage.py, that python manage.py sqlall appname "prints the CREATE TABLE and initial-data SQL statements for the given app name(s)."

I looked at INSTALLED_APPS in settings.py and ended up running something like python manage.py sqlall admin auth contenttypes sessions sites messages staticfiles (one for each django.contrib.appname in INSTALLED_APPS). That showed the initial CREATE TABLE statements, indexes and foreign key constraints:

ALTER TABLE `django_admin_log` ADD CONSTRAINT `content_type_id_refs_id_288599e6` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`);
ALTER TABLE `django_admin_log` ADD CONSTRAINT `user_id_refs_id_c8665aa` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`);
ALTER TABLE `auth_permission` ADD CONSTRAINT `content_type_id_refs_id_728de91f` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`);
ALTER TABLE `auth_group_permissions` ADD CONSTRAINT `permission_id_refs_id_a7792de1` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`);
ALTER TABLE `auth_group_permissions` ADD CONSTRAINT `group_id_refs_id_3cea63fe` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`);
ALTER TABLE `auth_user_user_permissions` ADD CONSTRAINT `permission_id_refs_id_67e79cb` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`);
ALTER TABLE `auth_user_groups` ADD CONSTRAINT `group_id_refs_id_f0ee9890` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`);
ALTER TABLE `auth_user_user_permissions` ADD CONSTRAINT `user_id_refs_id_f2045483` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`);
ALTER TABLE `auth_user_groups` ADD CONSTRAINT `user_id_refs_id_831107f1` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`);
ALTER TABLE `auth_message` ADD CONSTRAINT `user_id_refs_id_9af0b65a` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`);

After converting all the tables to ENGINE=INNODB I ran the foreign key constraints above and believe I should have the database in the same state as it would have been if my database had defaulted to creating INNODB tables in the first place.

Incidentally, as Michael van de Waeter mentioned in his answer, if you want any new tables Django creates to be INNODB by default, you should add 'OPTIONS': {"init_command": "SET storage_engine=INNODB",} to the DATABASES dict in settings.py

Selfdenial