How to find total Database Attachment size in Confluence 5.7?

[Andrew Wolpers [BlackPearl PDM]]

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.

[Branno · Accepted solution]

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]]

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

[Steven F Behnke]

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);

• https://confluence.atlassian.com/display/DOC/Confluence+Data+Model
  
• https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_octet-length

[Andrew Wolpers [BlackPearl PDM]]

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]

Well, Attachmentdata is a table, not a column.