id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34639	MySQL 8.0 hangs indefinitely when using the admin search with a Foreign Key and Annotate	Nicolas Lupien	nobody	"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

"	Bug	new	contrib.admin	4.2	Normal		mysql		Unreviewed	0	0	0	0	0	0