#34639: MySQL 8.0 hand 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 | Keywords: mysql
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
------------------------------------------+------------------------
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
--
Ticket URL: <https://code.djangoproject.com/ticket/34639>
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/010701889740c9a4-56c33721-3e58-4c4c-bcdb-a0bcabd0e3f9-000000%40eu-central-1.amazonses.com.
