#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 get_queryset(self, request):
> return super().get_queryset(request).annotate(Count("member"))
>
> 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("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
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34639#comment:2>
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018897425ea6-9a08a0d4-040d-4e53-b96e-b714a6a89057-000000%40eu-central-1.amazonses.com.
