Django set Storage Engine & Default Charset

Question

Creating my tables from my models.py. I donno how to do 2 things -

1. I want to specify MySQL to create some of my tables as InnoDB & some as MyISAM. How do I do it?
2. Also I want to specify my tables DEFAULT CHARSET as utf8. How do I do it?

This is what I see when I run syncdb -

...
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I use Ubuntu 10.04, Django 1.2.X, MySQL 5.1.X

UPDATE: I thought these might be MySQL default settings & I ended up changing my.cnf where I added default-character-set = utf8. But to no use.

Danialah

Answer 1

I don't think you can change storage engines on a table-by-table basis, but you can do it on a database-by-database basis. This, of course, means that InnoDB foreign key constraints, for example, can't apply to foreign keys to MyISAM tables.

So you need to declare two "databases", which may very well be on the same server:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        #...
    }
    'innodb': {
        'ENGINE': 'django.db.backends.mysql',
        #...
        'OPTIONS': { 'init_command': 'SET storage_engine=INNODB;' }
    }
}

And you'll just need to apply using('innodb') to querysets for tables in InnoDB land.

As for UTF-8, again, I think you need to do this at the database level. I don't think syncdb creates the database for you, just the tables. You should create the database manually anyway, so you can have privileges set right before running syncdb. The database creation command you want is:

CREATE DATABASE django CHARACTER SET utf8;

---

That said, I usually recommend that people create two django users in the database: one for database schema work ("admin") and one for everything else (with different passwords):

CREATE DATABASE django CHARACTER SET utf8;
CREATE USER 'django_site'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON django.* TO django_site;
CREATE USER 'django_admin'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON django.* TO django_admin;
GRANT CREATE, DROP, ALTER, INDEX, LOCK TABLES ON django.* TO django_admin;
FLUSH PRIVILEGES;

(Note that this needs to be done for each database.)

For this to work, you need to modify manage.py:

import sys
if len(sys.argv) >= 2 and sys.argv[1] in ["syncdb", "dbshell", "migrate"]:
    os.environ['DJANGO_ACCESS'] = "ADMIN"

Then in your settings.py, use the environment variable to pick the right settings. Make sure the site (i.e. non-admin) user is the default.

(Additionally, I don't store the database setup, SECRET_KEY, or anything else sensitive in settings.py because my Django project is stored in Mercurial; I have settings.py pull all that in from an external file accessible only by Django's user and the server admins. I'll leave the "how" as an exercise for the reader... because I detailed some of it in answers to others' questions, and I'm too lazy to look it up right now.)

Excision

Well, you can mix InnoDB and MyISAM in one database. I learned it the hard way when some of my foreign keys were not enforced and some of them were. So, it's a good idea to set MySQL default table type to InnoDB. Otherwise even a dump and subsequent reload can change table types, if you forget to dump this info. – Eada

Can you please explain what using('innodb') means and where to add it? – Muzzleloader

A QuerySet, by default, uses the database specified in settings.DATABASES['default']. To use a different database (e.g. the one named 'innodb' in my example above), you need to call the using function on the QuerySet (e.g. Foo.objects.filter(...).using('innodb')). – Excision

Answer 2

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `gender` varchar(6) NOT NULL,
  `email` varchar(32) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `created` datetime NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=76 ;

Dryasdust