Re: [Django] #15940: Patch database documentation to explain why using MySQL's sql_mode=strict is important (was: Patch database documentation to explain why using sql_mode=strict is important)
Django
is important
---------------------------------------+-------------------------------
Reporter: foxwhisper | Owner: nobody
Type: New feature | Status: new
Milestone: | Component: Documentation
Version: 1.3 | 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 |
---------------------------------------+-------------------------------
Changes (by anonymous):
* ui_ux: => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/15940#comment:3>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
is important
Type: New feature | Status: new
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 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 [[https://gist.github.com/2495661|silently altering the data]]).
There are other ways MySQL mis-handles data silently by default, as
[[https://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html|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
[[https://dev.mysql.com/doc/refman/5.6/en/server-sql-
mode.html#sqlmode_strict_all_tables|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 :-)
--
Ticket URL: <https://code.djangoproject.com/ticket/15940#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: foxwhisper | Owner: nobody
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 1
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by brian@…):
* component: Documentation => Database layer (models, ORM)
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/15940#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: foxwhisper | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.3
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 1
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by aaugustin):
I think it is possible:
{{{
DATABASES = {
'default':
'ENGINE': 'django.db.backends.mysql',
# ...
'OPTIONS': {
'init_command': "SET sql_mode = '...';",
}
}
}
}}}
(I haven't tested.)
--
Ticket URL: <https://code.djangoproject.com/ticket/15940#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: foxwhisper | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.3
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 1
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/15940#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: foxwhisper | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.3
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 1
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by teeberg):
This also works:
{{{
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'OPTIONS': {
'sql_mode': 'traditional',
}
}
}
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/15940#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: foxwhisper | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.3
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 1
Easy pickings: 0 | UI/UX: 0
Changes (by orzel):
* cc: django2.20.orzelf@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/15940#comment:9>
Django
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
* cc: tocker@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/15940#comment:11>
Django
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
* cc: george@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/15940#comment:12>