Summary

Based on the removeStaleSharedDrafts class, a stale draft is defined as:

Pages with a content_status 'draft' and which have a last modified date that is earlier than or equal to the last modified date on the published page.

However, it is possible that a draft is marked as stale only by definition but in theory, it is still a valid draft. Refer to the 'Steps to reproduce' section for more information.

Steps to reproduce

1. Create a new page and insert a task to the page.
2. Publish the page.
3. Edit the page again to add some text, then close the editor while opting to save it as a draft.
4. Run the query in How to manually remove Stale Drafts from Confluence Database to confirm that a stale draft doesn't exist yet in the DB.

   SELECT d.contentid, d.title, d.prevver, d.lastmoddate, c.lastmoddate
   FROM CONTENT d
   JOIN CONTENT c
     ON d.prevver = c.contentid
   WHERE d.content_status = 'draft'
     AND d.prevver IS NOT NULL
     AND c.lastmoddate > d.lastmoddate;

5. On the page in 'View' mode, mark the task as completed. This creates a new version of the page without the draft being published.
6. Run the query in step #4 again and we will see that there is one stale draft.
7. Set Collaborative Editing to 'Off' or 'Limited' and then to 'On' to trigger the stale draft removal process.

Expected results

The draft should not be removed despite it fitting the definition/criteria of a stale draft.

Actual results

The draft is removed because the draft's lastmoddate is earlier than the page's lastmoddate, which fits the definition of a stale draft.

Workaround

Before turning Collaborative Editing ON you can retrieve information from possibly affected pages by running the query below.
With that information, Confluence administrators will be able to share the draft content if a user asks for it.

This query will list the following attributes:

• Draft ID
• Draft last modify date
• Draft storage format
• Current version pageID
• Link to the current page
• Creator username
• Last modifier username
• Last modify date

SELECT  DISTINCT(d.contentid) AS DRAFT_ID, d.lastmoddate AS DRAFT_LAST_MOD_DATE, bd.body AS DRAFT_STORAGE_FORMAT, c.contentid AS CURRENT_VERSION_ID, '<Confluence Base URL>/viewpage.action?pageId='||c.contentid AS CURRENT_VERSION_LINK, umc.lower_username AS CURRENT_VERSION_CREATOR, umcm.lower_username AS CURRENT_VERSION_LAST_MODIFIER, c.lastmoddate AS CURRENT_VERSION_LASTMODDATE
FROM CONTENT d
JOIN CONTENT c ON d.prevver = c.contentid
JOIN "AO_BAF3AA_AOINLINE_TASK" i ON d.prevver = i."CONTENT_ID"
JOIN BODYCONTENT bd ON bd.contentid = d.contentid
JOIN USER_MAPPING umc ON umc.user_key = c.creator
JOIN USER_MAPPING umcm ON umcm.user_key = c.lastmodifier
WHERE d.content_status = 'draft'
  AND d.prevver IS NOT NULL
  AND c.lastmoddate > d.lastmoddate
  AND (i."COMPLETE_DATE" > d.lastmoddate OR i."UPDATE_DATE" > d.lastmoddate)
;