Bugzilla:SQLCookBook

Bugzilla mysql queries

This is a set of mysql queries to get data out of the bugzilla database. This is really here for sql weenies, and probably not too useful to anyone else.

Please feel free to edit these for current practice. Here are more example queries: http://www.bugzilla.org/docs/queries.html

If you want to learn to write your own SQL query for a Bugzilla search, you can start by doing a search in Bugzilla, and then add &debug=1 to the URL of the query result page. Then it will output a bunch of stuff, including the SQL statement that it uses to search for the bugs. You can take that and edit it to suit your needs. --Newacct 19:58, 16 July 2010 (UTC)

List all the classifications

select id, name from classifications;

List the product_id's of all the products in a particular classification

assumes you know the classification_id

select products.id from products where products.classification_id = 5;

List of all products by classification

select p.id, p.name, c.id, c.name from classifications c, products p
where p.classification_id = c.id order by c.name, p.name;

Get a list of all milestones which belong to a particular classifications

select * from milestones
where product_id = any (select products.id from products where products.classification_id = 5);

The difference between the next two code bits is that the last join, against bugs, is a left join instead a regular join.

Get a list of all bugs with their named classification, product and component

select cmp.name, p.name, cl.name, bugs.bug_id
from classifications cl left join products p on p.classification_id = cl.id
                       left join components cmp on cmp.product_id = p.id
                            join bugs on bugs.component_id = cmp.id
order by bugs.bug_id

Get a list of all bugs, with named classification, product and components, including categories with nothing in them.

select cmp.name, p.name, cl.name, bugs.bug_id
from classifications cl left join products p on p.classification_id = cl.id
                       left join components cmp on cmp.product_id = p.id
                       left join bugs on bugs.component_id = cmp.id
order by bugs.bug_id

Get a list of the bug opening times from bugs, and combine it with the bugs_activity table to make a table which actually has all the transitions in it.

Note that I actually limit to the changes in field 9, which is the bug status.

select bug_id, creation_ts, 'OPENED' from bugs
union all select bug_id, bug_when, added from bugs_activity
where fieldid = 9 order by bug_id;

Insert a set of milestones into all the products of a classification, multiple times:

set @num := 0;
set @sk := 0;
set @num := @num + 1;
set @sk := @sk + 100;
select @num, @sk;
insert into milestones(product_id, value,sortkey) select id, concat("Sprint ",@num), @sk from products where classification_id = 5;
select product_id, value, sortkey from milestones, products where milestones.product_id = products.id and products.classification_id = 5;

Get a table that is all of bugs_activity, corrected to include bug opens

select bug_id, bug_when, added as state from bugs_activity
 where fieldid = 9
union all
 select bug_id, creation_ts, 'OPENED'
 from bugs order by bug_id;

"Edit" a comment, replacing it with a new version

update longdescs set thetext='(In reply to comment #2)\n\> Technical details such as this should pro
bably be included in a separate, more\n\> specific CVS training curriculum rather than a general (S)
CM class.\n\> Wasn't someone in the Software Competency going to put together a CVS class?\n\nSee bu
g 1555 (my request for a CVS training class)\n'   where comment_id=4520;

Add everyone to a particular group

select id from groups where name = "MyProduct";
+----+
| id |
+----+
| 45 |
+----+
1 row in set (0.00 sec)

We then get all the rows from profiles, add on the groups.id we just got, one '0' to say that this is just a regular user, not one who can grant this power to others, and that their inclusion is explicit. The insert ignore says "Yeah, I know we are trying to insert duplicate rows, but don't stop going, you're allowed to fail to insert the row, IGNORE that error.

insert ignore into user_group_map select userid, 45, 0, 0 from profiles;

Note that if you wanted to replace any duplicate rows with your version (removing a blessing, for instance), you could add on duplicate key update. See the MySQL manual for more info. Further note that IGNORE and ON DUPLICATE KEY UPDATE are not mutually exclusive. IGNORE really means to turn all errors into warnings.