[patch] MySQL backend does not handle zero dates

[jbowtie@…]

The MySQL backend installs custom conversions for DateFields and DateTimeFields. These conversion functions fail when parsing zero dates with "ValueError: year is out of range".

Solution one is to allow default MySQL parsing, which transparently treats zero dates as NULL and converts valid dates to datetime objects.
Solution two is to modify the conversion functions to return None when encountering a zero date.

[Malcolm Tredinnick]

I would prefer solution three: fix the places we are passing bad data through to the backend and continue to correctly raise an error when a bad date is passed in. Zero is not a date, so treating it as one is treating the symptom, rather than the problem.

Do you know how we are getting to the point of passing in zero dates? Is it a problem with the form field to Python conversion or somewhere else? It's going to be a little hard to debug the root cause of this without an example of how it can fail without the user entering bad data.

[jbowtie@…]

It's a "feature" of MySQL. The zero date is a sentinel value that is used in place of NULL. According to the docs, passing in a NULL actually sets the field to the current date and time.

It's important to note that the user can't set this directly, it can only be set as a column default. While it's possible that 5.x may have some option that can switch this behavior off, older versions do not.

This also seems to affect introspection behavior when building Django interfaces to replace PHP code. *cough*

[wam-djangobug@…]

I'll add my two cents here as well. It doesn't make any difference how the value got into the field, as a stated goal of Django is to be able to integrate with legacy databases. I just ran 'django-admin.py inspectdb' and got a traceback with 'ValueError: year is out of range' half way through the conversion. I agree it's not ideal, but it should be supported.

[wam-djangobug@…]

As an FYI, I just noticed that someone has contributed a patch to this issue in ticket:2763. It didn't address datetime fields though, so I've updated the patch. Using the patched version of util.py results in inspectdb against my legacy database no longer tracebacking.

Note, I'm updating the summary of this ticket to include [patch] since trac is giving me Akismet rejected spam when I attempt to change the summary on #2763.

[Adrian Holovaty]

Duplicate of #2763, which has a patch.