#15940: use strict mode with mysql
-------------------------------------+-------------------------------------
Reporter: foxwhisper | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.3
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (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_trans''actional''. 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/15940#comment:10>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/068.1a944b25dbbf2f301bc67d29b3ac40be%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
