[Question] MySQL Microseconds stripping

[Cristiano Coelho]

Hello,

After django 1.8, the mysql backend no longer strips microseconds.
This is giving me some issues when upgrading from 1.7 (I actually upgraded to 1.9 directly), since date times are not stored with micro second precision on mysql, but the queries are sent with them.
As I see it, my only option is to update all existing date time columns of all existing tables, which is quite boring since there are many tables.
Is there a way I can explicitly set the model datetime precision? Will this work with raw queries also? Could this be a global setting or monkey patch? This new behaviour basically breaks any '=' query on date times, at least raw queries (I haven't tested the others) since it sends micro seconds which are not stripped down.

[Cristiano Coelho]

Also I would like to add, that even if the mysql column is a 0 fractional datetime column, and you send a datetime with some fraction in it (which is what django does right now), it won't handle it correctly (ie trim the fraction since the actual column has no fraction) but instead just try to match the fractional date. This makes me think if this might be a bug with mysql...

[Erik Cederstrand]

MySQL as of version 5.6.4 (and MariaDB) is able to store microseconds in datetime fields, but you need to set the precision when you create the column. In Django, this should "just work". Which version of MySQL are you using, and are your columns created as DATETIME(6)?

Erik

[Cristiano Coelho]

Erik,
I'm using MySQL 5.6.x and indeed it has microseconds support, but that's not the issue.

The issue is that every datetime column created has no microseconds (since they were created with django 1.7, so it is actually a datetime(0) column) and I would like to keep it that way, however, since django 1.8+ will always send microseconds in the query, inserts will go fine (mysql will just ignore them) but SELECTS will all fail since mysql will not strip microseconds from the where clause (even if the column is defined as 0 datetime, duh, really mysql?), so basically everything that uses datetime equaility in the query stopped working.

As suggested, all datetime columns should be updated to datetime(6) so it works correctly with the new django behaviour, however, I would like to keep datetime columns as they are, since I don't need microseconds, so I'm wondering if there's any way to get back the old django behaviour for mysql, through a setting, or monkey patch (as long as it works for all models and raw queries).

[Erik Cederstrand]

> Den 19. dec. 2015 kl. 13.15 skrev Cristiano Coelho <cristia...@gmail.com>:
>
> Erik,
> I'm using MySQL 5.6.x and indeed it has microseconds support, but that's not the issue.
>
> The issue is that every datetime column created has no microseconds (since they were created with django 1.7, so it is actually a datetime(0) column) and I would like to keep it that way, however, since django 1.8+ will always send microseconds in the query, inserts will go fine (mysql will just ignore them) but SELECTS will all fail since mysql will not strip microseconds from the where clause (even if the column is defined as 0 datetime, duh, really mysql?), so basically everything that uses datetime equaility in the query stopped working.

Can you elaborate on that? You're doing something like:

SELECT foo_date FROM my_table WHERE foo_date = '2015-12-24 12:34:56.123456';

and expecting it to return rows where foo_date is '2015-12-24 12:34:56', but it doesn't?

I'm not sure that's a bug - it's not the least astonishing to me. Why aren't you stripping microseconds from the datetime values before issuing the query, if your data never has microseconds?

Erik

[Aymeric Augustin]

Hello,

If I read Cristiano correctly, he does this:

INSERT INTO my_table (foo_date) VALUES ('2015-12-24 12:34:56.123456');

SELECT foo_date FROM my_table WHERE foo_date = '2015-12-24 12:34:56.123456’;

and he expects it to return the row he just inserted. That wouldn’t be astonishing. In my opinions, either the insert should fail on the select should return the inserted row. (Obviously this isn’t what happens. I kno\ we’re talking about MySQL here.)


The relevant commits landed in 1.8 which was released in April:

https://github.com/django/django/commit/9e746c13e81241fbf1ae64ec118edaa491790046
https://github.com/django/django/commit/22da5f8817ffff3917bcf8a652dce84f382c9202

The release notes say that “new datetime database columns created with Django 1.8 and MySQL 5.6.4 and up will support microseconds”. This means that users end up with “mixed” databases where some date time columns have fractional microseconds and others have not. I don’t think that’s a very good outcome.

I had brought up that question on the ticket, https://code.djangoproject.com/ticket/19716#comment:3 : “what's going to happen for developers upgrading from an older version of Django?” but I didn’t follow up, unfortunately.


Depending on how MySQL handles conversions, this can easily result in bad behavior. For example, let’s assume the following model:

class Foo(models.Model):

updated_at = models.DateTimeField() # field created on old Django or MySQL
admin_updated_at = models.DateTimeField(blank=True, null=True) # field created on newer Django or MySQL

@property
def last_updated_by_admin(self):
return updated_at == admin_updated_at

foo = Foo.objects.get(…)
foo.updated_at = foo.admin_updated_at = timezone.now()
foo.save()
foo.last_updated_by_admin # True

foo = Foo.objects.get(…)
foo.last_updated_by_admin # False


To be fair, this has a lot to do with MySQL’s lax approach to storing data. There’s so many situations where it just throws data away happily that one can’t really expect to read back data written to MySQL.

That said, this change in Django sets up this trap for unsuspecting users. Providing a way for users to declare which fields use the old format won't work because of pluggable apps: they cannot know what version of MySQL their users were running when they first created a given datetime column. The best solution may be to provide a conversion script to upgrade all datetime columns from the old to the new format.


Best regards,

--
Aymeric.

[Erik Cederstrand]

> Den 19. dec. 2015 kl. 16.01 skrev Aymeric Augustin <aymeric....@polytechnique.org>:
>
> To be fair, this has a lot to do with MySQL’s lax approach to storing data. There’s so many situations where it just throws data away happily that one can’t really expect to read back data written to MySQL.
>
> That said, this change in Django sets up this trap for unsuspecting users. Providing a way for users to declare which fields use the old format won't work because of pluggable apps: they cannot know what version of MySQL their users were running when they first created a given datetime column. The best solution may be to provide a conversion script to upgrade all datetime columns from the old to the new format.

One simple solution could be for Christiano to subclass the DateTimeField to handle the microsecond precision explicitly. Something like this to strip:


class DateTimeFieldWithPrecision(DateTimeField):
def __init__(self, *args, **kwargs):
self.precision = kwargs.get('precision', 6)
assert 0 <= self.precision <= 6
super().__init__(*args, **kwargs)

def pre_save(self, model_instance, add):
dt = getattr(model_instance, self.attname)
dt.replace(microsecond=int(dt.microsecond/10**(6-self.precision)))
return dt


Erik

[Shai Berger]

if I get the complaints correctly, something similar would need to be done
when preparing a value for querying.

More generally, I think Christiano just wants "the old field back" -- so, he
has a use-case for a DateTimeField which explicitly does not use second
fractions. We already have a DateTimeField which explicitly does not use day
fractions (DateField), so I suppose we could find sense in that... We would
typically suggest, as Erik implicitly did, that such field be done outside of
Django, but the backward-compatibility issues mentioned by Aymeric make it
quite plausible that such a field will be added to core or contrib.

Shai.

[Cristiano Coelho]

Aymeric is right. I do an insert with microseconds (since that's what django does right now) but mysql has the column defined as datetime(0), so it just strips the microsecond part, however, when doing the select, I'm expecting to get the value I have just inserted, but it doesn't work, since mysql doesn't strip microseconds from the select as it does for the insert. So this is really a mysql issue I guess...

About using a custom datetime field that strips microseconds, that won't work for raw queries I believe, not even .update statements as they ignore pre-save? As the stripping happens (or used to happen) at the sql query compile level.
This is really a bummer, because it seems like the only option is to convert all my datetime columns into datetime(6), which increases the table size and index by around 10%, for something I will never use.

Any other work around that can work with both normal and raw queries? Should I complain at mysql forums?

[Erik Cederstrand]

> Den 20. dec. 2015 kl. 01.04 skrev Cristiano Coelho <cristia...@gmail.com>:
>
> About using a custom datetime field that strips microseconds, that won't work for raw queries I believe, not even .update statements as they ignore pre-save? As the stripping happens (or used to happen) at the sql query compile level.
> This is really a bummer, because it seems like the only option is to convert all my datetime columns into datetime(6), which increases the table size and index by around 10%, for something I will never use. Any other work around that can work with both normal and raw queries?

While I understand that you'd like this to Just Work, you're sending microseconds to the DB, knowing they will get lost, and expecting comparisons to still work *with* microseconds. It's like expecting 12.34 == int(12.34).

Why not strip the microseconds explicitly as soon as you're handed a datetime with microseconds? That way you make it explicit that you really don't want microseconds. That's less head-scratching for the next person to work with your code. Just dt.replace(microsecond=0) all date values before you issue a .filter(), .save(), .update(), .raw() or whatever.

> Should I complain at mysql forums?

You could try, but since Oracle took over, all my reports have been answered with WONTFIX. Anyway, it'll be months or years before you get something you can install on your server.

Erik

[Shai Berger]

On Sunday 20 December 2015 11:47:54 Erik Cederstrand wrote:
>
> Why not strip the microseconds explicitly as soon as you're handed a
> datetime with microseconds? That way you make it explicit that you really
> don't want microseconds. That's less head-scratching for the next person
> to work with your code. Just dt.replace(microsecond=0) all date values
> before you issue a .filter(), .save(), .update(), .raw() or whatever.
>

This could be a workable solution, or you could be asking him to add dozens of
lines of boilerplate. Either way, we should contend with the claim of
backwards-incompatibility. IMO we need to do one of two things:

- Provide a way to restore pre-1.8 behavior of the MySql backend, or
- Document a sensible workaround (and I do not consider "add a line before
every query" to be sensible).

Shai.

[Cristiano Coelho]

Thanks for the suggestion, I think that work around might just add too much code, so I'm probably going the way of converting every datetime column of every table to datetime(6) and afford the extra storage (and probably a little performance impact ?).
I think the documented change might need a little more of attention, and mention something about that any equality query will stop working if you either don't strip microseconds or update datetime columns to datetime(6) (and not even datetime(3) will work...)

[Aymeric Augustin]

2015-12-20 22:57 GMT+01:00 Cristiano Coelho <cristia...@gmail.com>:

Thanks for the suggestion, I think that work around might just add too much code, so I'm probably going the way of converting every datetime column of every table to datetime(6) and afford the extra storage (and probably a little performance impact ?).
I think the documented change might need a little more of attention, and mention something about that any equality query will stop working if you either don't strip microseconds or update datetime columns to datetime(6) (and not even datetime(3) will work...)

If that's the solution we end up recommending -- because the horse has left the barn months ago... -- then we must document it in detail.

This is a large backwards incompatibility that may result in subtle bugs and requires non-trivial steps to fix. It doesn't live up to Django's standards.

--

Aymeric.

[Josh Smeaton]

I think this is a fairly big oversight that should be fixed in the most backwards compatible way, so users don't need to change their code, or only have to change it minimally. I'm with Aymeric here. Does Django have visibility of the field constraints at insert/select queryset time? Ideally Django would handle the differences transparently. If that's not possible then we should have a migration or script that'll do the conversion on behalf of users once off.

./manage.py mysql-upgrade-microseconds && ./manage.py migrate ?

[Cristiano Coelho]

I think a simple setting allowing to use the old behaviour should be enough, shouldn't it? How does it handle other db backends? I'm not sure if oracle has an option for datetime precision, but if it does, it makes sense to have a global setting for datetime precision, as right now you are pretty much forced to always go with a precision of 6 (at least on mysql?) and that might be just too much if you want a simpler datetime.

[Adam Johnson]

Hi,

I've just finished converting all the datetime columns to datetime(6) in our database after an upgrade to MySQL 5.6. We don't use Django migrations, many of these were done manually with pt-online-schema-change. Just catching up on this thread, throwing some ideas in.

Why not strip the microseconds explicitly as soon as you're handed a datetime with microseconds? 

A note here: stripping isn't what MySQL does - it actually rounds. This is the most annoying thing since it breaks a 5.6 replica feeding off a 5.5 master - it's reported as unfixed bug here: https://bugs.mysql.com/bug.php?id=76948 . (Note: MariaDB kept the stripping behaviour).

We actually ended up patchy.patching MySQLdb.times.DateTime2literal so that microseconds never escaped the Python layer in the whole application, whilst we upgraded:

from MySQLdb.times import DateTime2literal

patchy.patch(DateTime2literal, """\

    @@ -1,3 +1,4 @@

     def DateTime2literal(d, c):

         \"""Format a DateTime object as an ISO timestamp.\"""

    +    d = d.replace(microsecond=0)

         return string_literal(format_TIMESTAMP(d), c)

    """)

converting every datetime column of every table to datetime(6) and afford the extra storage (and probably

There's no extra storage usage in fact - the old format of datetime columns in 5.5 was less efficient than the new one in 5.6. Also, whenever you migrate a table that was created on 5.5 with datetime columns on 5.6, they get converted to the new format automatically. 

Does Django have visibility of the field constraints at insert/select queryset time? 

Django could lookup the precision of datetime columns from information_schema.columns , but I don't think it's necessarily a great idea to change the field behaviour to match this.

./manage.py mysql-upgrade-microseconds && ./manage.py migrate ?

If you're imagining a command to generate migrations, you can't do this for third party apps. It might be better to have a command that simply outputs, or runs, the SQL to alter all tables, outside of the migrations framework? I'd've liked that, I ended up using a little script like that here to generate the SQL and then run it manually/with pt-online-schema-change. I'll look at open sourcing it in Django-MySQL so you guys can look at it.

[Adam Johnson]

I've released the forementioned command as part of Django-MySQL, see here: https://django-mysql.readthedocs.org/en/latest/management_commands/fix_datetime_columns.html