Warn that bulk_create()'s ignore_conflicts option ignores not only duplicate keys on MySQL.

[Tobias Krönke]

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'

[Mariusz Felisiak]

I'm not sure if the Django documentation is the right place for this warning. We cannot document all database caveats.

[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.

[Mariusz Felisiak]

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 ​bulk_create() docs) that MySQL ignores not only duplicate keys but also other errors, with a reference to ​the MySQL docs.

[Tobias Krönke]

Thank you for your feedback I used to craft a PR: ​https://github.com/django/django/pull/13051.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 69e0d9c:

Fixed #31689 -- Doc'd caveat about using bulk_create()'s ignore_conflicts on MariDB and MySQL.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 5ba2dfb:

[3.1.x] Fixed #31689 -- Doc'd caveat about using bulk_create()'s ignore_conflicts on MariDB and MySQL.

Backport of 69e0d9c553bb55dde8d7d1d479a78bfa7093f406 from master

[Mariusz Felisiak <felisiak.mariusz@…>]

In 0afe8ba2:

[3.0.x] Fixed #31689 -- Doc'd caveat about using bulk_create()'s ignore_conflicts on MariDB and MySQL.

Backport of 69e0d9c553bb55dde8d7d1d479a78bfa7093f406 from master