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

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
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Posted Tuesday in Uncategorized

Friday fun: how many celebrates Midsummer holiday or is this a Swedish tradition only?

Any other country that celebrates Midsummer holiday (this friday 22 June)?  

35 views 3 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