Skip to content

Instantly share code, notes, and snippets.

@kekru
Last active May 19, 2023 13:17
Show Gist options
  • Save kekru/02e660840912f74027d3a65c345219aa to your computer and use it in GitHub Desktop.
Save kekru/02e660840912f74027d3a65c345219aa to your computer and use it in GitHub Desktop.
Confluence Server find broken attachments

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment