Opened 5 years ago

Closed 3 years ago

#11984 closed Bug (wontfix)

Problem with date_hierarchy in Django 1.1 - Truncated incorrect datetime value: '0000-01-01 00:00:00'

Reported by: sergey.g.aleshin@… Owned by: sorin
Component: contrib.admin Version: 1.1
Severity: Normal Keywords:
Cc: sorin Triage Stage: Design decision needed
Has patch: yes Needs documentation: no
Needs tests: yes Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I have simple model:

class News(models.Model):
	text_r = models.TextField(blank=True)
	text_e = models.TextField(blank=True)
	url = models.CharField(max_length=765, blank=True)
	newsdate = models.DateField(null=True, blank=True)
	red = models.IntegerField(null=True, blank=True)
	
	def title_r(self):
		return get_n_chars(self.text_r, 50)
	
	def title_e(self):
		return get_n_chars(self.text_e, 50)
	
	def __unicode__(self):
		return self.title_r() or self.title_e()

And admin interface to it:

from django.contrib import admin
from jsite.models import News

class NewsAdmin(admin.ModelAdmin):
	date_hierarchy = 'newsdate'
	list_display = ('newsdate', 'title_r', 'title_e')
	
admin.site.register(News, NewsAdmin)

Admin interface always fails to display list of objects:

Environment:

Request Method: GET
Request URL: http://bsd2:8000/admin/jsite/news/
Django Version: 1.1
Python Version: 2.5.4
Installed Applications:
['django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.admindocs',
 'django.contrib.admin',
 'django.contrib.sites',
 'django.contrib.flatpages',
 'journals.jsite']
Installed Middleware:
('django.middleware.common.CommonMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.flatpages.middleware.FlatpageFallbackMiddleware')


Template error:
In template /usr/local/lib/python2.5/site-packages/django/contrib/admin/templates/admin/change_list.html, error at line 60
   Caught an exception while rendering: invalid literal for int() with base 10: 'None'
   50 :       {% endif %}


   51 :     {% endblock %}


   52 :     {% if cl.formset.errors %}


   53 :         <p class="errornote">


   54 :         {% blocktrans count cl.formset.errors|length as counter %}Please correct the error below.{% plural %}Please correct the errors below.{% endblocktrans %}


   55 :         </p>


   56 :         <ul class="errorlist">{% for error in cl.formset.non_field_errors %}<li>{{ error }}</li>{% endfor %}</ul>


   57 :     {% endif %}


   58 :     <div class="module{% if cl.has_filters %} filtered{% endif %}" id="changelist">


   59 :       {% block search %}{% search_form cl %}{% endblock %}


   60 :       {% block date_hierarchy %} {% date_hierarchy cl %} {% endblock %}


   61 : 


   62 :       {% block filters %}


   63 :         {% if cl.has_filters %}


   64 :           <div id="changelist-filter">


   65 :             <h2>{% trans 'Filter' %}</h2>


   66 :             {% for spec in cl.filter_specs %}{% admin_list_filter cl spec %}{% endfor %}


   67 :           </div>


   68 :         {% endif %}


   69 :       {% endblock %}


   70 :       


Traceback:
File "/usr/local/lib/python2.5/site-packages/django/core/handlers/base.py" in get_response
  92.                 response = callback(request, *callback_args, **callback_kwargs)
File "/usr/local/lib/python2.5/site-packages/django/contrib/admin/options.py" in wrapper
  226.                 return self.admin_site.admin_view(view)(*args, **kwargs)
File "/usr/local/lib/python2.5/site-packages/django/views/decorators/cache.py" in _wrapped_view_func
  44.         response = view_func(request, *args, **kwargs)
File "/usr/local/lib/python2.5/site-packages/django/contrib/admin/sites.py" in inner
  186.             return view(request, *args, **kwargs)
File "/usr/local/lib/python2.5/site-packages/django/contrib/admin/options.py" in changelist_view
  986.         ], context, context_instance=context_instance)
File "/usr/local/lib/python2.5/site-packages/django/shortcuts/__init__.py" in render_to_response
  20.     return HttpResponse(loader.render_to_string(*args, **kwargs), **httpresponse_kwargs)
File "/usr/local/lib/python2.5/site-packages/django/template/loader.py" in render_to_string
  108.     return t.render(context_instance)
File "/usr/local/lib/python2.5/site-packages/django/template/__init__.py" in render
  178.         return self.nodelist.render(context)
File "/usr/local/lib/python2.5/site-packages/django/template/__init__.py" in render
  779.                 bits.append(self.render_node(node, context))
File "/usr/local/lib/python2.5/site-packages/django/template/debug.py" in render_node
  71.             result = node.render(context)
File "/usr/local/lib/python2.5/site-packages/django/template/loader_tags.py" in render
  97.         return compiled_parent.render(context)
File "/usr/local/lib/python2.5/site-packages/django/template/__init__.py" in render
  178.         return self.nodelist.render(context)
File "/usr/local/lib/python2.5/site-packages/django/template/__init__.py" in render
  779.                 bits.append(self.render_node(node, context))
File "/usr/local/lib/python2.5/site-packages/django/template/debug.py" in render_node
  71.             result = node.render(context)
File "/usr/local/lib/python2.5/site-packages/django/template/loader_tags.py" in render
  97.         return compiled_parent.render(context)
File "/usr/local/lib/python2.5/site-packages/django/template/__init__.py" in render
  178.         return self.nodelist.render(context)
File "/usr/local/lib/python2.5/site-packages/django/template/__init__.py" in render
  779.                 bits.append(self.render_node(node, context))
File "/usr/local/lib/python2.5/site-packages/django/template/debug.py" in render_node
  71.             result = node.render(context)
File "/usr/local/lib/python2.5/site-packages/django/template/loader_tags.py" in render
  24.         result = self.nodelist.render(context)
File "/usr/local/lib/python2.5/site-packages/django/template/__init__.py" in render
  779.                 bits.append(self.render_node(node, context))
File "/usr/local/lib/python2.5/site-packages/django/template/debug.py" in render_node
  71.             result = node.render(context)
File "/usr/local/lib/python2.5/site-packages/django/template/loader_tags.py" in render
  24.         result = self.nodelist.render(context)
File "/usr/local/lib/python2.5/site-packages/django/template/__init__.py" in render
  779.                 bits.append(self.render_node(node, context))
File "/usr/local/lib/python2.5/site-packages/django/template/debug.py" in render_node
  81.             raise wrapped

Exception Type: TemplateSyntaxError at /admin/jsite/news/
Exception Value: Caught an exception while rendering: invalid literal for int() with base 10: 'None'

Original Traceback (most recent call last):
  File "/usr/local/lib/python2.5/site-packages/django/template/debug.py", line 71, in render_node
    result = node.render(context)
  File "/usr/local/lib/python2.5/site-packages/django/template/__init__.py", line 936, in render
    dict = func(*args)
  File "/usr/local/lib/python2.5/site-packages/django/contrib/admin/templatetags/admin_list.py", line 313, in date_hierarchy
    } for year in years]
  File "/usr/local/lib/python2.5/site-packages/django/db/models/query.py", line 106, in _result_iter
    self._fill_cache()
  File "/usr/local/lib/python2.5/site-packages/django/db/models/query.py", line 692, in _fill_cache
    self._result_cache.append(self._iter.next())
  File "/usr/local/lib/python2.5/site-packages/django/db/models/sql/subqueries.py", line 397, in results_iter
    date = typecast_timestamp(str(date))
  File "/usr/local/lib/python2.5/site-packages/django/db/backends/util.py", line 68, in typecast_timestamp
    if not ' ' in s: return typecast_date(s)
  File "/usr/local/lib/python2.5/site-packages/django/db/backends/util.py", line 53, in typecast_date
    return s and datetime.date(*map(int, s.split('-'))) or None # returns None if s is null
ValueError: invalid literal for int() with base 10: 'None'

In fact, typecast_date gots 'None' (as string, not None) as s parameter value

OS: FreeBSD 6.4, Python 2.5.4, Django 1.1 from ports, MySQL 5.1.36

Attachments (1)

django_bug_7383_v1.patch (3.2 KB) - added by sorin 5 years ago.

Download all attachments as: .zip

Change History (14)

comment:1 in reply to: ↑ description Changed 5 years ago by anonymous

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

As i found, error caused by incorrect date in one of table rows
for example, date 0000-01-01 leads to this error

comment:2 Changed 5 years ago by sorin

  • Owner changed from nobody to sorin
  • Status changed from new to assigned
  • Summary changed from Problem with date_hierarchy in Django 1.1 to Problem with date_hierarchy in Django 1.1 - Truncated incorrect datetime value: '0000-01-01 00:00:00'
  • Triage Stage changed from Unreviewed to Accepted

I confirm the same issue on this context:
MySQL db having some datetime fields with '0000-00-00 00:00:00'. When you try to use date_hierarchy on these fields you get:

Caught an exception while rendering: Truncated incorrect datetime value: '0000-01-01 00:00:00'

comment:3 Changed 5 years ago by sorin

  • Owner sorin deleted
  • Status changed from assigned to new

comment:4 Changed 5 years ago by sorin

  • Cc sorin added
  • Triage Stage changed from Accepted to Unreviewed

comment:5 Changed 5 years ago by sorin

I this error is also part of the same bug - shortly django should be able to work with invalid dates from database.

Caught an exception while rendering: invalid literal for int(): None
Original Traceback (most recent call last):
  File "/usr/lib/python2.4/site-packages/django/template/debug.py", line 71, in render_node
    result = node.render(context)
  File "/usr/lib/python2.4/site-packages/django/template/__init__.py", line 936, in render
    dict = func(*args)
  File "/usr/lib/python2.4/site-packages/django/contrib/admin/templatetags/admin_list.py", line 308, in date_hierarchy
    return {
  File "/usr/lib/python2.4/site-packages/django/db/models/query.py", line 106, in _result_iter
    self._fill_cache()
  File "/usr/lib/python2.4/site-packages/django/db/models/query.py", line 692, in _fill_cache
    self._result_cache.append(self._iter.next())
  File "/usr/lib/python2.4/site-packages/django/db/models/sql/subqueries.py", line 397, in results_iter
    date = typecast_timestamp(str(date))
  File "/usr/lib/python2.4/site-packages/django/db/backends/util.py", line 68, in typecast_timestamp
    if not ' ' in s: return typecast_date(s)
  File "/usr/lib/python2.4/site-packages/django/db/backends/util.py", line 53, in typecast_date
    return s and datetime.date(*map(int, s.split('-'))) or None # returns None if s is null
ValueError: invalid literal for int(): None

Changed 5 years ago by sorin

comment:6 Changed 5 years ago by sorin

  • Has patch set
  • Owner set to sorin
  • Status changed from new to assigned

I attached a patch that does solves these errors. These are happening when you have datetime fields on MySQL with value '0000-00-00 00:00:00' and you are trying to use date_hierarchy on them.

comment:7 Changed 5 years ago by ramiro

It could be me, but I don't feel comfortable with having to modify:

  • Code of the MySQL backend
  • An utility function that is used by other two DB backends

just to workaround problems in a contrib app (admin) feature (date_hierarchy), especially when the origin is bad data poorly handled by MySQL.

See also #443.

comment:8 Changed 5 years ago by Alex

I concur with ramiro, the bug is data bug, not a SQL generation bug. I'm a) not convinced the admin should cover for you when your data is invalid, and b) even if it should *the admin* should handle that, not the DB backend.

comment:9 Changed 5 years ago by sorin

First, rememeber that Django is not always used to connect to new databases so changing the values from the database is not always an option. I'm sure other people have legacy databases that are using the 0000-00-00 format instead of NULL for dates. This is something accepted by MySQL and should be accepted by Django also.

Also I don't think that this patch does have a negative impact on any other back-ends - in fact all it does is that it does detect possible errors in order to prevent application crash.

comment:10 Changed 5 years ago by nbolton

+1

I think it would be good for the patch to be committed.

comment:11 Changed 5 years ago by SmileyChris

  • Needs tests set
  • Triage Stage changed from Unreviewed to Design decision needed

comment:12 Changed 4 years ago by julien

  • Severity set to Normal
  • Type set to Bug

comment:13 Changed 3 years ago by aaugustin

  • Easy pickings unset
  • Resolution set to wontfix
  • Status changed from assigned to closed
  • UI/UX unset

I'm not convinced by the arguments above.

Incomplete date values aren't acceptable in Python and Django will never write them to a database. I understand they could exist in a legacy MySQL database, but they can't be interpreted as dates — what's the semantic for 0000-02-29?— so there's not much we can do in Django. Your best chance is probably to write a custom field for such columns, and interpret the values as strings or custom objects.

MySQL is known for its lax requirements on data integrity; Django follows more strict principles; I'm going to close the ticket for this reason.

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