Opened 9 months ago

Last modified 9 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: http://cms.example.com/posts/posts/?q=Deliverable++TRC+Promo+%282+Options%29+Visual+Snapshot++%28Montage%29+How+do+you+know+your+reward+Strategy+is+working%3F+Masterclass%3A+Consumerization+of+Total+Rewards%3A+Superhero%27s+Toolkit+Alignment%2C+Management+and+Governance+of+%E2%80%98Executive+Pay%E2%80%99+and+Long+Term+Incentives+Award+Ceremony%3A+League+Awards+-Total+Rewards+2017+%22Masterclass%3AMotivating+your+Internal+Customers++Part+II%3A+A+Communication+Toolkit+by+Preeti+Gupta%2C+Aditya+Birla+Group

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:

# models.py
@python_2_unicode_compatible
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'))

# admin.py
@admin.register(Posts)
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 (7)

comment:1 Changed 9 months ago by VINAY KUMAR SHARMA

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

comment:2 Changed 9 months ago by Jani Tiainen

Could you create minimal testcase that causes the error?

comment:3 Changed 9 months 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 9 months 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 9 months 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 9 months 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 9 months ago by Anton Samarchyan

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top