Django: Ticket #15940: Recommend using strict mode with MySQLhttps://code.djangoproject.com/ticket/15940
<p>
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).
</p>
<p>
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 ( <a class="ext-link" href="http://docs.djangoproject.com/en/dev/ref/databases/"><span class="icon">​</span>http://docs.djangoproject.com/en/dev/ref/databases/</a> ) which explains why you should use sql_mode=strict, along with how to do it ( which is explained in (<a class="ext-link" href="http://code.djangoproject.com/ticket/15923#comment:10"><span class="icon">​</span>http://code.djangoproject.com/ticket/15923#comment:10</a> by kmtracey ).
</p>
<p>
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?
</p>
<p>
If this is accepted, I'd be happy to submit the documentation patch.
</p>
en-usDjangohttps://www.djangoproject.com/s/img/site/hdr_logo.gifhttps://code.djangoproject.com/ticket/15940
Trac 1.2Aymeric AugustinSun, 01 May 2011 19:38:49 GMTstage changedhttps://code.djangoproject.com/ticket/15940#comment:1
https://code.djangoproject.com/ticket/15940#comment:1
<ul>
<li><strong>stage</strong>
changed from <em>Unreviewed</em> to <em>Design decision needed</em>
</li>
</ul>
<p>
I'll mark this as DDN, following Alex's judgement here: <a class="ext-link" href="http://code.djangoproject.com/ticket/15923#comment:7"><span class="icon">​</span>http://code.djangoproject.com/ticket/15923#comment:7</a>
</p>
<p>
In my opinion, documenting MySQL's features/bugs belongs to MySQL's docs, not to Django's.
</p>
<p>
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.
</p>
TicketKaren TraceySun, 01 May 2011 20:47:42 GMTneeds_docs, stage changedhttps://code.djangoproject.com/ticket/15940#comment:2
https://code.djangoproject.com/ticket/15940#comment:2
<ul>
<li><strong>needs_docs</strong>
set
</li>
<li><strong>stage</strong>
changed from <em>Design decision needed</em> to <em>Accepted</em>
</li>
</ul>
<p>
I'd say the existence of SQL modes in MySQL is worth a paragraph here: <a class="ext-link" href="http://docs.djangoproject.com/en/1.3/ref/databases/#mysql-notes"><span class="icon">​</span>http://docs.djangoproject.com/en/1.3/ref/databases/#mysql-notes</a>, 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 <a class="ext-link" href="http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html"><span class="icon">​</span>http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html</a>), 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.
</p>
TicketanonymousMon, 27 Jun 2011 14:33:33 GMTsummary changed; ui_ux sethttps://code.djangoproject.com/ticket/15940#comment:3
https://code.djangoproject.com/ticket/15940#comment:3
<ul>
<li><strong>ui_ux</strong>
unset
</li>
<li><strong>summary</strong>
changed from <em>Patch database documentation to explain why using sql_mode=strict is important</em> to <em>Patch database documentation to explain why using MySQL's sql_mode=strict is important</em>
</li>
</ul>
TicketBen FinneyThu, 26 Apr 2012 04:27:11 GMThttps://code.djangoproject.com/ticket/15940#comment:4
https://code.djangoproject.com/ticket/15940#comment:4
<p>
Using MySQL's strict SQL mode is important, and Django should set it as the default.
</p>
<p>
There are many ways to lose data integrity if this mode is not enabled: integers larger than maximum is one, as is <code>UPDATE footable SET bar = NULL</code> when <code>footable.bar</code> is a <code>NOT NULL</code> column (MySQL defaults to <a class="ext-link" href="https://gist.github.com/2495661"><span class="icon">​</span>silently altering the data</a>).
</p>
<p>
There are other ways MySQL mis-handles data silently by default, as <a class="ext-link" href="https://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html"><span class="icon">​</span>documented in the server modes</a> for addressing those misbehaviours.
</p>
<p>
As the bug reporter notes, these problems often go un-noticed until it's too late.
</p>
<p>
This makes the case, IMO, that Django should default to turning on MySQL's <code>sql_mode</code> to one of the STRICT modes (I recommend <a class="ext-link" href="https://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html#sqlmode_strict_all_tables"><span class="icon">​</span>STRICT_ALL_TABLES</a>) by default, and perhaps allow the non-strict modes only if specifically requested with a setting.
</p>
<p>
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 :-)
</p>
Ticketbrian@…Tue, 19 Nov 2013 04:07:34 GMTcomponent, summary changedhttps://code.djangoproject.com/ticket/15940#comment:5
https://code.djangoproject.com/ticket/15940#comment:5
<ul>
<li><strong>component</strong>
changed from <em>Documentation</em> to <em>Database layer (models, ORM)</em>
</li>
<li><strong>summary</strong>
changed from <em>Patch database documentation to explain why using MySQL's sql_mode=strict is important</em> to <em>use strict mode with mysql</em>
</li>
</ul>
<p>
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?).
</p>
<p>
My understanding is that Django could set strict mode on a per session basis, so other applications are not affected.
</p>
<p>
I don't think it is currently possible to set per session mysql parameters without changing Django however.
</p>
TicketAymeric AugustinTue, 19 Nov 2013 07:33:05 GMThttps://code.djangoproject.com/ticket/15940#comment:6
https://code.djangoproject.com/ticket/15940#comment:6
<p>
I think it is possible:
</p>
<pre class="wiki">DATABASES = {
'default':
'ENGINE': 'django.db.backends.mysql',
# ...
'OPTIONS': {
'init_command': "SET sql_mode = '...';",
}
}
}
</pre><p>
(I haven't tested.)
</p>
TicketBrian MayFri, 22 Nov 2013 05:05:33 GMThttps://code.djangoproject.com/ticket/15940#comment:7
https://code.djangoproject.com/ticket/15940#comment:7
<p>
I stand corrected, it is possible to set STRICT_ALL_TABLES on a per session basis, as described in comment 6.
</p>
<p>
I haven't seen any reason however why this should not be the default, used for all installs.
</p>
<p>
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.
</p>
TicketJonas TrappenbergThu, 14 Aug 2014 22:37:48 GMThttps://code.djangoproject.com/ticket/15940#comment:8
https://code.djangoproject.com/ticket/15940#comment:8
<p>
This also works:
</p>
<pre class="wiki">DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'OPTIONS': {
'sql_mode': 'traditional',
}
}
}
</pre>
TicketThomas CapricelliSun, 28 Dec 2014 11:05:45 GMTcc sethttps://code.djangoproject.com/ticket/15940#comment:9
https://code.djangoproject.com/ticket/15940#comment:9
<ul>
<li><strong>cc</strong>
<em>django2.20.orzelf@…</em> added
</li>
</ul>
TicketMorgan TockerFri, 02 Jan 2015 22:29:52 GMThttps://code.djangoproject.com/ticket/15940#comment:10
https://code.djangoproject.com/ticket/15940#comment:10
<p>
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:
</p>
<ul><li>MySQL 5.6 (GA Feb 2013) enabled sql mode <code>STRICT_TRANS_TABLES</code> 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.
</li></ul><ul><li>MySQL 5.7 (in development) will enable <code>STRICT_TRANS_TABLES</code> and <code>ONLY_FULL_GROUP_BY</code> as compiled defaults. The SQL modes <code>ERROR_FOR_DIVISION_BY_ZERO</code>, <code>NO_ZERO_DATE</code> and <code>NO_ZERO_IN_DATE</code> are also folded into the "STRICT" definition. Thus STRICT is now more strict. To show an example from 5.7 DMR5:
</li></ul><pre class="wiki">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
</pre><ul><li>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<em>actional</em>. 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).
</li></ul><p>
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.
</p>
TicketMorgan TockerFri, 02 Jan 2015 22:31:34 GMTcc changedhttps://code.djangoproject.com/ticket/15940#comment:11
https://code.djangoproject.com/ticket/15940#comment:11
<ul>
<li><strong>cc</strong>
<em>tocker@…</em> added
</li>
</ul>
TicketGeorge MarshallThu, 26 Nov 2015 04:58:25 GMTcc changedhttps://code.djangoproject.com/ticket/15940#comment:12
https://code.djangoproject.com/ticket/15940#comment:12
<ul>
<li><strong>cc</strong>
<em>george@…</em> added
</li>
</ul>
TicketTim GrahamTue, 29 Mar 2016 14:17:19 GMTneeds_docs, has_patch, component, summary changedhttps://code.djangoproject.com/ticket/15940#comment:13
https://code.djangoproject.com/ticket/15940#comment:13
<ul>
<li><strong>needs_docs</strong>
unset
</li>
<li><strong>has_patch</strong>
set
</li>
<li><strong>component</strong>
changed from <em>Database layer (models, ORM)</em> to <em>Documentation</em>
</li>
<li><strong>summary</strong>
changed from <em>use strict mode with mysql</em> to <em>Recommend using strict mode with MySQL</em>
</li>
</ul>
<p>
A note for <code>ref/databases.txt</code> is proposed as part of <a class="closed ticket" href="https://code.djangoproject.com/ticket/26351" title="#26351: Cleanup/optimization: Recommend enabling MySQL's STRICT_TRANS_TABLES to prevent silent truncation (closed: fixed)">#26351</a> (Add a database check that recommends enabling MySQL's STRICT_TRANS_TABLES to prevent silent truncation): <a class="ext-link" href="https://github.com/django/django/pull/6292"><span class="icon">​</span>PR</a>
</p>
TicketTim GrahamFri, 01 Apr 2016 23:26:31 GMTstage changedhttps://code.djangoproject.com/ticket/15940#comment:14
https://code.djangoproject.com/ticket/15940#comment:14
<ul>
<li><strong>stage</strong>
changed from <em>Accepted</em> to <em>Ready for checkin</em>
</li>
</ul>
TicketClaude Paroz <claude@…>Fri, 08 Apr 2016 18:34:28 GMTstatus changed; resolution sethttps://code.djangoproject.com/ticket/15940#comment:15
https://code.djangoproject.com/ticket/15940#comment:15
<ul>
<li><strong>status</strong>
changed from <em>new</em> to <em>closed</em>
</li>
<li><strong>resolution</strong>
set to <em>fixed</em>
</li>
</ul>
<p>
In <a class="changeset" href="https://code.djangoproject.com/changeset/b2aab09fe99b0e6e2e0357a7a794355a631c3039" title="Fixed #15940 -- Added MySQL note about strict SQL mode">b2aab09f</a>:
</p>
<div class="message"><p>
Fixed <a class="closed ticket" href="https://code.djangoproject.com/ticket/15940" title="#15940: New feature: Recommend using strict mode with MySQL (closed: fixed)">#15940</a> -- Added MySQL note about strict SQL mode<br />
</p>
</div>
TicketClaude Paroz <claude@…>Fri, 08 Apr 2016 18:36:51 GMThttps://code.djangoproject.com/ticket/15940#comment:16
https://code.djangoproject.com/ticket/15940#comment:16
<p>
In <a class="changeset" href="https://code.djangoproject.com/changeset/99bec24f99d60e7f52248484ebfd7e4a02c39749" title="[1.9.x] Fixed #15940 -- Added MySQL note about strict SQL mode
...">99bec24</a>:
</p>
<div class="message"><p>
[1.9.x] Fixed <a class="closed ticket" href="https://code.djangoproject.com/ticket/15940" title="#15940: New feature: Recommend using strict mode with MySQL (closed: fixed)">#15940</a> -- Added MySQL note about strict SQL mode<br />
</p>
<p>
Backport of b2aab09fe9 from master.<br />
</p>
</div>
Ticket