Confluence 'Missing attachments' images

Sometimes Confluence users will report that their attachments have disappeared, with an 'Unknown Attachment' placeholder image showing:

The weird thing is that the attachment(s) do actually exist, attached to the page. It's just the image reference in the page that's broken.

If you view the Confluence page source through the Source Editor plugin, you'll see something weird: the 'Unknown Attachment' isn't Confluence saying "hey, there's no attachment by this name any more". It is in fact an embedded image of the 'unknown-attachment' image:

How does this happen?

This is a result of a bug in the Confluence collaborative editor:

CONFSERVER-55928 - Getting issue details... STATUS

Am I affected?

To see if any of your Confluence's pages are affected by this, run this SQL on the database (Postgres-flavoured):

select 'https://confluence.mycompany.com/pages/viewpage.action?pageId=' || contentid AS url, title, lastmoddate from content JOIN bodycontent USING (contentid) WHERE content_status='current' and prevver is null and body like '%/plugins/servlet/confluence/placeholder/unknown-attachment%';

How to fix?

Since the image attachments are actually there, it's just a matter of editing the page and replacing the 'Unknown Attachment' images with the correct images.

Easier said than done sometimes, especially if the page has been edited many times after the images were corrupted. You'll need to loop tediously through the page history, finding the point at which the image(s) were deleted, then fix the current page's images.

This is exceptionally tedious, but some SQL can help ease the pain:

• First, find the contentid IDs of affected pages, using the SQL above. Store the contentid  list in a text file.

  • Now, for each contentid  (for example, 22432553) run this SQL:

    select baseurl || current AS current
            , baseurl || bad AS bad
            , baseurl || good AS good
    from (
            select baseurl
                    , current
                    , contentid AS bad
                    , lastmoddate
                    , lag(contentid, 1) over (order by lastmoddate) AS good
                    , (body ~ 'unknown-attachment') missingattach
                    from (VALUES (22432553)) AS t (current)
                    CROSS JOIN (VALUES ('https://confluence.example.com/pages/viewpage.action?pageId=')) AS b(baseurl)
                    CROSS JOIN content 
                    JOIN bodycontent USING (contentid) 
                    WHERE content_status='current'
                            and (contentid= current or prevver = current)
                    ORDER BY lastmoddate
    ) x where missingattach=true limit 1;
    
    

    This will give you three URLs:  the current page, the first 'gone bad' revision, and the 'just before we went bad' revision:

    confluence=> select baseurl || current AS current
            , baseurl || bad AS bad
            , baseurl || good AS good
    from (
            select baseurl
                    , current
                    , contentid AS bad
                    , lastmoddate
                    , lag(contentid, 1) over (order by lastmoddate) AS good
                    , (body ~ 'unknown-attachment') missingattach
                    from (VALUES (22432553)) AS t (current)
                    CROSS JOIN (VALUES ('https://confluence.example.com/pages/viewpage.action?pageId=')) AS b(baseurl)
                    CROSS JOIN content 
                    JOIN bodycontent USING (contentid) 
                    WHERE content_status='current'
                            and (contentid= current or prevver = current)
                    ORDER BY lastmoddate
    ) x where missingattach=true limit 1;
    ┌─[ RECORD 1 ]────────────────────────────────────────────────────────────────────┐
    │ current │ https://confluence.example.com/pages/viewpage.action?pageId=22432553  │
    │ bad     │ https://confluence.example.com/pages/viewpage.action?pageId=101730306 │
    │ good    │ https://confluence.example.com/pages/viewpage.action?pageId=101729414 │
    └─────────┴───────────────────────────────────────────────────────────────────────┘

  • Now:
    • Open all three URLs in three browser tabs
    • In the 'bad' tab, locate the 'Unknown Attachment' image and find some identifying text just above it. Perhaps the word Advanced  in my example:
      
    • On the 'good' tab search for the marker word. Below it you will see the correct image. Click on the image and note the end part  of the URL, which will be the filename. Store the filename in your copy/paste buffer.
    • On the 'current' tab:
      • edit the page
      • Search for your marker word
      • Delete the 'Unknown Attachment' image
      • Press '!' to begin inserting an image, and paste the image filename from your copy/paste buffer.
      • Repeat process for however many broken images you find.
      • Save the page.

Still tedious, but not so bad.