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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.