[Django] #31689: Docs should warn about ignore_conflicts making MySQL tinker with your data
Django
-----------------------------------------+------------------------
Reporter: Tobias Krönke | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 3.0
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+------------------------
We were caught off guard that MySQL e.g. changes a not nullable date field
into `0000-00-00` when trying to bulk insert with `ignore_conflicts=True`
and `None` as the value for the column. You can read about that behavior
here:
- https://www.mysqltutorial.org/mysql-insert-ignore/
- https://dev.mysql.com/doc/refman/8.0/en/insert.html
{{{
With IGNORE, invalid values are adjusted to the closest values and
inserted;
}}}
Of course, you're encouraged to activate strict mode in MySQL (https
://django-mysql.readthedocs.io/en/latest/checks.html), but this will not
help in this specific case:
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict
{{{
If strict mode is enabled, dates with zero parts are not permitted and
inserts produce an error, unless IGNORE is given as well. For INSERT
IGNORE and UPDATE IGNORE, dates with zero parts are inserted as
'0000-00-00'
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31689>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: Tobias Krönke | Owner: nobody
Cleanup/optimization |
Component: Documentation | Version: 3.0
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tobias Krönke):
* type: Uncategorized => Cleanup/optimization
* component: Uncategorized => Documentation
--
Ticket URL: <https://code.djangoproject.com/ticket/31689#comment:1>
Django
Reporter: Tobias Krönke | Owner: nobody
Cleanup/optimization |
Component: Documentation | Version: 3.0
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by felixxm):
I'm not sure if the Django documentation is the right place for this
warning. We cannot document all database caveats.
--
Ticket URL: <https://code.djangoproject.com/ticket/31689#comment:2>
Django
Reporter: Tobias Krönke | Owner: nobody
Cleanup/optimization |
Component: Documentation | Version: 3.0
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tobias Krönke):
I was considering this, too. However the description is rather vague not
really mentioning what "ignoring a failure" could mean and thereby easily
implying that the row is simply not inserted. If you don't want to pollute
this method's documentation, it could also be added to
https://docs.djangoproject.com/en/3.0/ref/databases/#setting-sql-mode to
make users aware of exceptions to strict mode.
--
Ticket URL: <https://code.djangoproject.com/ticket/31689#comment:3>
Django
duplicate keys on MySQL.
--------------------------------------+------------------------------------
Reporter: Tobias Krönke | Owner: nobody
Component: Documentation | Version: 3.0
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* stage: Unreviewed => Accepted
Comment:
Agreed. It is really unexpected that `ignore_conflicts` ignores also bunch
of other errors. I don't think we should document this specific use case,
I would add a short warning (to the
[https://docs.djangoproject.com/en/stable/ref/models/querysets/#django.db.models.query.QuerySet.bulk_create
bulk_create() docs]) that MySQL ignores not only duplicate keys but also
other errors, with a reference to [https://dev.mysql.com/doc/refman/8.0/en
/sql-mode.html#ignore-strict-comparison the MySQL docs].
--
Ticket URL: <https://code.djangoproject.com/ticket/31689#comment:4>
Django
duplicate keys on MySQL.
--------------------------------------+------------------------------------
Component: Documentation | Version: 3.0
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tobias Krönke):
* has_patch: 0 => 1
Comment:
Thank you for your feedback I used to craft a PR:
https://github.com/django/django/pull/13051.
--
Ticket URL: <https://code.djangoproject.com/ticket/31689#comment:5>
Django
duplicate keys on MySQL.
Reporter: Tobias Krönke | Owner: Tobias
Type: | Krönke
Cleanup/optimization | Status: assigned
Component: Documentation | Version: 3.0
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* owner: nobody => Tobias Krönke
* status: new => assigned
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/31689#comment:6>
Django
duplicate keys on MySQL.
-------------------------------------+-------------------------------------
Reporter: Tobias Krönke | Owner: Tobias
Type: | Krönke
Component: Documentation | Version: 3.0
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak <felisiak.mariusz@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"69e0d9c553bb55dde8d7d1d479a78bfa7093f406" 69e0d9c]:
{{{
#!CommitTicketReference repository=""
revision="69e0d9c553bb55dde8d7d1d479a78bfa7093f406"
Fixed #31689 -- Doc'd caveat about using bulk_create()'s ignore_conflicts
on MariDB and MySQL.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31689#comment:7>
Django
duplicate keys on MySQL.
-------------------------------------+-------------------------------------
Reporter: Tobias Krönke | Owner: Tobias
Type: | Krönke
Cleanup/optimization | Status: closed
Component: Documentation | Version: 3.0
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"5ba2dfb9d0043121ae9966855ce468ce1104a626" 5ba2dfb]:
{{{
#!CommitTicketReference repository=""
revision="5ba2dfb9d0043121ae9966855ce468ce1104a626"
[3.1.x] Fixed #31689 -- Doc'd caveat about using bulk_create()'s
ignore_conflicts on MariDB and MySQL.
Backport of 69e0d9c553bb55dde8d7d1d479a78bfa7093f406 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31689#comment:8>
Django
duplicate keys on MySQL.
-------------------------------------+-------------------------------------
Reporter: Tobias Krönke | Owner: Tobias
Type: | Krönke
Cleanup/optimization | Status: closed
Component: Documentation | Version: 3.0
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"0afe8ba2d6712f3766f1ef11bbd14734cb3b2d2a" 0afe8ba2]:
{{{
#!CommitTicketReference repository=""
revision="0afe8ba2d6712f3766f1ef11bbd14734cb3b2d2a"
[3.0.x] Fixed #31689 -- Doc'd caveat about using bulk_create()'s
ignore_conflicts on MariDB and MySQL.
Backport of 69e0d9c553bb55dde8d7d1d479a78bfa7093f406 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31689#comment:9>