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.patch <https://github.com/adamchainz/patchy>ing 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. On Tuesday, December 22, 2015 at 2:56:41 AM UTC, Cristiano Coelho wrote: > > 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. > > El lunes, 21 de diciembre de 2015, 19:54:29 (UTC-3), Josh Smeaton escribió: >> >> 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 ? >> >> >> On Monday, 21 December 2015 19:39:44 UTC+11, Aymeric Augustin wrote: >>> >>> 2015-12-20 22:57 GMT+01:00 Cristiano Coelho <[email protected]>: >>> >>>> 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. >>> >> -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/7e75bce6-5006-4c75-a773-6e24fc19c08b%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
