Importing issues into the Jira database using SQL

Hi,

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.

Thanks.

1 answer

This widget could not be displayed.

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:

<JIRA-HOME>/caches/indexes

For me this was C:\Program Files\Application Data\JIRA\caches\indexes

3. Delete the following sub folders:

- changes

- comments

- entities

- issues

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.

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted yesterday in Jira

What modern development practices are at the heart of how your team delivers software?

Hey Community mates! Claire here from the Software Product Marketing team. We all know software development changes rapidly, and it's often tough to keep up. But from our research, we've found the h...

69 views 0 1
Join discussion

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