convert legacy django mysql dbs from myisam to innodb
The Problem and the Virtue of Transactions
Disclaimer: a long time programmer, old guy, learning new tricks. If I made a mistake please let me know in the comments and I’ll credit and update the post. Thanks!
Short version: For Django – MYISAM BAD. INNODB GOOD. Fight it if you wish, but for me, this was the bug and the fix. (Oh, and everything below relates to a Mac OS X 10.7.3 so translate to your OS.)
Django supports transactions. This is a good thing. And recommended. In my case I had a bug where something in admin delete worked on my local machine but did NOT work in staging. Tres embarrassant!
Specifically (for the now open source Tendenci software) I was struggling with building a Tendenci Django plugin with related objects (categories, etc) and attached images (files). Basic CRUD stuff. CRUD worked on my django dev environment locally, but only CRU worked and D failed in staging. Specifically Django failed on delete with a relationship error. Can’t delete three things at once. Why? Hmmmm. Stumped.
Thanks to debugging by JMO, he found the difference is our staging and production database servers have tables that are set to INNODB as the storage engine and my local mySQL defaulted database tables to MYISAM (MySQL 5.1). Thus trying to delete an object through the Django admin failed in staging because it could not delete three things at once in INNODB which strictly enforces relationship rules. OK, that seems fair. MYISAM is more willy-nilly-do-whatever. Which means you have scraps of relational data left in your tables. Yuck. Long-term-data-mining-hell.
Bottom line – transactions in Django are not supported at a database level with Django middleware with MYISAM and thus I couldn’t wrap a (multi-)delete into one call in admin.py. Sure we could programmatically delete the objects in sequence as a quick bug fix, Yet I’d rather let Django handle the heavy lifting (and shorter code). And long term there is a strong need for transactions. Especially in the age of RESTFUL APIs (I miss you soap! NOT!) In current web development with API calls dependencies can easily be on 30 routers and 20 servers just so you can post a pic of your chicks to Instagram Flickr. Thus you have to be able to roll back transactions. That is part of what a framework does. And transaction rollbacks clearly should NOT be the application layer’s job IMHO. MVC and all even if C is in V in Django. Another debate…. So transactions it is.
How to upgrade the dev mysql database environment from MYISAM to INNODB?
The fix that worked for me came from this post on converting a mysql database to INNODB for Drupal as well as several others credited below. While this post is on Django, Drupal still supports both MYISAM and INNODB, the default in Drupal 7 is now INNODB. So don’t fight the man. I’m moving to INNODB for Django too.
What steps are required for Django on MYISAM on a Mac OSX to get transactions working in Django? Combining a few of the posts (all linked below) and the django transactions help file you wind up with the following:
First convert your database. To do this create a temp directory and change (cd temp) into it using terminal. Run the commands from this post. I repeated them below slightly changed and I am skipping the prompt indicator to make it easier to copy.
First check if this is even a problem by getting a list of databases that have tables that use MYISAM
mysql -u USERNAME -p #it will prompt for your password here SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';
You will get some results from “information_schema” and “mysql” and I’d recommend not changing those. Just look for your dev databases.
Second create the SQL file to change the offending databases that are your Django mysql databases.
cd temp
mysql -u USERNAME -p -e "SHOW TABLES IN YOURDATABASENAME;" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql
It will prompt you for your password and replace the USERNAME and the YOURDATABASENAME with your own. Side note, don’t put a dash in your database name or it won’t work. I want those 30 minutes of my life back. Moving on….
I’m a curious guy so I wanted to see the contents of the sql file. (Replace “Sublime Text 2″ with the text editor of your choice. I just use Sublime because Glen told me to).
open -a "Sublime Text 2" alter_table.sql
Ooooh. Aaaaah. Looks fine. OK, close that. From there I prefer to do the update one database at a time to be sure so this:
mysql -u USERNAME -p YOURDATABASENAME < alter_table.sql
Then to confirm run the myisam table query again (which we hope does NOT show our now converted DB.)
SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';
All good? Cool.
For all I know INNODB might break another database so I am only focusing on your Django mysql databases. Now in your settings.py file in your Django project be sure you have the following line as part of your DATABASES setting.
'OPTIONS': {"init_command": "SET storage_engine=INNODB",}
The whole settings.py mysql connection setup now looks like this because I hate when people leave off the context of where to put code or the details (I’m looking at you StackOverFlow.) /rant/Actually the only thing I hate more are code examples that use sqllite because they are close to useless. /rant/
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'YOURDATABASENAME',
'USER': 'USERNAME',
'PASSWORD': 'BLAH',
'HOST': 'BLAH',
'PORT': 'BLAH',
'OPTIONS': {"init_command": "SET storage_engine=INNODB",}
}
}
Now when you run from your project directory the usual “python manage.py syncdb“ command it doesn’t use the database defaults regardless. Anything new should be automatically created INNODB.
The following posts are the entire basis of the content above. I just combined it all in one place specific to Django and MySQL on Mac OS X. Comments and corrections are welcome.
- https://docs.djangoproject.com/en/dev/topics/db/transactions/
- http://djangosaur.tumblr.com/post/357759467/django-transaction-mysql-engine-innodb
- http://www.electrictoolbox.com/find-innodb-tables-mysql/
- http://www.mikebernat.com/blog/MySQL_-_InnoDB_vs_MyISAM
- http://highervisibilitywebsites.com/convert-your-mysql-database-myisam-innodb-and-get-ready-drupal-7-same-time
- http://stackoverflow.com/questions/9947671/performance-difference-between-innodb-and-myisam-in-mysql
- http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB
