Sql query to get all attachment in jira

[anuj__sharma]

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

[anuj__sharma · Accepted solution]

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

[Colin McDermott_GLiNTECH]

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. 

[Francisco Crespo Smith]

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