How to find total Database Attachment size in Confluence 5.7?

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

This widget could not be displayed.

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

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

This widget could not be displayed.
Steven Behnke Community Champion Jul 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);

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 Behnke Community Champion Jul 27, 2015

Well, Attachmentdata is a table, not a column.

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Monday in Confluence

Why start from scratch? Introducing four new templates for Confluence Cloud

Hi my Community friends!  For those who don't know me, I'm a product marketer on the Confluence Cloud team - nice to meet you! For those of you who do, you know that I've been all up in your Co...

251 views 2 4
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