Mysql TimeZone problems

[info@…]

I am using Python3.2, django 1.6 and the connector from MySQL

I have a model object with a date

   q=Order.objects.datetimes('date','year')
   q[0]

This results in the following error:

  File "<console>", line 1, in <module>
  File "/home/dinne/.virtualenvs/myprojectenv/lib/python3.2/site-packages/django/db/models/query.py", line 132, in __getitem__
    return list(qs)[0]
  File "/home/dinne/.virtualenvs/myprojectenv/lib/python3.2/site-packages/django/db/models/query.py", line 96, in __iter__
    self._fetch_all()
  File "/home/dinne/.virtualenvs/myprojectenv/lib/python3.2/site-packages/django/db/models/query.py", line 854, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/home/dinne/.virtualenvs/myprojectenv/lib/python3.2/site-packages/django/db/models/sql/compiler.py", line 1108, in results_iter
    raise ValueError("Database returned an invalid value "
ValueError: Database returned an invalid value in QuerySet.datetimes(). Are time zone definitions for your database and pytz installed?

I have pytz installed and have loaded the tables in mysql. In the log I see a following query:

SELECT DISTINCT CAST(DATE_FORMAT(CONVERT_TZ(`beadmixer_order`.`date`, 'UTC', 'UTC'), '%%Y-01-01 00:00:00') AS DATETIME) FROM `beadmixer_order` ORDER BY 1 DESC LIMIT 21; args=('UTC',)

The %%Y seems to be the culprit:

>>> from django.db import connection
>>> cursor = connection.cursor()
>>> cursor.execute("SELECT DISTINCT CAST(DATE_FORMAT(CONVERT_TZ(`abc_order`.`date`, 'UTC', 'UTC'), '%Y-01-01 00:00:00') AS DATETIME) FROM `abc_order` ORDER BY 1 DESC LIMIT 21")
>>> str(cursor.fetchone())
'(datetime.datetime(2014, 1, 1, 0, 0, tzinfo=<UTC>),)'
>>> cursor.execute("SELECT DISTINCT CAST(DATE_FORMAT(CONVERT_TZ(`abc_order`.`date`, 'UTC', 'UTC'), '%%Y-01-01 00:00:00') AS DATETIME) FROM `abc_order` ORDER BY 1 DESC LIMIT 21")
>>> str(cursor.fetchone())
'(None,)'

I tried a small modification:

The code for mysql.connector.django.base

def datetime_trunc_sql(self, lookup_type, field_name, tzname):
        # Django 1.6
        if settings.USE_TZ:
            field_name = "CONVERT_TZ({0}, 'UTC', %s)".format(field_name)
            params = [tzname]
        else:
            params = []
        fields = ['year', 'month', 'day', 'hour', 'minute', 'second']
        #my modification
        format_ = ('%Y-', '%m', '-%%d', ' %H:', '%%i', ':%%s')
        #format_ = ('%%Y-', '%%m', '-%%d', ' %%H:', '%%i', ':%%s')
        format_def = ('0000-', '01', '-01', ' 00:', '00', ':00')
        try:
            i = fields.index(lookup_type) + 1
        except ValueError:
            sql = field_name
        else:
            format_str = ''.join([f for f in format_[:i]] +
                                 [f for f in format_def[i:]])
            sql = "CAST(DATE_FORMAT({0}, '{1}') AS DATETIME)".format(
                field_name, format_str)
        return sql, params

This seems to solve some problems:

>>> q=Order.objects.datetimes('date','year')
>>> q[0]
datetime.datetime(2014, 1, 1, 0, 0, tzinfo=<UTC>)

This is ok. But the following still fails:

>>> q=Order.objects.datetimes('date','day')
>>> q[0]
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/home/dinne/.virtualenvs/myprojectenv/lib/python3.2/site-packages/django/db/models/query.py", line 132, in __getitem__
    return list(qs)[0]
  File "/home/dinne/.virtualenvs/myprojectenv/lib/python3.2/site-packages/django/db/models/query.py", line 96, in __iter__
    self._fetch_all()
  File "/home/dinne/.virtualenvs/myprojectenv/lib/python3.2/site-packages/django/db/models/query.py", line 854, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/home/dinne/.virtualenvs/myprojectenv/lib/python3.2/site-packages/django/db/models/sql/compiler.py", line 1108, in results_iter
    raise ValueError("Database returned an invalid value "
ValueError: Database returned an invalid value in QuerySet.datetimes(). Are time zone definitions for your database and pytz installed?

The corresponding query:
SELECT DISTINCT CAST(DATE_FORMAT(CONVERT_TZ(`beadmixer_order`.`date`, 'UTC', 'UTC'), '%Y-%m-%%d 00:00:00') AS DATETIME) FROM `beadmixer_order` ORDER BY 1 ASC LIMIT 1; args=('UTC',)

I can't change %%d in %d in 'becamysql.connector.django.base' because this results in a formatting exception.

The code in django.db.backends.mysql

def datetime_trunc_sql(self, lookup_type, field_name, tzname):
        if settings.USE_TZ:
            field_name = "CONVERT_TZ(%s, 'UTC', %%s)" % field_name
            params = [tzname]
        else:
            params = []
        fields = ['year', 'month', 'day', 'hour', 'minute', 'second']
        format = ('%%Y-', '%%m', '-%%d', ' %%H:', '%%i', ':%%s') # Use double percents to escape.
        format_def = ('0000-', '01', '-01', ' 00:', '00', ':00')
        try:
            i = fields.index(lookup_type) + 1
        except ValueError:
            sql = field_name
        else:
            format_str = ''.join([f for f in format[:i]] + [f for f in format_def[i:]])
            sql = "CAST(DATE_FORMAT(%s, '%s') AS DATETIME)" % (field_name, format_str)
        return sql, params

seems similar enough to have the same problems.

[Tim Graham]

Last time I tried (several months ago), the Django test suite did not pass using the MySQL connector. Thus, I am not sure we should be recommending it in our docs (or at least add some caveats).

Besides this issue, does it mostly work for you?

[anonymous]

I have had no other problems using the MySQL connector. I started out using Django with the included MySQL backend. However that one had problems in Python 3.

Furthermore the code for eg. datetime_trunc_sql seems very similar, so I wondered if the same problem could exist in the default MySQL backend as well.

[anonymous]

I can reproduce the test failure of datetimes.tests.DateTimesTests.test_21432 on Django 1.6 with Connector/Python 1.1.6. Not sure if the bug is in our code or the connector, but in any case, I'm going to mark this as a duplicate of #12500.

[anonymous]

modify function as_sql in compiler.py as
return ' '.join(result).replace('%%','%'), tuple(params)