Snapshot of a Bugzilla database

Bugzilla is not my preferred choice when choosing a bug/issue/incident tracking system : I found Jira (way) more powerful and flexible, even a the price of an increased complexity and learning curve (for the administrator).

Some times ago I worked on a bugzilla installation used mainly to track software development for a large, enterprise-level ERP.

One requirement was to display the status of the database at a particular time in the past (for reporting purposes), but Bugzilla doesn’t have such tool (it has other nice reports btw).

Exploring the (MySQL) database I found that:

• current bugs status is stored in the “bugs” table
• the table “bugs_activity” stores the changes (who, when and what) to bugs

It is therefore possible to find the value for a bug at a particular time by looking at the bugs activity table for that field either in the “added” or in the “removed” columns, or in the “bugs” table, if no activity is found for that field (ie. the value of the field is the same as its creation).

For the “bug_status” field, this gives (for jan, 01 2014 and bug # 99) :

SELECT Ifnull(
       (
                SELECT   added
                FROM     bugs_activity bas
                WHERE    bas.bug_id = 99
                AND      bas.bug_when <= '2014-01-01'
                AND      fieldid = 9
                ORDER BY bug_when DESC
                LIMIT    1),
                (Ifnull(
                (
                        SELECT   removed
                        FROM     bugs_activity bass
                        WHERE    bass.bug_id = 99
                        AND      bass.bug_when >= '2014-01-01'
                        AND      fieldid = 9
                        ORDER BY bug_when DESC
                        LIMIT    1),
                        bug_status
                 ))
        )
)
AS
  bug_status

Repeat as necessary for every desired field.

If you are scripting, you may want to use a user-defined variable for target date

SELECT @mydate := '2014-12-17 18:00:00';

If you are looking for an array of values (i needed multiple milestones) you may find the MySQL “find_in_set” function useful:

SELECT @mymilestone := 'MyProject v1,MyProject v2';

SELECT
...
WHERE FIND_IN_SET(bugs.target_milestone, @mymilestone)

Wrap up everything:

And you’re ready to go.

Thank you for reading!