Confluence Server find broken attachments

Confluence-Server-find-broken-attachments.md

Confluence Server find broken attachments

Tested on Confluence Server 7.19.7 with MySQL DB

Based on How to determine the file paths for a page's attachments.
This is how to find out which attachments can not be downloaded, because the file is not found on the file system.

Create /tmp/query.sql

select concat('export filepath=/data/confluence/attachments/ver003/', spaceid MOD 250, '/', (spaceid DIV 1000) MOD 250, '/', spaceid,
'/', pageid MOD 250, '/', pageid DIV 1000 MOD 250, '/', pageid,
'/', case when prevver is null then contentid else prevver end,
'/', version, '; stat $filepath > /dev/null 2>&1 && echo "ok ', CONTENT.TITLE, ' v', version, '" || echo "notOk $filepath -> my-confluence.example.com/pages/viewpageattachments.action?pageId=', pageid, ' , title=', CONTENT.TITLE, '" # '
) as DiskLocV3,
spaceid, pageid, contentid, version, CONTENT.TITLE, concat('https://my-confluence.example.com/pages/viewpageattachments.action?pageId=', pageid)
from CONTENT where contenttype = 'ATTACHMENT' and
pageid in (
select c.contentid FROM CONTENT c JOIN SPACES s ON s.spaceid = c.spaceid
where c.contenttype = 'PAGE'
and s.spaceid is not null
AND c.prevver IS NULL)
order by pageid asc, CONTENT.TITLE asc, version desc;

Run it: mysql -u root -p confluence < /tmp/query.sql > /tmp/result.sh
Result will be

export filepath=/data/confluence/attachments/ver003/19/91/126091269/99/204/125954349/126532999/2; stat $filepath > /dev/null 2>&1 && echo "ok some-filename.png v2" || echo "notOk $filepath -> https://my-confluence.example.com/pages/viewpageattachments.action?pageId=125954349 , title=some-filename.png" #     126091269       125954349       126532999       2       
export filepath=/data/confluence/attachments/ver003/19/91/126091269/99/204/125954349/126532999/1; stat $filepath > /dev/null 2>&1 && echo "ok some-filename.png v1" || echo "notOk $filepath -> https://my-confluence.example.com/pages/viewpageattachments.action?pageId=125954349 , title=some-filename.png" #     126091269       125954349       128057379       1       
export filepath=/data/confluence/attachments/ver003/19/91/126091269/99/204/125954349/130089699/2; stat $filepath > /dev/null 2>&1 && echo "ok some-filename.svg v2" || echo "notOk $filepath -> https://my-confluence.example.com/pages/viewpageattachments.action?pageId=125954349 , title=some-filenamep.svg" #     126091269       125954349       130089699       2     

Now make it executable and run it: chmod +x /tmp/result.sh && /tmp/result.sh > /tmp/result.txt
Every file is being tested if it exists

ok some-filename.png v2
notOk /data/confluence/attachments/ver003/19/91/126091269/99/204/125954349/126532999/1 -> https://my-confluence.example.com/pages/viewpageattachments.action?pageId=125954349 , title=some-filename.png
notOk /data/confluence/attachments/ver003/19/91/126091269/99/204/125954349/130089699/2 -> https://my-confluence.example.com/pages/viewpageattachments.action?pageId=125954349 , title=some-filename.svg

Now you hava a list showing you which files are not found.
Try to find them, by searching a directory named "...moved_out_of_the_way"

$ find  /data/confluence/attachments -name "*moved_out_of_the_way*"
/data/confluence/attachments/ver003/19/91/126091269/99/204/125954349/126532999_moved_out_of_the_way
/data/confluence/attachments/ver003/85/151/35651585/240/156/102156990_moved_out_of_the_way