#34639: MySQL 8.0 hangs indefinitely when using the admin search with a Foreign
Key
and Annotate
--------------------------------+--------------------------------------
Reporter: Nicolas Lupien | Owner: nobody
Type: Bug | Status: new
Component: contrib.admin | Version: 4.2
Severity: Normal | Resolution:
Keywords: mysql | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
--------------------------------+--------------------------------------
Description changed by Nicolas Lupien:
Old description:
> We've moved to MySQL 8.0 in order to use Django 4.2 but our production
> system went down and we reverted to using MySQL 5.7 with Django 4.1.
> We've currently found a workaround that I'll add at the end of the bug
> report.
>
> If we use the search function of the admin on model with a foreign key
> and we override ModelAdmin.get_queryset with annotate, the search freezes
> our database. It had the same effect on Google Cloud SQL and on a local
> docker image of MySQL 8.0 and it works fine on both environment when
> using MySQL 5.7.
>
> The code:
>
> models.py
>
> {{{
> class Organization(models.Model):
> name = models.CharField(max_length=255)
>
> class Member(models.Model):
> name = models.CharField(max_length=255)
> organization = models.ForeignKey(Organization,
> on_delete=models.CASCADE, null=True)
> }}}
>
> admin.py
>
> {{{
> class OrganizationAdmin(admin.ModelAdmin):
> search_fields = ["name", "member__name"]
> list_display = ["name", "member_count"]
>
> class Meta:
> model = models.Organization
>
> def get_queryset(self, request):
> return super().get_queryset(request).annotate(Count("member"))
>
> def member_count(self, instance):
> return instance.member__count
> }}}
>
> I found that the ChangeList applies the override to get_queryset
> containing the annotate multiple times making the query extremely
> expensive. Give only 500 members it goes through 125,000,000 (500 * 500 *
> 500) rows.
>
> The workaround: If we override the ChangeList queryset, the call to
> annotate happens only once and the query is fine.
>
> {{{
> class CustomChangeList(ChangeList):
> def get_queryset(self, request):
> return
> super().get_queryset(request).annotate(Count("locker_connectors"))
>
> class OrganizationAdmin(admin.ModelAdmin):
> search_fields = ["name", "member__name"]
> list_display = ["name", "member_count"]
>
> class Meta:
> model = models.Organization
>
> def member_count(self, instance):
> return instance.member__count
>
> def get_changelist(self, request, **kwargs):
> return CustomChangeList
> }}}
>
> I created a repo with more details and the complete steps to reproduce
> the issue: https://github.com/betaflag/django-sqlbugdemo
New description:
We've moved to MySQL 8.0 in order to use Django 4.2 but our production
system went down and we reverted to using MySQL 5.7 with Django 4.1. We've
currently found a workaround that I'll add at the end of the bug report.
If we use the search function of the admin on model with a foreign key and
we override ModelAdmin.get_queryset with annotate, the search freezes our
database. It had the same effect on Google Cloud SQL and on a local docker
image of MySQL 8.0 and it works fine on both environment when using MySQL
5.7.
The code:
models.py
{{{
class Organization(models.Model):
name = models.CharField(max_length=255)
class Member(models.Model):
name = models.CharField(max_length=255)
organization = models.ForeignKey(Organization,
on_delete=models.CASCADE, null=True)
}}}
admin.py
{{{
class OrganizationAdmin(admin.ModelAdmin):
search_fields = ["name", "member__name"]
list_display = ["name", "member_count"]
class Meta:
model = models.Organization
def get_queryset(self, request):
return super().get_queryset(request).annotate(Count("member"))
def member_count(self, instance):
return instance.member__count
}}}
I found that the ChangeList applies the override to get_queryset
containing the annotate multiple times making the query extremely
expensive. Give only 500 members it goes through 125,000,000 (500 * 500 *
500) rows.
The workaround: If we override the ChangeList queryset, the call to
annotate happens only once and the query is fine.
{{{
class CustomChangeList(ChangeList):
def get_queryset(self, request):
return super().get_queryset(request).annotate(Count("member"))
class OrganizationAdmin(admin.ModelAdmin):
search_fields = ["name", "member__name"]
list_display = ["name", "member_count"]
class Meta:
model = models.Organization
def member_count(self, instance):
return instance.member__count
def get_changelist(self, request, **kwargs):
return CustomChangeList
}}}
I created a repo with more details and the complete steps to reproduce the
issue: https://github.com/betaflag/django-sqlbugdemo
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34639#comment:3>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018897450523-c6b9a108-46ac-45a2-8c83-30c8c796b2e7-000000%40eu-central-1.amazonses.com.
