Code

Opened 8 years ago

Closed 7 years ago

#2763 closed defect (fixed)

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

Reported by: mvassallo+django@… Owned by: adrian
Component: django-admin.py inspectdb Version:
Severity: normal Keywords: inspectdb
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: yes Patch needs improvement: yes
Easy pickings: UI/UX:

Description

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

Attachments (5)

mysql-timedate-null-value-inspectdb.patch (937 bytes) - added by wam-djangobug@… 8 years ago.
Patch to address MySQL's NULL value format of time/date stamps.
mysql-timedate-null-value-inspectdb-2.patch (924 bytes) - added by wam-djangobug@… 8 years ago.
same as prior attachment, but minus the debugging print statement.
django-mysql-zero-typcast-ticket2763.patch (1.4 KB) - added by wam-djangobug@… 7 years ago.
alternative patch which limits change to only affecting the mysql backend.
django-mysql-zero-typcast-ticket2763.2.patch (2.3 KB) - added by wam-djangobug@… 7 years ago.
Update to prior patch to handle Zero times, as well as date and datetime stamps
django-mysql-zero-typcast-ticket2763.diff (1.6 KB) - added by wam-djangobug@… 7 years ago.
same content of last patch, but with different name and diff options to get interactive viewing to work

Download all attachments as: .zip

Change History (17)

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

Changed 8 years ago by wam-djangobug@…

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

Changed 8 years ago by wam-djangobug@…

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

comment:2 Changed 8 years ago by wam-djangobug@…

  • Summary changed from Inspect db crashes with MySQL date "0000-00-00" - Quick fix included to [patch] Inspect db crashes with MySQL date "0000-00-00" - Quick fix included

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

Changed 7 years ago by wam-djangobug@…

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

Changed 7 years ago by wam-djangobug@…

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

Changed 7 years ago by wam-djangobug@…

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

comment:3 Changed 7 years ago by adrian

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!

comment:4 Changed 7 years ago by adrian

  • Component changed from django-admin.py to django-admin.py inspectdb

comment:5 Changed 7 years ago by mmccarty@…

Is this patch in the trunk?

comment:6 Changed 7 years ago by mmccarty@…

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

comment:7 Changed 7 years ago by Simon G. <dev@…>

  • Patch needs improvement set
  • Triage Stage changed from Unreviewed to Ready for checkin

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

comment:8 Changed 7 years ago by Simon G. <dev@…>

  • Needs tests set

comment:9 follow-up: Changed 7 years ago by 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 ;)

comment:10 in reply to: ↑ 9 Changed 7 years ago by 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

comment:11 Changed 7 years ago by Andy Dustman <farcepest@…>

The patch on #2635 will fix this issue.

comment:12 Changed 7 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from new to closed

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.