Hello,
> On 19 déc. 2015, at 09:21, Erik Cederstrand <[email protected]> wrote:
>
>> Den 19. dec. 2015 kl. 13.15 skrev Cristiano Coelho
>> <[email protected]>:
>>
>> 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?
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.
--
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/D1BE240D-C89C-4498-91F6-A48C44AFF4B2%40polytechnique.org.
For more options, visit https://groups.google.com/d/optout.
