It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

0 votes
Answer accepted

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
TAGS
Community showcase
Published in Next-gen

Keyboard shortcuts have arrived for next-gen projects!

...ollected feedback from users around the lack of shortcuts, and we’re here to address that: In next-gen projects, I miss the keyboard shortcuts badly. This is particularly true on the Board, but also i...

279 views 2 5
Read article

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you