I am currently experimenting with importing issues into a test instance of a Jira database using SQL.
This is my current approach:
1. Stop Jira.
2. Take a backup of the database.
3. Run the SQL.
4. Restart Jira.
5. Re-index Jira.
My issue is as follows:
I can't see the imported issues in any of the issue search or project summary screens. So for example if I list all the open issues in the project, I can't see my imported issues.
I'm confident that for the tables I've touched so far I have done so in a correct manner, because when I manually append the imported issue project keys into the URL I can then access them within Jira without any errors. If I then update the issue from within Jira, for example by adding a comment, only then does it appear in the issue search and project summary screens.
I have run a profiler when creating an issue within Jira in order to capture the SQL executed. I can see that these are the tables inserted to:
INSERT INTO JiraTestSchema.OS_WFENTRY (ID, NAME, INITIALIZED, STATE) VALUES ( @P0 , @P1 , @P2 , @P3 ) INSERT INTO JiraTestSchema.OS_CURRENTSTEP (ID, ENTRY_ID, STEP_ID, ACTION_ID, OWNER, START_DATE, DUE_DATE, FINISH_DATE, STATUS, CALLER) VALUES ( @P0 , @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 , @P9 ) INSERT INTO JiraTestSchema.jiraissue (ID, pkey, PROJECT, REPORTER, ASSIGNEE, issuetype, SUMMARY, DESCRIPTION, ENVIRONMENT, PRIORITY, RESOLUTION, issuestatus, CREATED, UPDATED, DUEDATE, RESOLUTIONDATE, VOTES, WATCHES, TIMEORIGINALESTIMATE, TIMEESTIMATE, TIMESPENT, WORKFLOW_ID, SECURITY, FIXFOR, COMPONENT) VALUES ( @P0 , @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 , @P9 , @P10 , @P11 , @P12 , @P13 , @P14 , @P15 , @P16 , @P17 , @P18 , @P19 , @P20 , @P21 , @P22 , @P23 , @P24 ) INSERT INTO JiraTestSchema.customfieldvalue (ID, ISSUE, CUSTOMFIELD, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE) VALUES ( @P0 , @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 ) INSERT INTO JiraTestSchema.customfieldvalue (ID, ISSUE, CUSTOMFIELD, PARENTKEY, STRINGVALUE, NUMBERVALUE, TEXTVALUE, DATEVALUE, VALUETYPE) VALUES ( @P0 , @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 ) INSERT INTO JiraTestSchema.userassociation (SOURCE_NAME, SINK_NODE_ID, SINK_NODE_ENTITY, ASSOCIATION_TYPE, SEQUENCE, CREATED) VALUES ( @P0 , @P1 , @P2 , @P3 , @P4 , @P5 ) INSERT INTO JiraTestSchema.userhistoryitem (ID, entitytype, entityid, USERNAME, lastviewed, data) VALUES ( @P0 , @P1 , @P2 , @P3 , @P4 , @P5 )
I have mimicked the above logic. I'm also updating the project counter and each of the necessary entries in the SEQUENCE_VALUE_ITEM table.
What am I missing here, why does my imported issue not appear within the issue search and project summary screens?
I know it's recommended not to use SQL to import issues directly to the Jira database but it is a test instance I am experimenting with so there's no risk involved. I'm determined to get to the bottom of this issue because I don't want to waste the effort I've put in so far as it is nearly fully functional. It's also been a good experience as it allows me to familiarise myself with the inner workings of the Jira database schema.
I managed to get this working. For the sake of future posterity, here is the solution. The problem was that I had not cleared the internal Jira caches:
1. Stop Jira.
2. Cache folder can be found here:
For me this was C:\Program Files\Application Data\JIRA\caches\indexes
3. Delete the following sub folders:
4. Start Jira.
5. Re-index Jira.
The cache folders should be recreated now and the imported issues will show up within the Jira UI.
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!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG