Setting Django/MySQL site to use UTF-8

Question

I want to set my Django site to use UTF-8 for MySQL in a foolproof way, regardless of whether the MySQL installation uses UTF-8 as its default. In addition to creating the tables with UTF-8 encoding, I added the following to my database initialization in settings.py to ensure the connection is also using utf-8:

'OPTIONS': { 'init_command': 'SET storage_engine=INNODB; SET names "utf8"' }

This results in an error:

_mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now")

What is the right way to do this? Is there another place where the SET NAMES needs to be executed?

Answer 1

All I had to do was put this in settings.py:

'OPTIONS': { 'init_command': 'SET storage_engine=INNODB' }

Then I created the database myself in MySQL with:

CREATE DATABASE my_database CHARACTER SET utf8;

followed by all the CREATE USER and GRANT calls.

After that, ./manage.py syncdb and ./manage.py migrate (since I use South) finished everything up.

It's been working fine so far. Didn't have to change any MySQL config files, either. (The sysadmin wanted to keep MySQL using the latin1 charset by default for the other users.)

Answer 2

You need only using SET once only and appending others commands as:

'SET storage_engine=INNODB,character_set_connection=utf8,collation_connection=utf8_unicode_ci'

Answer 3

Check: http://blog.ionelmc.ro/2014/12/28/terrible-choices-mysql/

  'SET '
                'storage_engine=INNODB,'
                'character_set_connection=utf8,'
                'collation_connection=utf8_bin,'
                'SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED',
         }