Opened 12 years ago

Closed 12 years ago

#2369 closed defect (duplicate)

[patch] MySQL backend does not handle zero dates

Reported by: jbowtie@… Owned by: Adrian Holovaty
Component: Database layer (models, ORM) Version:
Severity: normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:


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.

Change History (5)

comment:1 Changed 12 years ago by 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.

comment:2 Changed 12 years ago by 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*

comment:3 Changed 12 years ago by 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 ' 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.

comment:4 Changed 12 years ago by wam-djangobug@…

Summary: MySQL backend does not handle zero dates[patch] MySQL backend does not handle zero dates

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

comment:5 Changed 12 years ago by Adrian Holovaty

Resolution: duplicate
Status: newclosed

Duplicate of #2763, which has a patch.

Note: See TracTickets for help on using tickets.
Back to Top