JIRA importers from Mantis, Why is jiraJOIN tables jiraissue and customfieldvalue

I have imported issues from Mantis using the Importers plugin. Now I am trying to do some cleanup using SQL on the issue database. The issue I am currently working on is, that the Mantis categories have been imported Components and all Issues were imported as Bugs. I now want to write a little update script that updates the issue types based on components and for some individual issues based on the external issue IDs. I am starting by joining the 'jiraissue' and 'customfieldvalue' on ID where the External issue ID is the External issue ID. This is my query:

SELECT j.ID AS JiraID, f.ID AS JiraID2, CONVERT(int, f.STRINGVALUE) AS MantisID
FROM jiraissue j RIGHT OUTER JOIN customfieldvalue f ON j.ID = f.ID
WHERE 
  CUSTOMFIELD = 10317

However, it turns out that there are entries in 'jiraissue' only for very few entries in the customfieldvalue table. Am I joining with the wrong table?

4 answers

1 accepted

Accepted Answer
0 votes

I think you're joining the correct table. I'm no DBA, but to do "get custom field value from an issue", the most primitive statement is

select * from customfieldvalue where issue = <issue id from jiraissue table> and customfield = <customfield id from customfield table>

There should be no customfieldvalue entries without a matching jiraissue record - the whole point of the customfieldvalue table is that it holds data for the issues, so if the issues are missing, the implication is broken data! (Of course, it's fine to have jiraissue records with no customfieldvalues).

Oh, I found my error, I just joined with the wrong ID, should have been customfieldvalue.ISSUE, stupid me.

Can you please share the exact query you used? I am looking for a similar solution.

This is an example update statement:

/** Issues that ought be Improvements **/
UPDATE [jira_tnag_prod].[dbo].[jiraissue]
SET issuetype = 4 -- improvement
WHERE ID IN ( 
  SELECT j.ID AS JiraID
  FROM jiraissue j RIGHT OUTER JOIN customfieldvalue f ON j.ID = f.ISSUE
  WHERE
    CUSTOMFIELD = 10317 AND                  -- custom field External issue ID  
    CONVERT(int, f.STRINGVALUE) IN ( 1, 2, 3 ) );

However, the actual migration took many many more update statements for correcting various issues that were not imported as intended. This included:

* translating resolutions

* translating components to issue types (this example)

* translating the values of one of our mantis attributes to componentes

* mapping severities to priorities

* setting values for some newly introduced attributes for tracking system test / review state

* translateing free text versions to reified versions in JIRA

...

Thanks for the pointer. I haven't got it to work for me yet but it definitely gives me a starting point.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published 7 hours ago in Sourcetree

Tip from the team: configure your repos for hosting goodness!

Supported Platforms macOS Windows We recently introduced support for additional hosting services such as GitHub Enterprise, GitLab (Cloud, Community Edition, Enterprise Edition), and...

51 views 0 1
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you