Query Cookbook

As a bugzilla administrator, sometimes the queries I need to do are too specialized for the standard bugzilla query page and I have to do them in SQL. I want to record some of these queries here for later reference to save me from re-inventing the wheel. Hopefully others will find this useful as well.

  • Search for bug owners who seem to have not used bugzilla in a while. Send these people mail and disable the accounts of those that bounce. Use the date that they last commented on a bug to decide whether they're active. This isn't perfect, but its good enough since most bug operations require a comment of some kind and in any case, we're not going to do anything to them unless their mail bounces.
    SELECT MAX(bug_when) AS last_update,
           profiles.login_name AS email
    FROM longdescs AS ld,
         bugs,
         profiles
    WHERE ld.who = bugs.assigned_to
      AND ld.who = profiles.userid
    GROUP BY email
    HAVING last_update < DATE_SUB(NOW(),INTERVAL 1 YEAR)
    ;
    

    The above query probably would have done the job but our db is too huge to take the direct approach. I made a copy of the bugs db and deleted resolved bugs and 'old' comments. From this I created a list of owners of open bugs and a list of owners who have commented recently. Subtracting one list from the other creates a list of people who haven't.

    1. Create a dump of the original database.
      % mysqldump bugs > ~/bugs.dump
      
    2. Create a temporary database to hold your trimmed version of the db.
      % mysql
      mysql> CREATE DATABASE tmp;
      
    3. Import the bug database.
      % mysql tmp < bugs.dump
      
    4. Delete old long descriptions.
      DELETE FROM longdescs WHERE bug_when < DATE_SUB(NOW(),INTERVAL 1 YEAR);
      
    5. Remove bugs that aren't open.
      DELETE FROM bugs WHERE bug_status IN('RESOLVED', 'VERIFIED', 'CLOSED');
      
    6. The only bugs left are open ones. The only comments left are recent ones. For each bug, print the owner if you can find a comment for them. This is a list of bug owners with recent comments.
      SELECT p.login_name
      FROM profiles p
           INNER JOIN bugs ON p.userid = bugs.assigned_to
           INNER JOIN longdescs ON bugs.assigned_to = longdescs.who
      GROUP BY p.login_name
      INTO OUTFILE "/tmp/aliveowners"
      ;
      
    7. Make a list of all bug owners.
      SELECT p.login_name
      FROM profiles p
           INNER JOIN bugs ON p.userid = bugs.assigned_to
      GROUP BY p.login_name
      INTO OUTFILE "/tmp/allowners"
      ;
      
    8. Subtract the list of alive owners from the list of all owners to get a list of owners of open bugs who haven't commented on a bug recently.
      % diff aliveowners allowners > checkonthese
      
  • Bugs assigned to people with disabled accounts.
    SELECT bugs.bug_id,
           p.login_name,
           comp.name,
           bugs.short_desc
    FROM bugs
         INNER JOIN profiles p ON bugs.assigned_to = p.userid
         INNER JOIN components comp ON bugs.component_id = comp.id
    WHERE bugs.bug_status IN('UNCONFIRMED', 'NEW', 'CONFIRMED', 'ASSIGNED', 'IN_PROGRESS', 'REOPENED')
      AND p.disabledtext != ""
    ;
    
  • Bugs whose QA contact is disabled.
    SELECT bugs.bug_id,
           p.login_name,
           comp.name,
           bugs.short_desc
    FROM bugs
         INNER JOIN profiles p ON bugs.qa_contact = p.userid
         INNER JOIN components comp ON bugs.component_id = comp.id
    WHERE bugs.bug_status IN('UNCONFIRMED', 'NEW', 'CONFIRMED', 'ASSIGNED', 'IN_PROGRESS', 'REOPENED')
      AND p.disabledtext != ""
    ;
    
  • Count how many times a bug has been marked FIXED.
    SELECT bug_id,
           COUNT(bug_id) AS my_sum
    FROM bugs_activity
    WHERE added = 'FIXED'
    GROUP BY bug_id
    ORDER BY my_sum
    ;
    
  • See who triages the most newly filed bugs by counting the number of times each person has changed bugs from the UNCONFIRMED state in the bug activity table. This includes confirming bugs and closing bugs without confirming them first (e.g. marking as duplicate).
    SELECT COUNT(ba.bug_id) AS confirmsum,
           p.login_name
    FROM profiles p,
         INNER JOIN bugs_activity ba ON p.userid = ba.who
         INNER JOIN fielddefs fd ON ba.fieldid = fd.fieldid
    WHERE fd.name = 'bug_status'
      AND ba.removed = 'UNCONFIRMED'
    GROUP BY p.login_name
    ORDER BY confirmsum
    ;
    
  • Count how many bugs each person has confirmed. List the top 20 people in descending order.
    SELECT profiles.login_name AS email,
           COUNT(bug_id) AS bugcount
    FROM bugs_activity ba,
         INNER JOIN profiles ON ba.who = profiles.userid
         INNER JOIN fielddefs fd ON ba.fieldid = fd.fieldid
    WHERE fd.name = 'everconfirmed'
      AND ba.added = 1
    GROUP BY email
    ORDER BY bugcount DESC
    LIMIT 20
    ;
    
  • Count how many unconfirmed bugs people closed as INVALID, DUPLICATE or WORKSFORME. Sort in descending order and list only the top ten.
    SELECT profiles.login_name AS email,
           COUNT(ba.bug_id) AS bugcount
    FROM bugs_activity ba
         INNER JOIN profiles ON ba.who = profiles.userid
         INNER JOIN bugs ON ba.bug_id = bugs.bug_id
         INNER JOIN fielddefs fd ON ba.fieldid = fd.fieldid
    WHERE fd.name = 'resolution'
      AND ba.added IN('INVALID', 'DUPLICATE', 'WORKSFORME')
      AND bugs.everconfirmed = 0
    GROUP BY email
    ORDER BY bugcount DESC
    LIMIT 10
    ;
    
  • Count how many bugs each person has verified as INVALID.
    SELECT profiles.login_name AS email,
           COUNT(bugs.bug_id) AS bugcount
    FROM profiles
         INNER JOIN bugs_activity ba ON profiles.userid = ba.who
         INNER JOIN bugs ON ba.bug_id = bugs.bug_id
         INNER JOIN fielddefs fd ON ba.fieldid = fd.fieldid
    WHERE fd.name = 'bug_status'
      AND ba.added = 'VERIFIED'
      AND bugs.resolution = 'INVALID'
    GROUP BY email
    ORDER BY bugcount
    ;
    
  • Count how many bugs each person has added attachments to.
    SELECT p.login_name AS email,
           COUNT(a.bug_id) AS bugcount
    FROM profiles p,
         INNER JOIN attachments a ON p.userid = a.submitter_id
    GROUP BY email
    ORDER BY bugcount
    ;
    
  • List the top 100 bug reporters.
    SELECT profiles.login_name AS email,
           COUNT(bugs.bug_id) AS bugcount
    FROM bugs
         INNER JOIN profiles ON bugs.reporter = profiles.userid
    GROUP BY email
    ORDER BY bugcount DESC
    LIMIT 100
    ;
    
  • List people who have reported more than 500 bugs.
    SELECT profiles.login_name AS email,
           COUNT(bugs.bug_id) AS bugcount
    FROM bugs
         INNER JOIN profiles ON bugs.reporter = profiles.userid
    GROUP BY email
    HAVING bugcount > 500
    ORDER BY bugcount
    ;
    
  • List the top ten largest attachments.
    SELECT p.login_name,
           attach_id,
           bug_id,
           LENGTH(thedata) datalength,
           ispatch,
           description
    FROM attachments a
         INNER JOIN profiles p ON a.submitter_id = p.userid
    ORDER BY datalength DESC
    LIMIT 10
    ;
    
  • List all of the people who have granted or denied a review in the Bugzilla product in the last 6 months.
    SELECT profiles.login_name,
           MAX(act.bug_when) AS last_review,
           COUNT(act.added) AS review_count
    FROM bugs_activity AS act
         INNER JOIN profiles ON act.who = profiles.userid
         INNER JOIN bugs ON act.bug_id = bugs.bug_id
         INNER JOIN products ON bugs.product_id = products.id
    WHERE DATE_SUB(NOW(),INTERVAL 6 MONTH) < act.bug_when
      AND (act.added = 'review+' OR act.added = 'review-')
      AND products.name = 'Bugzilla'
    GROUP BY profiles.login_name
    ORDER BY review_count DESC
    ;