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

[sergey.g.aleshin@…]

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

[anonymous]

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

[sorin]

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'

[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

[sorin]

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.

[Ramiro Morales]

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.

[Alex Gaynor]

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.

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

[nbolton]

+1

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

[Aymeric Augustin]

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.