How to find total Database Attachment size in Confluence 5.7?

Andrew Wolpers [BlackPearl PDM]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 27, 2015

I'm looking to find the sum of all attachments on a Confluence system to migrate from the deprecated Database attachment storage to local. As the system is fairly large, I'm looking to see if I need to bump up the storage on the system itself. I have searched, but the only query of use for Mysql I've been able to find is:

mysql> SELECT a.TITLE AS "Attachment Name", a.FILESIZE, c.TITLE AS "Page Title", s.SPACENAME AS "Space Name", CONCAT("https://instancename/pages/viewpageattachments.action?pageId=", a.PAGEID) AS "Location" FROM attachments a JOIN content c ON a.PAGEID = c.CONTENTID JOIN spaces s ON c.SPACEID = s.SPACEID ORDER BY a.FILESIZE DESC;

This has returned an error, since I believe the database scheme was changed a little bit when 5.7 started:

ERROR 1146 (42S02): Table 'confluence4.attachments' doesn't exist

 

I'm not against massaging the numbers and doing some addition, but any help for the initial query would be great! Thanks for reading.

2 answers

1 accepted

3 votes
Answer accepted
Branno
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
December 17, 2015

Howdy Andrew!

I actually just had a colleague ask me the same question! While seeing if a query already existed, I came across this and hoped I could still be helpful. Atlassian removed the attachments table as of Confluence 5.7, and moved their database entries into the content table. You can use the following query to find the total size (in bytes):

select sum(LONGVAL) from CONTENTPROPERTIES
where CONTENTID in (select CONTENTID from CONTENT where CONTENTTYPE = 'ATTACHMENT') 
and PROPERTYNAME = 'FILESIZE';

Cheers,
Stephen Brannen | Confluence Support

Andrew Wolpers [BlackPearl PDM]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 17, 2015

Thanks Stephen! I did get closer to some answers, and you were basically on the right direction. To add on to this, here's what I did (with the help of Daniel from support) before we added the space field: SELECT octet_length(a.data) AS size, a.attachmentid, c.title AS attachmentTitle, u.username AS guilty, co.title AS pageTitle FROM ATTACHMENTDATA AS a JOIN CONTENT AS c ON c.contentid = a.attachmentid JOIN USER_MAPPING AS u ON u.user_key = c.creator JOIN CONTENT AS co ON c.pageid = co.contentid ORDER BY size DESC

0 votes
Steven F Behnke
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 27, 2015

Perhaps running the "Octet length" function against the "attachmentdata" table? Since attachments stored in-database are stored in the "attachmentdata" table, perhaps we can lift the size directly. The "Octet length" function returns the defined length of the column in bytes, which sounds about right. The actual saved size might still be different but perhaps this is a workaround.

SELECT OCTET_LENGTH(attachmentdata);
Andrew Wolpers [BlackPearl PDM]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 27, 2015

Thanks Steve, but no luck. Saw this error: {code} SELECT OCTET_LENGTH(attachmentdata); ERROR 1054 (42S22): Unknown column 'attachmentdata' in 'field list' {code} Going to do some digging.

Steven F Behnke
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 27, 2015

Well, Attachmentdata is a table, not a column.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events