Opened 4 years ago

Last modified 2 months ago

#15940 new New feature

use strict mode with mysql

Reported by: foxwhisper Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords:
Cc: django2.20.orzelf@…, tocker@… Triage Stage: Accepted
Has patch: no Needs documentation: yes
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (11)

comment:1 Changed 4 years ago by aaugustin

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Design decision needed

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.

comment:2 Changed 4 years ago by kmtracey

  • Needs documentation set
  • Triage Stage changed from Design decision needed to Accepted

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.

comment:3 Changed 4 years ago by anonymous

  • Summary changed from Patch database documentation to explain why using sql_mode=strict is important to Patch database documentation to explain why using MySQL's sql_mode=strict is important
  • UI/UX unset

comment:4 Changed 3 years ago by bignose

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 :-)

comment:5 Changed 16 months ago by brian@…

  • Component changed from Documentation to Database layer (models, ORM)
  • Summary changed from Patch database documentation to explain why using MySQL's sql_mode=strict is important to use strict mode with mysql

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.

comment:6 Changed 16 months ago by aaugustin

I think it is possible:

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

(I haven't tested.)

comment:7 Changed 16 months ago by brian

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.

comment:8 Changed 7 months ago by teeberg

This also works:

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

comment:9 Changed 2 months ago by orzel

  • Cc django2.20.orzelf@… added

comment:10 Changed 2 months ago by morgo

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.

comment:11 Changed 2 months ago by morgo

  • Cc tocker@… added
Note: See TracTickets for help on using tickets.
Back to Top