Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,363,022
Community Members
 
Community Events
168
Community Groups

Sql query to get all attachment in jira

I am trying to get the Jira issue attachment details from database using a sql query.

The query is :

SELECT  pkey, CI.NEWVALUE, CI.NEWSTRING FROM jiraissue JI, 
 changegroup CG, 
 changeitem CI 
 WHERE JI.ID = CG.issueid 
   AND JI.PROJECT IN (select id from project 
                    where pkey = 'projectkey') 
   AND CG.ID = CI.groupid 
   AND CI.FIELD = 'Attachment' 
   AND JI.issuestatus IN (select ID from issuestatus);

When i verify this data with the attachment folder in jira/attachment
The data fetched from database is coming out to be less(incomplete).

There are some attachment in the jira/attachment folder with the syntax :
"attachmentid_attachmentname" which i am not able to retrieve using database.

Can someone suggest a solution



2 answers

1 accepted

0 votes
Answer accepted

I have got the required data from the 'fileattachment' table.

 

But still i have a doubt :
why jira saves some files in the file system as 'attachmentid' only and 
some files as 'attachmentid_filename' ?

This is also described here
https://confluence.atlassian.com/jirakb/jira-unable-to-find-attachments-269984254.html


But can some one clarify please

 

 

Thanks

Interesting issue.

Unsure if this helps , I think i have found a bug in:

https://confluence.atlassian.com/jirakb/jira-unable-to-find-attachments-269984254.html?_ga=2.215639447.1047557927.1593908543-11622563.1582323089

<JIRA_HOME>/attachments/<PROJECT>/<ISSUE_ID>/<ID>

On Every server I have seen lately Project is followed by 10000

jira/data/data/attachments/KEY/10000/KEY-1/13821 

I don't get why the 10000 is added to the path. Yet it seems jira add's a duplicate schema with the id of 10000 on many installs. It could be that this path changes as per project schema. 

The reason for this is mentioned on this documentation page: Important subdirectories 

Attachments are stored in the following directory: $JIRA_Home/data/jira/data/attachments/PROJECT_KEY/x0000/ISSUE_KEY/ID

where the subdirectory x0000 is created and numbered according to the number of issues on the JIRA instance. It uses the following pattern:

Directory name
10000
20000
30000
Issues 1-10,000 10,001-20,000 20,001-30,000

Suggest an answer

Log in or Sign up to answer