Keep Bugzilla IDs from import to JIRA

SarahL August 5, 2012

When you import from a Bugzilla database is it possible to "keep" the Bugzilla IDs, so that the corresponding JIRA IDs have the same number?? We are migrating from Bugzilla but have a selfwritten subversion connection which links to the Bugzilla IDs. I want to have legacy support so that the IDs in the subversion comments are still valid in the new Jira system.

2 answers

1 accepted

1 vote
Answer accepted
Pawel Niewiadomski
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 6, 2012

JIRA Importers Plugin currently stores the original bud id as External System Id which is a custom field. So you can always refer back from the legacy system.

It's not currently possible to re-use the original bug id as issue key in JIRA Importers Plugin for couple of reasons. First the issue key pattern has standarized and many external tools assume that the issue key in JIRA has a known format. Sure there's an advanced option in JIRA to change it, but it may break something.

Also it's not possible to change issue key format for only one project, this is a global setting AFAIR.

If you really want to go this way you can modify JIRA Importers Plugin to copy issue keys, then you'd have to reconfigure JIRA and it may work as expected.

But I'm not sure if it really worth it. You decide.

Michael McAnaney August 13, 2013

I'm having the same issue. I wouldn't want to change the format, I just want to translate from one format to another. Is there a way to do that?

1 vote
Leonard Chew
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 22, 2016

We just recently imported Bugzilla Data into our JIRA using the Bugzilla External Import Tool, and we also wanted keep the ID's.

The main reason for us needing the same ID's was to keep the correct links of git-checkins to the bugs through the Jira add-on "git Integration for Jira".

We solved the issue by editing the data direclty on the database using an oracle-sql script.
OK, there might be better ways, but this is how it worked for us...

Prerequisites:

  • The Bugzilla Data needs to be freshly imported intto the Jira-Project (i.e. no additional items were created in Jira after the import)
  • Oracle Database
  • Linux OS for the attachments

 

Below are the scripts. They were used on:

  • Database: Oracle 12c
  • Jira Version: JIRA v7.1.0
  • OS: Linux, Red Hat 4.8.5-4


There are two major parts:

  • Part 1: Convert the ID's and update the descriptions and comments with the correct new ID's.
  • Part 2: Correct the attachment locations on the filesystem by generatating and executing a bash-script.
    On a Windows OS, you may want to generate a cmd- or powershell script.

 

Part 1: Convert the ID's and update the descriptions and comments with the correct new ID's.

Usage:

  • Change the value of the variable 'PROJECTKEY' with the Jira-Project Key of your freshly imported Bugzilla Data.
  • Run the SQL script step by step on your Oracle Database
  • Before commiting, crosscheck the data with bugzilla using the select statements at the end.
  • Commit the changes
  • Reindex your Jira Project: "Jira Administration" -> System -> Indexing

Except for the attachments, everything should now be fine.
Do not drop the table zilla_to_jira_tmp yet, that is still needed for part 2 below.

define PROJECTKEY = 'BUG';  -- Project Key of the Bug-Project
set serveroutput on size 100000;

create table zilla_to_jira_tmp
as 
select to_number(cfv.stringvalue) BugzillaId, 
      ji2.id       IssuePK, 
      ji2.issuenum JiraIssuenum, 
      '&PROJECTKEY-'||ji2.issuenum OldJiraKey, 
      '&PROJECTKEY-'||cfv.stringvalue NewJiraKey
from jiraissue ji2
join project p on ji2.project = p.id
join customfieldvalue cfv on (ji2.id = cfv.issue)
join customfield      cf  on (cfv.customfield = cf.id
                              and cf.cfname = 'External issue ID')
where p.pkey = '&PROJECTKEY';

create unique index zilla_to_jira_tmp_1 on zilla_to_jira_tmp (OldJiraKey);
create unique index zilla_to_jira_tmp_2 on zilla_to_jira_tmp (issuepk);

-- Update the Issue-Keys with the corresponding Bugzilla ID
merge into jiraissue ji
using 
(
  select BugzillaId, IssuePK
  from zilla_to_jira_tmp
  where BugzillaId != JiraIssuenum
) bz
on (ji.id = bz.IssuePK)
when matched then
  update set ji.issuenum = bz.BugzillaId;

-- Update the High-Watermark Project Counter with the correct max-value
update project p
set pcounter = (select max(issuenum) 
                from   jiraissue ji
                join   project p1 on ji.project = p1.id
                where  p1.pkey = '&PROJECTKEY')
where p.pkey = '&PROJECTKEY';

-- Update all Keys in description and comments to the new value (This part is NOT rerunnable)
declare
 v_counter    number := 0;
begin 
 -- update old keys in jiraissue descriptions
 for l_jiraissue in (select *
                     from   jiraissue ji
                     join   zilla_to_jira_tmp bz on (ji.id = bz.IssuePK)
                     where  ji.description like '%&PROJECTKEY-%')
 loop
   for l_update in (select to_char(regexp_substr(l_jiraissue.description,'(&PROJECTKEY-[0-9]+)',1,level)) oldKey
                    from   dual
                    connect by level <= regexp_count(l_jiraissue.description,'(&PROJECTKEY-[0-9]+)'))
   loop
     -- update every bug-number occurrence one at a time:
     update jiraissue
     set    description = replace(description,l_update.oldKey,(select newjirakey from zilla_to_jira_tmp where OldJiraKey=l_update.oldKey))
     where  id = l_jiraissue.id;
     v_counter := v_counter + 1;
   end loop;
 end loop;
 dbms_output.put_line('Updated '||v_counter||' references in bug descriptions');

 -- update old keys in all comments (jiraaction)
 v_counter := 0;
 for l_action in (select ja.id, ja.issueid,ja.actionbody
                  from   jiraaction ja
                  join   zilla_to_jira_tmp bz on (ja.issueid = bz.IssuePK)
                  where  actiontype = 'comment'
                  and    actionbody like '%&PROJECTKEY-%')
 loop
   for l_update in (select to_char(regexp_substr(l_action.actionbody,'(&PROJECTKEY-[0-9]+)',1,level)) oldKey
                    from   dual
                    connect by level <= regexp_count(l_action.actionbody,'(&PROJECTKEY-[0-9]+)'))
   loop
     -- update every bug-number occurrence one at a time:
     update jiraaction
     set    actionbody = replace(actionbody,l_update.oldKey,(select newjirakey from zilla_to_jira_tmp where OldJiraKey=l_update.oldKey))
     where  id = l_action.id;
     v_counter := v_counter + 1;
   end loop;
 end loop;
 dbms_output.put_line('Updated '||v_counter||' references in bug comments');
end;
/

-- Crosscheck the data with Bugzilla. If OK, commit
-- check descriptions and new Jira-Key:
select * from (
select bz.BugzillaId,'&PROJECTKEY-'||ji.issuenum NewJiraKey, ji.description NewDescription
from   jiraissue ji
join   zilla_to_jira_tmp bz on (ji.id = bz.IssuePK)
where  ji.description like '%&PROJECTKEY-%'
order by IssuePK desc)
where rownum < 5;

-- check content of comments:
select * from (
select bz.BugzillaId, bz.NewJiraKey, ja.actionbody commnt 
from   jiraaction ja
join   zilla_to_jira_tmp bz on (ja.issueid = bz.IssuePK)
where  actiontype = 'comment'
and    actionbody like '%&PROJECTKEY-%'
order by IssuePK desc)
where rownum < 5;

-- commit; -- commit if data is correct

 

 

Part 2: Correct the attachment locations on the filesystem by generatating and executing a bash-script.

I dont know if the attachments in jira are configurable, but ours are located in groups of 10000, e.g. bugs BUG-10001 to BUG-20000 are in folder ./20000 and so forth.

This will only work, if the behavior is the same.

 

Usage:

  • Check the discspace and backup your attachment-folder:
    $ cd /var/atlassian/application-data/jira/data/attachments #(or wherever it is)
    $ du -k BUG -d 0 # where BUG is the projectkey
    $ df -k
    $ cp -rp BUG BUG_BACKUP
  • Edit the Project Key on the sql script below
  • Run the select statement on your Oracle Database
  • Paste/Export the content of the select statement into a file, e.g. move_attachmentFolders.bash
  • Copy the file into the bug-project of the attachments folder:
    e.g. to /var/atlassian/application-data/jira/data/attachments/BUG/move_attachmentFolders.bash
  • Go to the directory and execute the script
    $ cd /var/atlassian/application-data/jira/data/attachments/BUG
    $ chmod 750 ./move_attachmentFolders.bash
    $ ./move_attachmentFolders.bash

define PROJECTKEY = 'BUG';  -- Project Key of the Bug-Project

select shell_command from (
select '#!/bin/bash' shell_command, 1 rnk from dual 
union
select 'if [ ! $(pwd | grep "data/attachments/&PROJECTKEY") ]; then echo "ERROR: You need to execute the script in the jira-attachments directory of Project &PROJECTKEY"; exit 1; fi' shell_command, 2 rnk from dual
union
select 'for f in $(find . -type d | grep "&PROJECTKEY-[0-9][0-9]*$"); do mv $f ${f}_moved; done' shell_command, 3 rnk from dual 
union
select 'if [ ! -d "./'|| dir|| '" ]; then mkdir ./' || dir ||'; fi' shell_command, 4+(dir/1000000) rnk
from 
( select distinct ceil(bugzillaid/10000)*10000 dir
  from zilla_to_jira_tmp)
union
select 'if [ -d ./' || ceil(jiraissuenum/10000)*10000||'/'||OLDJIRAKEY || '_moved ]; then ' ||'mv ./'|| ceil(jiraissuenum/10000)*10000||'/'||OLDJIRAKEY || '_moved ./' || ceil(bugzillaid/10000)*10000||'/'||NEWJIRAKEY || '; fi' shell_command, 5+jiraissuenum rnk
from FILEATTACHMENT fa
join ZILLA_TO_JIRA_TMP bz on (fa.issueid = bz.issuepk)
where oldjirakey != newjirakey
)
order by rnk;

Suggest an answer

Log in or Sign up to answer