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
We're bringing product updates and pro tips on teamwork to ten cities around the world.Save your spot