Importing issues into the Jira database using SQL

[New Jira Developer]

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.

[New Jira Developer]

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.