Recommend using strict mode with MySQL

[Cal Leeming]

Currently, MySQL doesn't restrict you from entering an integer larger than the maximum allowed. It will raise a warning, but this warning isn't caught by Django anywhere. This is one of those gotcha's, which you only really notice after you've lost some data, and can't recover it (or unless you happened to come across similar threads like this).

Although I'd love to see Django using sql_mode=strict by default, I doubt this will happen. So instead, can we add a section into the database documentation ( ​http://docs.djangoproject.com/en/dev/ref/databases/ ) which explains why you should use sql_mode=strict, along with how to do it ( which is explained in (​http://code.djangoproject.com/ticket/15923#comment:10 by kmtracey ).

On a side note, there may also be other reasons why using sql_mode would be good or bad, so this would possibly have to be explored before this could be accepted?

If this is accepted, I'd be happy to submit the documentation patch.

[Aymeric Augustin]

I'll mark this as DDN, following Alex's judgement here: ​http://code.djangoproject.com/ticket/15923#comment:7

In my opinion, documenting MySQL's features/bugs belongs to MySQL's docs, not to Django's.

The docs generally assume that you are familiar with a) Python b) HTTP c) your database engine d) your web server e) your caching engine, if you use one, etc.

[Karen Tracey]

I'd say the existence of SQL modes in MySQL is worth a paragraph here: ​http://docs.djangoproject.com/en/1.3/ref/databases/#mysql-notes, after the section on storage engines and before the discussion of MySQLdb. A lot of people seem to not even know these modes exist. I think it is worth pointing them out (referencing the MySQL doc ​http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html), mentioning that many default MySQL configurations treat what most people would consider to be error conditions (note this affects more than just integer range handling) rather more cavalierly than may be expected, and that choosing and configuring an appropriate mode for your server is something that should be done as part of initial setup. I don't believe we should recommend any particular mode, and we should be careful about stating anything about what is the default, because that does in fact differ depending on what distribution is in use.

[Ben Finney]

Using MySQL's strict SQL mode is important, and Django should set it as the default.

There are many ways to lose data integrity if this mode is not enabled: integers larger than maximum is one, as is UPDATE footable SET bar = NULL when footable.bar is a NOT NULL column (MySQL defaults to ​silently altering the data).

There are other ways MySQL mis-handles data silently by default, as ​documented in the server modes for addressing those misbehaviours.

As the bug reporter notes, these problems often go un-noticed until it's too late.

This makes the case, IMO, that Django should default to turning on MySQL's sql_mode to one of the STRICT modes (I recommend ​STRICT_ALL_TABLES) by default, and perhaps allow the non-strict modes only if specifically requested with a setting.

So, rather than documenting MySQL's bugs, Django should use the provided feature for addressing them correctly, and allow site admins to shoot themselves in the foot only if they go looking for the gun :-)

[brian@…]

The problem with setting sql_mode to STRICT_ALL_TABLES, is it will affect all databases, and potentially break non-django apps that rely on the broken behaviour (do any exist?).

My understanding is that Django could set strict mode on a per session basis, so other applications are not affected.

I don't think it is currently possible to set per session mysql parameters without changing Django however.

[Aymeric Augustin]

I think it is possible:

DATABASES = {
    'default':
        'ENGINE': 'django.db.backends.mysql',
        # ...
        'OPTIONS': {
            'init_command': "SET sql_mode = '...';",
        }
    }
}

(I haven't tested.)

[Brian May]

I stand corrected, it is possible to set STRICT_ALL_TABLES on a per session basis, as described in comment 6.

I haven't seen any reason however why this should not be the default, used for all installs.

Also, as a semi-related issue all the documents say "MySQL defaults to ​silently altering the data". Which doesn't match my experience. What I have observed is that mysql truncates the data and writes it, returns a warning that data is truncated, which gets translated as _mysql_exceptions.Warning, which Django treats as an error and fails. This can be incredibly confusing, especially if transactions aren't enabled.

[Jonas Trappenberg]

This also works:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'OPTIONS': {
            'sql_mode': 'traditional',
        }
    }
}

[Morgan Tocker]

I work on the MySQL team at Oracle. We have been changing the defaults to be more strict over new releases. Here is a summary of what has happened:

• MySQL 5.6 (GA Feb 2013) enabled sql mode STRICT_TRANS_TABLES for new installations. What this means is that all config files have this sql mode setting, but if a my.cnf file existing prior to installing, or a user moves the config file it won't be enabled.

• MySQL 5.7 (in development) will enable STRICT_TRANS_TABLES and ONLY_FULL_GROUP_BY as compiled defaults. The SQL modes ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE and NO_ZERO_IN_DATE are also folded into the "STRICT" definition. Thus STRICT is now more strict. To show an example from 5.7 DMR5:

CREATE TABLE test (a int unsigned);
INSERT INTO test VALUES (-1);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
insert into test values (0/0);
ERROR 1365 (22012): Division by 0

CREATE TABLE test2 (a varchar(10));
INSERT INTO test2 VALUES ('abcdefghijklmnopqrstuvwxyz');
ERROR 1406 (22001): Data too long for column 'a' at row 1

CREATE TABLE test3 (a datetime);
INSERT INTO test3 VALUES ('0000-00-00 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'a' at row 1

• Since MySQL 5.5 (Dec 2010) InnoDB is the default storage engine, so for most installs there shouldn't be a difference between strict all versus strict_transactional. Since a DML statement could modify multiple rows, and an sql violation could occur mid-modification on any row. Producing errors on non-transactional tables is always tricky, hence the ability to control these behaviors independently (STRICT for transactional tables versus ALL tables).

Explicitly setting sql modes that Django is compatible with on connection sounds like a good idea to me. Other projects (Wordpress, Drupal, Magento) are also doing this.

[Tim Graham]

A note for ref/databases.txt is proposed as part of #26351 (Add a database check that recommends enabling MySQL's STRICT_TRANS_TABLES to prevent silent truncation): ​PR

[Claude Paroz <claude@…>]

In b2aab09f:

Fixed #15940 -- Added MySQL note about strict SQL mode

[Claude Paroz <claude@…>]

In 99bec24:

[1.9.x] Fixed #15940 -- Added MySQL note about strict SQL mode

Backport of b2aab09fe9 from master.