Opened 4 years ago

Closed 3 years ago

#11096 closed defect (fixed)

Avoid an `OperationalError: too many SQL variables`

Reported by: hasienda Owned by: hasienda
Priority: normal Component: TagsPlugin
Severity: normal Keywords:
Cc: rjollos, jun66j5 Trac Release:


The two-stepped approach in tractags.model.tagged_resources doesn't work at all for large quantities of tagged resources and must be replaced to avoid an OperationalError: too many SQL variables.

This surfaced while changing ticket tags processing for #4503. I was hit well over 10.000 tickets. Many Trac projects might not reach that number (too soon), even fewer will reach that count for other resources like wiki pages.

And issues mentioned in #4503 might actually have prevented this issue from getting known as well, because in these Trac applications TagsPlugin would have been dropped long before reaching the critical mark (resource count per tag realm). I've seen ticket view load times beyond 15 s/page with KeywordSuggestPlugin using TagsPlugin for not more than 5.000 tickets, that dropped to about 4 seconds when deactivating get_tagged_resources (done per request) by TicketTagProvider.

Still I consider it a more or less accidental find, because I've been trying to improve code, not to fix it.

Attachments (0)

Change History (3)

comment:1 Changed 4 years ago by hasienda

(In [13165]) TagsPlugin: Mirror ticket tags into normalized tags db table, refs #4503, #9194 and #11096.

Parsing ticket fields per request has been a known performance hog, that's why using the tags db table for regular access yields a considerable speed-up.

As a side-effect 'ticket' is the pilot for fixing a recently announced issue with get_tagged_resources in the default tag provider implementation. Nicely polished by stretching unit test coverage to this part of the code.

ToDo: DefaultTagProvider must be fixed as well, and other tag providers should be reviewed too as a precaution.

comment:2 Changed 3 years ago by rjollos

With [trac] debug_sql = enabled, the SQL in tractags.ticket is logged twice:

08:23:23 PM Trac[ticket] DEBUG: 
            SELECT *
              FROM (SELECT id, keywords, COALESCE(keywords, '') AS std_fields
                      FROM ticket AS tkt
                     WHERE NOT EXISTS (SELECT * FROM tags
                                       WHERE tagspace=%s AND name=CAST( AS text))
                      AND status != 'closed') AS s
             WHERE std_fields != ''
             ORDER BY id
08:23:23 PM Trac[util] DEBUG: SQL: 
            DELETE FROM tags
             WHERE tagspace=%s
               AND NOT EXISTS (SELECT * FROM ticket AS tkt
                               WHERE AS integer) AND status != 'closed')
08:23:23 PM Trac[util] DEBUG: args: ('ticket',)
08:23:23 PM Trac[util] DEBUG: prefetch: 0 rows
08:23:23 PM Trac[ticket] DEBUG: ENTER_TAG_DB_CHECKOUT
08:23:23 PM Trac[util] DEBUG: SQL: 
            SELECT *
              FROM (SELECT id, keywords, COALESCE(keywords, '') AS std_fields
                      FROM ticket AS tkt
                     WHERE NOT EXISTS (SELECT * FROM tags
                                       WHERE tagspace=%s AND name=CAST( AS text))
                      AND status != 'closed') AS s
             WHERE std_fields != ''
             ORDER BY id

I'm thinking we could remove this line:

  • tagsplugin/trunk/tractags/

    diff --git a/tagsplugin/trunk/tractags/ b/tagsplugin/trunk/tractags/tic
    index e7afda4..bf46ce7 100644
    a b class TicketTagProvider(DefaultTagProvider): 
    220220             ORDER BY id
    221221            """ % (','.join(self.fields), db.concat(*fields),
    222222                   db.cast('', 'text'), ignore)
    223         self.env.log.debug(sql)
    224223        # Obtain cursors for reading tickets and altering tags db table.
    225224        # DEVEL: Use appropriate cursor typs from Trac 1.0 db API.
    226225        ro_cursor = db.cursor()
Last edited 3 years ago by rjollos (previous) (diff)

comment:3 Changed 3 years ago by hasienda

  • Resolution set to fixed
  • Status changed from new to closed

In 13815:

TagsPlugin: Completing preparation for v0.7 release.

Availability of that code as stable, tagged release
closes #2429, #3359, #3610, #3624, #3677, #3754, #3864, #3947, #3983, #4078, #4277, #4503, #4799, #5523, #7787, #7857, #8638, #9057, #9058, #9059, #9060, #9061, #9062, #9063, #9149, #9210, #9521, #9630, #9636, #10032, #10416, #10636, #11096, #11147, #11152, #11274, #11302, #11658 and #11659.

Additionally there are some issues and enhancement requests showing progress,
but known to require more work to resolve them satisfactorily, specifically
refs #2804, #4200, #8747 and #9064.

Thanks to all contributors and followers, that enabled and encouraged a good
portion of this development work.

Add Comment

Modify Ticket

as closed The owner will remain hasienda.
The resolution will be deleted. Next status will be 'reopened'.

E-mail address and user name can be saved in the Preferences.

Note: See TracTickets for help on using tickets.