Explicit Explicitly Open Living

Migration to Bugzilla 3.2.x from custom defect tracking solution

Since I use Google and blog and forum posts frequently to help solve problems, I felt it only appropriate to contribute back to the interwebs some of the solutions I have developed to solve problems.  So, here are the details for migrating from a custom defect tracking system in MS Access to Bugzilla 3.2.x.  I will be installing this in a Windows environment, but I like to work in the simpler world of Linux and then just copy over the data to Windows (If you want to use Windows as your sandbox, set it up by following https://wiki.mozilla.org/Bugzilla:Win32Install).

Setup your sandbox
I first created a sandbox by downloading a Fedora 10 virtual appliance (http://www.thoughtpolice.co.uk/vmware/#fedora10).  I then setup the Bugzilla environment with a simple command (see https://wiki.mozilla.org/Bugzilla:Linux_Distro_Installation).

yum install bugzilla httpd mysql-server phpmyadmin perl-CPAN

Now, if you want to get all of the basic add-ons, grab them by first running: /usr/share/bugzilla/checksetup.pl

Follow the instructions there to make sure you have all of the addons configured and database setup properly (e.g. /usr/bin/perl install-module.pl --all).

Start your servers: /etc/init.d/httpd start; /etc/init.d/mysqld start

Congrats.  Go to your server and make sure things are looking good (e.g. http://localhost).

Configuration Settings

Go ahead and configure Bugzilla parameters.  You will need to enable aliases and bug moving (for importing).  The aliases are only going to be used since I want to know the old bug # from the previous system.

Since that doesn't install the latest version of Bugzilla, download the latest and overwrite the install /usr/share/bugzilla (latest stable release: http://www.bugzilla.org/download/#stable).  After downloading, run a command like:
tar xzvf bugzilla-3.2.2.tar.gz
/bin/cp -arf bugzilla-3.2.2./* /usr/share/bugzilla/


I'm going to wait on the actual upgrade of the DB until after the migration since the new version of Bugzilla has additional security features and I'm not sure if I'll end up breaking some references between tables based on my import.  But, I wanted to have the latest version of importxml.pl since that has some updates for importing bugs.

Export the data to XML following the Bugzilla DTD

I exported from MS Access using the attached code.  Remember, you will need to install the MS DAO into your references (Tools >> References) when in the module editor.  Of course, you'll have to modify your queries to produce the proper data for your custom system, but this should get well on your way.

Bugzilla XML export from MS Access utilizing VBScript: bugzilla-xml-export-from-ms-access-utilizing-vbscript.

Getting the data imported

Now, here is the tedious part.  You need to first have a bunch of data setup within Bugzilla so that the migration can go smoothly.  Otherwise, you'll end up with a bunch of bugs in there that have no useful project, version, status, priority, severity, owner, etc.  You need to get all of this data and setup Bugzilla.  I recommend that you run some SELECT DISTINCT statements on your main data output query from MS Access to determine the other data you need to setup prior to import.

From those SELECT DISNTINCT statements, you can fill out this spreadsheet that I put together to manage the queries required to get the rest of the data setup.

Bugzilla setup SQL statements: Bugzilla Migration SQL.xls

Run the SQL statements using phpMyAdmin (http://localhost/phpMyAdmin).

Since, I don't like Bugzilla's severities, overwrite their severity table with the values I like (Bugzilla SQL status table overwrite).

You'll need to define a default owner, product, component, etc.  Be sure do that the in administration parameters.

Before attempting to import into Bugzilla, I suggest that you take a DB snapshot so that when things don't go well, you can easily get back to this initial state, add the missing data, and re-try the import.  Go to phpMyAdmin and do an 'Export."  Make sure that you 'disable foreign key checks' and include the 'drop table' statements.

Now attempt to import the XML file by using a command similar to the one below:

/usr/share/bugzilla/importxml.pl -v ~/bugzilla_migration.xml > ~/bugzilla_migration.log

Now check the ~/ bugzilla_migration.log for errors.  Look out for times when it says that it can't locate the product, version, component, QAContact, status, priority, severity, etc.  Ignore errors with #PCDATA in them.

If you have errors to deal with, work through them by first restoring the bugs DB in phpMyAdmin.  Then add the proper lines into the Bugzilla excel spreadsheet and use the commands to add the missing values Bugzilla needed.  Re-import the file using the same commend.  Repeat this process each time until you only see #PCDATA errors in the file.

Congrats, you now have the data in Bugzilla.  Now, we'll need to do some cleanup.

update longdescs set thetext = replace(thetext ,'at http://10.31.217.134/bugzilla/show_bug.cgi?','in Legacy System with ');
update bugs_fulltext set comments = replace(comments,'at http://10.31.217.134/bugzilla/show_bug.cgi?','in Legacy System with ');
update bugs_fulltext set comments_noprivate = replace(comments_noprivate,'at http://10.31.217.134/bugzilla/show_bug.cgi?','in Legacy System with ');
update longdescs set thetext = replace(thetext ,'Unknown bug field "#PCDATA" encountered while moving bug\n   <#PCDATA></#PCDATA>\n','');
update bugs_fulltext set comments = replace(comments,'Unknown bug field "#PCDATA" encountered while moving bug\n   <#PCDATA></#PCDATA>\n','');
update bugs_fulltext set comments_noprivate = replace(comments_noprivate,'Unknown bug field "#PCDATA" encountered while moving bug\n   <#PCDATA></#PCDATA>\n','');
update longdescs as ut set thetext = replace(thetext ,'This bug was previously known as _bug_', CONCAT('This bug was known as bug ',IFNULL((select alias from bugs where bug_id = ut.bug_id),''), '.\nThis bug was #'));
update bugs_fulltext as ut set comments = replace(comments, 'This bug was previously known as _bug_', CONCAT('This bug was known as bug ',IFNULL((select alias from bugs where bug_id = ut.bug_id),''), '.\nThis bug was #'));
update bugs_fulltext  as ut set comments_noprivate = replace(comments_noprivate, 'This bug was previously known as _bug_', CONCAT('This bug was known as bug ',IFNULL((select alias from bugs where bug_id = ut.bug_id),''), '.\nThis bug was #'));

Final Bugzilla Setup

We need to do the final DB upgrade to Bugzilla 3.2.2.  So, run the following command: /usr/share/bugzilla/checksetup.pl

Now, log into Bugzilla and make sure that everything is looking good.  You probably want to run the Sanity Check and finish setting up all of the parameters.  Finally, you probably want to disable bug aliases and bug moving (unless you need them for another reason)

Custom fields in Bugzilla

There is one final thing that is very useful for my project.  It is custom multiple select fields.  I'm not sure why the Bugzilla team hasn't incorporated these fields into the search filter, so we'll have to tweak some of the Bugzilla code.

I've based the two attached files on this posting: http://www.nabble.com/Adding-Custom-Field-drop-downs-to-Advanced-Search-Page-td16304546.html#a19165732.

Files: Bugzilla modified query.cgi, Bugzilla modified formhtml.tmpl

If you are using Bugzilla 3.2.2, you can probably just overwrite the files with what I have here.  Please see the above for where to put the files.

After doing this, you'll need to re-compile your templates by executing checksetup.pl again.  Now, you'll be able to report and search by your custom fields multiple select fields.

/usr/share/bugzilla/checksetup.Bugzilla SQL status table overwritepl

Congrats, I hope this was helpful.  Now you'll need to finish customizing Bugzilla.