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.
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';
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
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.
Do you use templates with Confluence? Take part in a remote 1-hr workshop. You'll receive USD $100 for your time! We're looking for people to participate in a remote 1-hr workshop...
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!
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