How to manually remove Stale Drafts from Confluence Database

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community

Purpose

If your Confluence database has a high number of stale drafts stored, Collaborative Editing may take a long time or even fail to enable. Check the bug below for more details:

CONFSERVER-55745 - Getting issue details... STATUS

Here is the definition of what is a Stale Draft: removeStaleSharedDrafts Class

Diagnosis

Diagnosis 1

To identify the number of drafts in this state on your DB, run the following query: 

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 (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null));
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 (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null));
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 (c.lastmoddate > d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null));


If it results in thousands of drafts, proceed with the solution below.

Diagnosis 2

The atlassian-confluence.log may show errors similar to this one when attempting to enable Collaborative Editing:

2018-07-05 22:26:37,795 ERROR [Long running task: EnableTask] [plugins.synchrony.tasks.AbstractConfigLongRunningTask] runInternal An error occurred when running a Synchrony ConfigLongRunningTask
 -- url: /rest/synchrony-interop/enable | referer: http://localhost:8090/admin/confluence-collaborative-editor-plugin/configure.action | traceId: d0e2ba5342874779 | userName: admin
org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
	...
at com.atlassian.confluence.pages.DefaultPageManager.removeStaleSharedDrafts(DefaultPageManager.java:764)
	...
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
	...
Caused by: java.sql.SQLException: The DELETE statement conflicted with the REFERENCE constraint "FK_NOTIFICATIONS_CONTENT". The conflict occurred in database "CONFLUENCE", table "dbo.NOTIFICATIONS", column 'CONTENTID'.
	...


This appears to be caused by the order of tables when Hibernate initiated an auto-flush. Proceed with the solution below to manually clear out the stale drafts from the database.

Solution

  1. Stop Confluence (and Synchrony if running DC)
  2. Take a backup of your database

  3. Run the following queries to remove the Stale Draft entries:

    DELETE FROM NOTIFICATIONS WHERE CONTENTID IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM CONFANCESTORS WHERE ANCESTORID IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM CONFANCESTORS WHERE DESCENDENTID IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM BODYCONTENT WHERE CONTENTID IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM CONTENTPROPERTIES WHERE CONTENTID IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM LINKS WHERE CONTENTID IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM CONTENT_LABEL WHERE contentid IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM usercontent_relation WHERE targetcontentid IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM likes WHERE CONTENTID IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM CONTENT WHERE CONTENTID IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

delete from bandana where bandanakey = 'synchrony_collaborative_editor_app_registered'
         or bandanakey = 'synchrony_collaborative_editor_app_secret'
         or bandanakey = 'synchrony_collaborative_editor_app_id'; 
    DELETE FROM NOTIFICATIONS WHERE CONTENTID IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM CONFANCESTORS WHERE ANCESTORID IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM CONFANCESTORS WHERE DESCENDENTID IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM BODYCONTENT WHERE CONTENTID IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM CONTENTPROPERTIES WHERE CONTENTID IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM LINKS WHERE CONTENTID IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM CONTENT_LABEL WHERE contentid IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM usercontent_relation WHERE targetcontentid IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM likes WHERE CONTENTID IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

DELETE FROM CONTENT WHERE CONTENTID IN
(SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid 
WHERE d.content_status = 'draft'
  AND (c.lastmoddate >= d.lastmoddate
  OR (c.lastmoddate is not null and d.lastmoddate is null)));

delete from bandana where bandanakey = 'synchrony_collaborative_editor_app_registered'
         or bandanakey = 'synchrony_collaborative_editor_app_secret'
         or bandanakey = 'synchrony_collaborative_editor_app_id';

    Note that for MySQL, the SQL statements are slightly different as for PostgreSQL. 

    • MySQL may not allow you to modify the same table which is referenced in the sub-select query. Hence, to prevent issues while running the  DELETE FROM CONTENT   statement, a temporary table is created as shown below. 

    • We have detected scenarios where the lastmoddate and creationdate columns do not include the microseconds in the timestamp. In these cases, the datatype is set to datetime instead of datetime(6).
      According to       MySQL's documentation , this implies that there is no fractional value set (the number in brackets) and the timestamp will stop at seconds (without considering the microseconds unit). Hence, the condition in the temporary table is c.lastmoddate > d.lastmoddate instead of c.lastmoddate >= d.lastmoddate . This will avoid to identify wrongly some valid drafts as stale drafts. 




    CREATE TEMPORARY TABLE TEMP_CONTENTID (SELECT d.contentid
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
OR (c.lastmoddate is not null and d.lastmoddate is null)));

CREATE INDEX x_contentid ON TEMP_CONTENTID(contentid);

DELETE FROM NOTIFICATIONS WHERE CONTENTID IN (SELECT contentid FROM TEMP_CONTENTID);

DELETE FROM CONFANCESTORS WHERE ANCESTORID IN (SELECT contentid FROM TEMP_CONTENTID);

DELETE FROM CONFANCESTORS WHERE DESCENDENTID IN (SELECT contentid FROM TEMP_CONTENTID);

DELETE FROM LINKS WHERE CONTENTID IN (SELECT contentid FROM TEMP_CONTENTID);

DELETE FROM CONTENT_LABEL WHERE contentid IN (SELECT contentid FROM TEMP_CONTENTID);

DELETE FROM USERCONTENT_RELATION WHERE targetcontentid IN (SELECT contentid FROM TEMP_CONTENTID);

DELETE FROM LIKES WHERE CONTENTID IN (SELECT contentid FROM TEMP_CONTENTID);

DELETE FROM CONTENTPROPERTIES WHERE contentid IN (SELECT contentid FROM TEMP_CONTENTID); 

DELETE FROM BODYCONTENT WHERE contentid IN (SELECT contentid FROM TEMP_CONTENTID);  

DELETE FROM CONTENT WHERE prevver IN (SELECT contentid FROM TEMP_CONTENTID);

DELETE FROM CONTENT WHERE contentid IN (SELECT contentid FROM TEMP_CONTENTID);

DELETE FROM BANDANA WHERE bandanakey = 'synchrony_collaborative_editor_app_registered' 
OR bandanakey = 'synchrony_collaborative_editor_app_secret'
OR bandanakey = 'synchrony_collaborative_editor_app_id';

DROP TEMPORARY TABLE TEMP_CONTENTID;
    In case of Constraint Violations errors...

    In some scenarios, you may run into different constraint violations, such as: 

    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (confluence_qa.CONTENTPROPERTIES, CONSTRAINT FK3fly21xfk13rqh63txw2t6k2v FOREIGN KEY (CONTENTID) REFERENCES CONTENT (CONTENTID))

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (confluence_qa.BODYCONTENT, CONSTRAINT FKmbyiayesfp1eiq6gmol3mk3yl FOREIGN KEY (CONTENTID) REFERENCES CONTENT (CONTENTID))

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (confluence_qa.CONTENT, CONSTRAINT FKwjyn6091q3l1gl7bh143ma2a FOREIGN KEY (PAGEID) REFERENCES CONTENT (CONTENTID) ON DELETE RESTRICT ON UPDATE RESTRICT)

    In these situations, we encourage you to contact Atlassian Support for further guidance, though the following statements have been proved useful: 

    DELETE FROM CONTENTPROPERTIES WHERE contentid IN (SELECT contentid FROM CONTENT WHERE prevver IN (SELECT contentid FROM TEMP_CONTENTID));

DELETE FROM CONTENTPROPERTIES WHERE contentid IN (SELECT contentid FROM CONTENT WHERE pageid IN (SELECT contentid FROM TEMP_CONTENTID));

DELETE FROM BODYCONTENT WHERE contentid IN (SELECT contentid FROM CONTENT WHERE prevver IN (SELECT contentid FROM TEMP_CONTENTID));

DELETE FROM BODYCONTENT WHERE contentid IN (SELECT contentid FROM CONTENT WHERE pageid IN (SELECT contentid FROM TEMP_CONTENTID));

DELETE FROM CONTENT WHERE prevver IN (SELECT contentid FROM TEMP_CONTENTID);

DELETE FROM CONTENT WHERE pageid IN (SELECT contentid FROM TEMP_CONTENTID);

  4. Start Confluence

  5. Go to the Collaborative Editing management page
  6. Try to change the mode to ON


Other references not predicted on this guide may exist on the database that can prevent the DELETE FROM CONTENT query from running. If that is the case, please contact our Support team and supply the error returned by that query.



Last modified on Oct 2, 2024

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.