[patch] Inspect db crashes with MySQL date "0000-00-00" - Quick fix included

[mvassallo+django@…]

When using inspectdb to create a model of a legacy database (s postnuke stuff) the program encounters a timestamp value of "0000-00-00". This is valid in MySQL, but the code hangs on it, so i just added an if. It could be more elegant, but it works.
This is the patch:

--- util.py 2006-09-19 02:53:48.000000000 -0300
+++ util.py-patched 2006-09-19 02:42:39.000000000 -0300
@@ -43,6 +43,7 @@

###############################################

def typecast_date(s):

+ if s == "0000-00-00" : return None

return s and datetime.date(*map(int, s.split('-'))) or None # returns None if s is null

def typecast_time(s): # does NOT store time zone information

[wam-djangobug@…]

The patch above only handled dates, but date-timestamp columns were still causing an error. I've attached an updated diff (against subversion revision 3735) of django/trunk/django/db/backends/util.py which applies the same strategy (special case if clause) for both Date and DateTime fields.

[wam-djangobug@…]

Patch to address MySQL's NULL value format of time/date stamps.

[wam-djangobug@…]

same as prior attachment, but minus the debugging print statement.

[wam-djangobug@…]

Per comment on the attachment page, I'm updating the summary. Apparently, you can't make a change to the ticket properties without an associated comment or Trac thinks it's a spam posting.

Note that this bug/fix is a duplicate of ticket:2369

[wam-djangobug@…]

alternative patch which limits change to only affecting the mysql backend.

[wam-djangobug@…]

Update to prior patch to handle Zero times, as well as date and datetime stamps

[wam-djangobug@…]

same content of last patch, but with different name and diff options to get interactive viewing to work

[Adrian Holovaty]

The latest patch (django-mysql-zero-typcast-ticket2763.diff) looks good for committing, but I'm holding off on committing because the Django/Oracle sprint is happening right now, and they're heavily refactoring that general area of the code. Let's check it in after they're done with the Oracle stuff. Thanks for the patch!

[mmccarty@…]

Is this patch in the trunk?

[mmccarty@…]

nm... I didn't see the comment on 11/04/06 by adrian.

[Simon G. <dev@…>]

Marked as ready for checkin as per Adrian's comment, but patch possibly needs improvement if there were any major changes post-sprint (unlikely)

[Jens]

I think i have a similar problem. I got the error "year is out of range".

My table shows like this:

CREATE TABLE `pylucid_groups` (
`id` int(11) NOT NULL auto_increment,
`pluginID` int(11) NOT NULL default '0',
`name` varchar(50) collate latin1_general_ci NOT NULL default '',
`section` varchar(50) collate latin1_general_ci NOT NULL default '',
`description` varchar(50) collate latin1_general_ci NOT NULL default '',
`lastupdatetime` datetime NOT NULL default '0000-00-00 00:00:00',
`lastupdateby` int(11) default NULL,
`createtime` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

I have values in this table with dattime '0000-00-00 00:00:00'.
inspectdb crashes at this point:

  File ".\django\db\backends\mysql\introspection.py", line 15, in get_table_description
    cursor.execute("SELECT * FROM %s LIMIT 1" % quote_name(table_name))

Quick and dirty hack at this point ​http://code.djangoproject.com/browser/django/trunk/django/core/management.py?rev=4533#L762

Index: management.py
===================================================================
--- management.py	(revision 4542)
+++ management.py	(working copy)
@@ -762,6 +762,9 @@
             relations = introspection_module.get_relations(cursor, table_name)
         except NotImplementedError:
             relations = {}
+        except Exception, e:
+            yield "Error: %s" % e
+            continue
         try:
             indexes = introspection_module.get_indexes(cursor, table_name)
         except NotImplementedError:

Yes, this fixed nothing, but i have the result from the other tables ;)

[anonymous]

Replying to Jens:

I think i have a similar problem. I got the error "year is out of range".

Sorry, i found this ​http://code.djangoproject.com/ticket/443

[Andy Dustman <farcepest@…>]

The patch on #2635 will fix this issue.

[Malcolm Tredinnick]

This seems to be fixed in the current mysql backend. So, as Andy predicted, #2635 seems to have taken care of it.