Opened 3 years ago

Last modified 19 months ago

#27864 new Cleanup/optimization

Limit the number of terms in an admin search

Reported by: VINAY KUMAR SHARMA Owned by: nobody
Component: contrib.admin Version: 1.8
Severity: Normal Keywords: mysql
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Anton Samarchyan)

Found this bug when searching with long string:

OperationalError at /posts/posts

(1116, 'Too many tables; MySQL can only use 61 tables in a join')

Request URL:

I've created a CMS system using Django Admin, where models Posts is in app Posts.

Model Posts has an M2M field authors, and this field I've included in search for admin.ModelAdmin:

e.g here:

class Posts(models.Model):
    id = models.AutoField(primary_key=True, db_column='article_id')
    # ... other fields ...
    # Related M2M/Foreign Key fields
    authors = models.ManyToManyField(Author, through='PostAuthors',
                                     through_fields=('post', 'author'))
    subcategories = models.ManyToManyField(SubCategory, through='PostSubcategory',
                                           through_fields=('post', 'subcategory'))

class PostsAdmin(admin.ModelAdmin):
    actions = [delete_selected, republish_selected, add_to_top_stories]
    list_per_page = ADMIN_LIST_PER_PAGE
    list_filter = [PostTypeListFilter, 'datetime', 'is_active', 'is_deleted']
    search_fields = ['authors__name', 'title']

Change History (8)

comment:1 Changed 3 years ago by VINAY KUMAR SHARMA

Component: UncategorizedDatabase layer (models, ORM)
Keywords: mysql added
Type: UncategorizedBug
Version: 1.101.8

comment:2 Changed 3 years ago by Jani Tiainen

Could you create minimal testcase that causes the error?

comment:3 Changed 3 years ago by Tim Graham

Resolution: needsinfo
Status: newclosed

As Jani said, there isn't really enough information here to determine if it's a bug in Django. Ideally the steps to reproduce shouldn't use any third-party apps.

comment:4 Changed 3 years ago by Simon Charette

Tim, I'm confident this is related to this PR.

The thing here is that since search terms are filtered independently each space delimited term results in a JOIN, the problem gets even worse when there's multiple search_fields.

I think limiting the number of search terms wouldn't hurt here.

comment:5 in reply to:  2 Changed 3 years ago by VINAY KUMAR SHARMA

Replying to Jani Tiainen:

Could you create minimal testcase that causes the error?

Test :(

It's really hard for me! I don't write tests. Testing in Django is very difficult part. As this requires very high mem/cpu and configuration. I had tried but it sucks the system with large database. I also tried with fixtures, this also failed!

:( so sorry

comment:6 Changed 3 years ago by Tim Graham

Component: Database layer (models, ORM)contrib.admin
Resolution: needsinfo
Status: closednew
Summary: (1116, 'Too many tables; MySQL can only use 61 tables in a join')Limit the number of terms in an admin search
Triage Stage: UnreviewedAccepted
Type: BugCleanup/optimization

Limiting the number of search terms in the admin seems okay to me, although that's more complexity and another customization point. Some consideration in choosing a sensible default limit will be needed.

comment:7 Changed 3 years ago by Anton Samarchyan

Description: modified (diff)

comment:8 Changed 19 months ago by John Wright

Here is a test for admin_changelist/

    def test_long_search_var(self):                                                                                     
        parent = Parent.objects.create(name='Mary')                                                                     
        Child.objects.create(parent=parent, name='Danielle')                                                            
        Child.objects.create(parent=parent, name='Daniel')                                                              
        m = ParentAdmin(Parent, custom_site)                                                                            
        request = self.factory.get('/parent/', data={SEARCH_VAR: ' '.join(['daniel']*100)})                             
        request.user = self.superuser                                                                                   
        cl = m.get_changelist_instance(request)                                                                         
        # Make sure distinct() was called                                                                               
        self.assertEqual(cl.queryset.count(), 1)                                                                        
ERROR: test_long_search_var (admin_changelist.tests.ChangeListTests)
Traceback (most recent call last):
  File "/usr/lib/python3.7/unittest/", line 59, in testPartExecutor
  File "/usr/lib/python3.7/unittest/", line 615, in run
  File "/source/django/tests/admin_changelist/", line 420, in test_long_search_var
    cl = m.get_changelist_instance(request)
  File "/source/django/django/contrib/admin/", line 742, in get_changelist_instance
  File "/source/django/django/contrib/admin/views/", line 82, in __init__
  File "/source/django/django/contrib/admin/views/", line 210, in get_results
    result_count = paginator.count
  File "/source/django/django/utils/", line 57, in __get__
    res = instance.__dict__[] = self.func(instance)
  File "/source/django/django/core/", line 91, in count
    return c()
  File "/source/django/django/db/models/", line 391, in count
    return self.query.get_count(using=self.db)
  File "/source/django/django/db/models/sql/", line 504, in get_count
    number = obj.get_aggregation(using, ['__count'])['__count']
  File "/source/django/django/db/models/sql/", line 489, in get_aggregation
    result = compiler.execute_sql(SINGLE)
  File "/source/django/django/db/models/sql/", line 1080, in execute_sql
    cursor.execute(sql, params)
  File "/source/django/django/db/backends/", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/source/django/django/db/backends/", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/source/django/django/db/backends/", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/source/django/django/db/", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/source/django/django/db/backends/", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/source/django/django/db/backends/sqlite3/", line 383, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: at most 64 tables in a join

Ran 56 tests in 0.627s

FAILED (errors=1, skipped=1)

print in django/db/backends/sqlite3/ shows this SQL and params:

SELECT COUNT(*) FROM (SELECT DISTINCT "admin_changelist_parent"."id" AS Col1, "admin_changelist_parent"."name" AS Col2 FROM "admin_changelist_parent" INNER JOIN "admin_changelist_child" ON ("admin_changelist_parent"."id" = "admin_changelist_child"."parent_id") INNER JOIN "admin_changelist_child" T3 ON ("admin_changelist_parent"."id" = T3."parent_id") INNER JOIN "admin_changelist_child" T4 ON ("admin_changelist_parent"."id" = T4."parent_id") INNER JOIN "admin_changelist_child" T5 ON ("admin_changelist_parent"."id" = T5."parent_id") INNER JOIN "admin_changelist_child" T6 ON ("admin_changelist_parent"."id" = T6."parent_id") INNER JOIN "admin_changelist_child" T7 ON ("admin_changelist_parent"."id" = T7."parent_id") INNER JOIN "admin_changelist_child" T8 ON ("admin_changelist_parent"."id" = T8."parent_id") INNER JOIN "admin_changelist_child" T9 ON ("admin_changelist_parent"."id" = T9."parent_id") INNER JOIN "admin_changelist_child" T10 ON ("admin_changelist_parent"."id" = T10."parent_id") INNER JOIN "admin_changelist_child" T11 ON ("admin_changelist_parent"."id" = T11."parent_id") INNER JOIN "admin_changelist_child" T12 ON ("admin_changelist_parent"."id" = T12."parent_id") INNER JOIN "admin_changelist_child" T13 ON ("admin_changelist_parent"."id" = T13."parent_id") INNER JOIN "admin_changelist_child" T14 ON ("admin_changelist_parent"."id" = T14."parent_id") INNER JOIN "admin_changelist_child" T15 ON ("admin_changelist_parent"."id" = T15."parent_id") INNER JOIN "admin_changelist_child" T16 ON ("admin_changelist_parent"."id" = T16."parent_id") INNER JOIN "admin_changelist_child" T17 ON ("admin_changelist_parent"."id" = T17."parent_id") INNER JOIN "admin_changelist_child" T18 ON ("admin_changelist_parent"."id" = T18."parent_id") INNER JOIN "admin_changelist_child" T19 ON ("admin_changelist_parent"."id" = T19."parent_id") INNER JOIN "admin_changelist_child" T20 ON ("admin_changelist_parent"."id" = T20."parent_id") INNER JOIN "admin_changelist_child" T21 ON ("admin_changelist_parent"."id" = T21."parent_id") INNER JOIN "admin_changelist_child" T22 ON ("admin_changelist_parent"."id" = T22."parent_id") INNER JOIN "admin_changelist_child" T23 ON ("admin_changelist_parent"."id" = T23."parent_id") INNER JOIN "admin_changelist_child" T24 ON ("admin_changelist_parent"."id" = T24."parent_id") INNER JOIN "admin_changelist_child" T25 ON ("admin_changelist_parent"."id" = T25."parent_id") INNER JOIN "admin_changelist_child" T26 ON ("admin_changelist_parent"."id" = T26."parent_id") INNER JOIN "admin_changelist_child" T27 ON ("admin_changelist_parent"."id" = T27."parent_id") INNER JOIN "admin_changelist_child" T28 ON ("admin_changelist_parent"."id" = T28."parent_id") INNER JOIN "admin_changelist_child" T29 ON ("admin_changelist_parent"."id" = T29."parent_id") INNER JOIN "admin_changelist_child" T30 ON ("admin_changelist_parent"."id" = T30."parent_id") INNER JOIN "admin_changelist_child" T31 ON ("admin_changelist_parent"."id" = T31."parent_id") INNER JOIN "admin_changelist_child" T32 ON ("admin_changelist_parent"."id" = T32."parent_id") INNER JOIN "admin_changelist_child" T33 ON ("admin_changelist_parent"."id" = T33."parent_id") INNER JOIN "admin_changelist_child" T34 ON ("admin_changelist_parent"."id" = T34."parent_id") INNER JOIN "admin_changelist_child" T35 ON ("admin_changelist_parent"."id" = T35."parent_id") INNER JOIN "admin_changelist_child" T36 ON ("admin_changelist_parent"."id" = T36."parent_id") INNER JOIN "admin_changelist_child" T37 ON ("admin_changelist_parent"."id" = T37."parent_id") INNER JOIN "admin_changelist_child" T38 ON ("admin_changelist_parent"."id" = T38."parent_id") INNER JOIN "admin_changelist_child" T39 ON ("admin_changelist_parent"."id" = T39."parent_id") INNER JOIN "admin_changelist_child" T40 ON ("admin_changelist_parent"."id" = T40."parent_id") INNER JOIN "admin_changelist_child" T41 ON ("admin_changelist_parent"."id" = T41."parent_id") INNER JOIN "admin_changelist_child" T42 ON ("admin_changelist_parent"."id" = T42."parent_id") INNER JOIN "admin_changelist_child" T43 ON ("admin_changelist_parent"."id" = T43."parent_id") INNER JOIN "admin_changelist_child" T44 ON ("admin_changelist_parent"."id" = T44."parent_id") INNER JOIN "admin_changelist_child" T45 ON ("admin_changelist_parent"."id" = T45."parent_id") INNER JOIN "admin_changelist_child" T46 ON ("admin_changelist_parent"."id" = T46."parent_id") INNER JOIN "admin_changelist_child" T47 ON ("admin_changelist_parent"."id" = T47."parent_id") INNER JOIN "admin_changelist_child" T48 ON ("admin_changelist_parent"."id" = T48."parent_id") INNER JOIN "admin_changelist_child" T49 ON ("admin_changelist_parent"."id" = T49."parent_id") INNER JOIN "admin_changelist_child" T50 ON ("admin_changelist_parent"."id" = T50."parent_id") INNER JOIN "admin_changelist_child" T51 ON ("admin_changelist_parent"."id" = T51."parent_id") INNER JOIN "admin_changelist_child" T52 ON ("admin_changelist_parent"."id" = T52."parent_id") INNER JOIN "admin_changelist_child" T53 ON ("admin_changelist_parent"."id" = T53."parent_id") INNER JOIN "admin_changelist_child" T54 ON ("admin_changelist_parent"."id" = T54."parent_id") INNER JOIN "admin_changelist_child" T55 ON ("admin_changelist_parent"."id" = T55."parent_id") INNER JOIN "admin_changelist_child" T56 ON ("admin_changelist_parent"."id" = T56."parent_id") INNER JOIN "admin_changelist_child" T57 ON ("admin_changelist_parent"."id" = T57."parent_id") INNER JOIN "admin_changelist_child" T58 ON ("admin_changelist_parent"."id" = T58."parent_id") INNER JOIN "admin_changelist_child" T59 ON ("admin_changelist_parent"."id" = T59."parent_id") INNER JOIN "admin_changelist_child" T60 ON ("admin_changelist_parent"."id" = T60."parent_id") INNER JOIN "admin_changelist_child" T61 ON ("admin_changelist_parent"."id" = T61."parent_id") INNER JOIN "admin_changelist_child" T62 ON ("admin_changelist_parent"."id" = T62."parent_id") INNER JOIN "admin_changelist_child" T63 ON ("admin_changelist_parent"."id" = T63."parent_id") INNER JOIN "admin_changelist_child" T64 ON ("admin_changelist_parent"."id" = T64."parent_id") INNER JOIN "admin_changelist_child" T65 ON ("admin_changelist_parent"."id" = T65."parent_id") INNER JOIN "admin_changelist_child" T66 ON ("admin_changelist_parent"."id" = T66."parent_id") INNER JOIN "admin_changelist_child" T67 ON ("admin_changelist_parent"."id" = T67."parent_id") INNER JOIN "admin_changelist_child" T68 ON ("admin_changelist_parent"."id" = T68."parent_id") INNER JOIN "admin_changelist_child" T69 ON ("admin_changelist_parent"."id" = T69."parent_id") INNER JOIN "admin_changelist_child" T70 ON ("admin_changelist_parent"."id" = T70."parent_id") INNER JOIN "admin_changelist_child" T71 ON ("admin_changelist_parent"."id" = T71."parent_id") INNER JOIN "admin_changelist_child" T72 ON ("admin_changelist_parent"."id" = T72."parent_id") INNER JOIN "admin_changelist_child" T73 ON ("admin_changelist_parent"."id" = T73."parent_id") INNER JOIN "admin_changelist_child" T74 ON ("admin_changelist_parent"."id" = T74."parent_id") INNER JOIN "admin_changelist_child" T75 ON ("admin_changelist_parent"."id" = T75."parent_id") INNER JOIN "admin_changelist_child" T76 ON ("admin_changelist_parent"."id" = T76."parent_id") INNER JOIN "admin_changelist_child" T77 ON ("admin_changelist_parent"."id" = T77."parent_id") INNER JOIN "admin_changelist_child" T78 ON ("admin_changelist_parent"."id" = T78."parent_id") INNER JOIN "admin_changelist_child" T79 ON ("admin_changelist_parent"."id" = T79."parent_id") INNER JOIN "admin_changelist_child" T80 ON ("admin_changelist_parent"."id" = T80."parent_id") INNER JOIN "admin_changelist_child" T81 ON ("admin_changelist_parent"."id" = T81."parent_id") INNER JOIN "admin_changelist_child" T82 ON ("admin_changelist_parent"."id" = T82."parent_id") INNER JOIN "admin_changelist_child" T83 ON ("admin_changelist_parent"."id" = T83."parent_id") INNER JOIN "admin_changelist_child" T84 ON ("admin_changelist_parent"."id" = T84."parent_id") INNER JOIN "admin_changelist_child" T85 ON ("admin_changelist_parent"."id" = T85."parent_id") INNER JOIN "admin_changelist_child" T86 ON ("admin_changelist_parent"."id" = T86."parent_id") INNER JOIN "admin_changelist_child" T87 ON ("admin_changelist_parent"."id" = T87."parent_id") INNER JOIN "admin_changelist_child" T88 ON ("admin_changelist_parent"."id" = T88."parent_id") INNER JOIN "admin_changelist_child" T89 ON ("admin_changelist_parent"."id" = T89."parent_id") INNER JOIN "admin_changelist_child" T90 ON ("admin_changelist_parent"."id" = T90."parent_id") INNER JOIN "admin_changelist_child" T91 ON ("admin_changelist_parent"."id" = T91."parent_id") INNER JOIN "admin_changelist_child" T92 ON ("admin_changelist_parent"."id" = T92."parent_id") INNER JOIN "admin_changelist_child" T93 ON ("admin_changelist_parent"."id" = T93."parent_id") INNER JOIN "admin_changelist_child" T94 ON ("admin_changelist_parent"."id" = T94."parent_id") INNER JOIN "admin_changelist_child" T95 ON ("admin_changelist_parent"."id" = T95."parent_id") INNER JOIN "admin_changelist_child" T96 ON ("admin_changelist_parent"."id" = T96."parent_id") INNER JOIN "admin_changelist_child" T97 ON ("admin_changelist_parent"."id" = T97."parent_id") INNER JOIN "admin_changelist_child" T98 ON ("admin_changelist_parent"."id" = T98."parent_id") INNER JOIN "admin_changelist_child" T99 ON ("admin_changelist_parent"."id" = T99."parent_id") INNER JOIN "admin_changelist_child" T100 ON ("admin_changelist_parent"."id" = T100."parent_id") INNER JOIN "admin_changelist_child" T101 ON ("admin_changelist_parent"."id" = T101."parent_id") WHERE ("admin_changelist_child"."name" LIKE ? ESCAPE '\' AND T3."name" LIKE ? ESCAPE '\' AND T4."name" LIKE ? ESCAPE '\' AND T5."name" LIKE ? ESCAPE '\' AND T6."name" LIKE ? ESCAPE '\' AND T7."name" LIKE ? ESCAPE '\' AND T8."name" LIKE ? ESCAPE '\' AND T9."name" LIKE ? ESCAPE '\' AND T10."name" LIKE ? ESCAPE '\' AND T11."name" LIKE ? ESCAPE '\' AND T12."name" LIKE ? ESCAPE '\' AND T13."name" LIKE ? ESCAPE '\' AND T14."name" LIKE ? ESCAPE '\' AND T15."name" LIKE ? ESCAPE '\' AND T16."name" LIKE ? ESCAPE '\' AND T17."name" LIKE ? ESCAPE '\' AND T18."name" LIKE ? ESCAPE '\' AND T19."name" LIKE ? ESCAPE '\' AND T20."name" LIKE ? ESCAPE '\' AND T21."name" LIKE ? ESCAPE '\' AND T22."name" LIKE ? ESCAPE '\' AND T23."name" LIKE ? ESCAPE '\' AND T24."name" LIKE ? ESCAPE '\' AND T25."name" LIKE ? ESCAPE '\' AND T26."name" LIKE ? ESCAPE '\' AND T27."name" LIKE ? ESCAPE '\' AND T28."name" LIKE ? ESCAPE '\' AND T29."name" LIKE ? ESCAPE '\' AND T30."name" LIKE ? ESCAPE '\' AND T31."name" LIKE ? ESCAPE '\' AND T32."name" LIKE ? ESCAPE '\' AND T33."name" LIKE ? ESCAPE '\' AND T34."name" LIKE ? ESCAPE '\' AND T35."name" LIKE ? ESCAPE '\' AND T36."name" LIKE ? ESCAPE '\' AND T37."name" LIKE ? ESCAPE '\' AND T38."name" LIKE ? ESCAPE '\' AND T39."name" LIKE ? ESCAPE '\' AND T40."name" LIKE ? ESCAPE '\' AND T41."name" LIKE ? ESCAPE '\' AND T42."name" LIKE ? ESCAPE '\' AND T43."name" LIKE ? ESCAPE '\' AND T44."name" LIKE ? ESCAPE '\' AND T45."name" LIKE ? ESCAPE '\' AND T46."name" LIKE ? ESCAPE '\' AND T47."name" LIKE ? ESCAPE '\' AND T48."name" LIKE ? ESCAPE '\' AND T49."name" LIKE ? ESCAPE '\' AND T50."name" LIKE ? ESCAPE '\' AND T51."name" LIKE ? ESCAPE '\' AND T52."name" LIKE ? ESCAPE '\' AND T53."name" LIKE ? ESCAPE '\' AND T54."name" LIKE ? ESCAPE '\' AND T55."name" LIKE ? ESCAPE '\' AND T56."name" LIKE ? ESCAPE '\' AND T57."name" LIKE ? ESCAPE '\' AND T58."name" LIKE ? ESCAPE '\' AND T59."name" LIKE ? ESCAPE '\' AND T60."name" LIKE ? ESCAPE '\' AND T61."name" LIKE ? ESCAPE '\' AND T62."name" LIKE ? ESCAPE '\' AND T63."name" LIKE ? ESCAPE '\' AND T64."name" LIKE ? ESCAPE '\' AND T65."name" LIKE ? ESCAPE '\' AND T66."name" LIKE ? ESCAPE '\' AND T67."name" LIKE ? ESCAPE '\' AND T68."name" LIKE ? ESCAPE '\' AND T69."name" LIKE ? ESCAPE '\' AND T70."name" LIKE ? ESCAPE '\' AND T71."name" LIKE ? ESCAPE '\' AND T72."name" LIKE ? ESCAPE '\' AND T73."name" LIKE ? ESCAPE '\' AND T74."name" LIKE ? ESCAPE '\' AND T75."name" LIKE ? ESCAPE '\' AND T76."name" LIKE ? ESCAPE '\' AND T77."name" LIKE ? ESCAPE '\' AND T78."name" LIKE ? ESCAPE '\' AND T79."name" LIKE ? ESCAPE '\' AND T80."name" LIKE ? ESCAPE '\' AND T81."name" LIKE ? ESCAPE '\' AND T82."name" LIKE ? ESCAPE '\' AND T83."name" LIKE ? ESCAPE '\' AND T84."name" LIKE ? ESCAPE '\' AND T85."name" LIKE ? ESCAPE '\' AND T86."name" LIKE ? ESCAPE '\' AND T87."name" LIKE ? ESCAPE '\' AND T88."name" LIKE ? ESCAPE '\' AND T89."name" LIKE ? ESCAPE '\' AND T90."name" LIKE ? ESCAPE '\' AND T91."name" LIKE ? ESCAPE '\' AND T92."name" LIKE ? ESCAPE '\' AND T93."name" LIKE ? ESCAPE '\' AND T94."name" LIKE ? ESCAPE '\' AND T95."name" LIKE ? ESCAPE '\' AND T96."name" LIKE ? ESCAPE '\' AND T97."name" LIKE ? ESCAPE '\' AND T98."name" LIKE ? ESCAPE '\' AND T99."name" LIKE ? ESCAPE '\' AND T100."name" LIKE ? ESCAPE '\' AND T101."name" LIKE ? ESCAPE '\')) subquery ('%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%', '%daniel%')

Obviously, a bit more hilarious when it is the same word over and over again.

Note: See TracTickets for help on using tickets.
Back to Top