Terrible choices: MySQL

SQL mode *

With the default settings MySQL truncates and does other unspeakable things to data for the sake of not giving errors. Where is this a correct choice, hard to say.

What the defaults allow:

• Storing invalid dates like '0000-00-00' or '2010-01-00' [1].
• Silently treating errors like:
  • Specifying an unavailable storage engine. It will use the default engine, silently [2].
  • Inserting invalid data. Larger strings get truncated to the maximum length. Larger integers get truncated to the maximum. Other things get converted to NULL if the column allows that. All silently [3].

And no, ORMs won't save you from this pain by default. For Django you need to have something like this in the settings:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'whatever',
        'OPTIONS': {
            'sql_mode': 'TRADITIONAL',
        }  # Note that later we find out that this is not enough. Read on.
    }
}

There's an open Django ticket to set this the default.

And yes, you can set this in the MySQL settings but you don't really want that. Your app will misbehave and corrupt data if you ever happen to forget to change the /etc/mysql/my.cnf before deployment. And if you ever forget to do that, your data is already going to be corrupted by the time you notice something is not quite right.

Collations and encodings *

So far I've used utf8 as the connection charset. Cause you never know what the default one is (probably latin1). However, note that the charset affects the maximum index size for VARCHAR columns. Ever wonder why you often see this:

myfield = models.CharField(max_length=255, db_index=True)

Yes, the index on utf8 columns is limited to 255 characters [4]. If you need to store emoticons like an angry face 😠- and most probably you'd want to, given the predicament - then you're out of luck, you need a different charset, utf8mb4 [5]. But that means smaller index, only 191 characters [11].

But that's not that bad, you should get some errors if you fail to set the correct encoding. Collations however, are more trippy. A collation is a set of rules for comparing characters in a character set [6].

Notice a pattern here? Note the _ci suffix:

mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |
+----------+-----------------------------+---------------------+--------+

All the default collations are case insensitive. This means your queries and indexes are also going to be case insensitive [6]. I'd say they are just insensitive. To your pain.

If you have a case insensitive collation all sorts of queries that aren't a text search will behave strangely [7]. You'll notice that get_or_create doesn't work as expected when you have accents or a different case.

The solution is to use utf_bin collation and specify a different one only when you need special accent handling and case folding. The settings now look like this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'mydatabase',
        'OPTIONS': {
            'sql_mode': 'TRADITIONAL',
            'charset': 'utf8',
            'init_command': 'SET '
                'storage_engine=INNODB,'
                'character_set_connection=utf8,'
                'collation_connection=utf8_bin'
        }  # Note that later we find out that this is still not enough. Read on.
    }
}

Unfortunately I didn't know this from the start. So I had to fix the existing data and installation scripts. When you create a database, make sure that you specify the encoding and collation:

CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_bin;

To fix the existing data, a south migration would look like:

from south.db import db
from south.v2 import DataMigration

class Migration(DataMigration):
    def forwards(self, orm):
        print('')
        print('    Altering database ...')
        db.execute("ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_bin;")
        for table, in db.execute('SHOW TABLES'):
            print('    Altering table %s ...' % table)
            db.execute(
                "ALTER TABLE %s CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin" % table
            )

    def backwards(self, orm):
        # Altering the tables takes lots of time and
        # locks the tables, since it copies all the data.
        raise RuntimeError(
            "This migration probably took 2 hours, you don't really want to rollback ..."
        )

With the new migrations in Django 1.7:

from django.db import migrations

def forwards(apps, schema_editor):
    with schema_editor.connection.cursor() as cursor:
        print('')
        print('    Altering database ...')
        cursor.execute("ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_bin;")
        cursor.execute("SHOW TABLES;")
        for table, in cursor.fetchall():
            print('    Altering table %s ...' % table)
            cursor.execute(
                "ALTER TABLE %s CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin" % table
            )

def backwards(self, orm):
    # Altering the tables takes lots of time and
    # locks the tables, since it copies all the data.
    raise RuntimeError(
        "This migration probably took 2 hours, you don't really want to rollback ..."
    )

class Migration(migrations.Migration):
    dependencies = [
        # Needs to be filled, to figure it out run:
        #   django-admin makemigrations myapp --empty
    ]
    operations = [migrations.RunPython(forwards, backwards)]

Note that this will take long time to run (MySQL seems to copy all the tables for some reason) and it will change the encoding and collation for all the columns [8].

The transaction isolation level *

Unfortunately getting the collation and encodings right won't make get_or_create work flawlessly [9]. The default transaction level for MySQL is REPEATABLE READ, that means reads are consistent in the same transaction - they will return the same result, even if outside the transaction the data has changed. In other words, REPEATABLE READ will break get_or_create in a transaction - it's possible that code in a transaction won't "see" the object created outside the transaction (like in another process).

Unfortunately the defaults are hard to change without breaking existing apps [10], so you have to work this out in your connection settings. Now the settings are:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'mydatabase',
        'OPTIONS': {
            'sql_mode': 'TRADITIONAL',
            'charset': 'utf8',
            'init_command': 'SET '
                'storage_engine=INNODB,'
                'character_set_connection=utf8,'
                'collation_connection=utf8_bin,'
                'SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED',
        }  # Now we have a mild degree of confidence :-)
    }
}

DDL statements *

DDL statements in MySQL, not only they are slow and lock tables (that means downtime), they will ignore transactions. The almighty InnoDB can't save your sanity when an ALTER is used in a transaction. Thus, migrations in MySQL must be approached with great care (test them well) and to avoid downtime you need to use specific external tools.

Annoying, even for development:

! Error found during real run of migration! Aborting.

! Since you have a database that does not support running
! schema-altering statements in transactions, we have had
! to leave it in an interim state between migrations.

! The South developers regret this has happened, and would
! like to gently persuade you to consider a slightly
! easier-to-deal-with DBMS.

If this is the same with the migration system in 1.7 (seems it it, but you only get a plain traceback) then it's ideal to have migrations small in scope if you have custom sql that can easily fail, and squash them later, after you have successfully ran them.