Removing orphaned draft

Still need help?

The Atlassian Community is here for you.

Ask the community

Problem

When a user deletes any Confluence page, Confluence will not delete the draft associated with the page automatically. This results in orphaned drafts stored in the Confluence database. The following stack trace will be thrown to atlassian-confluence.log whenever Confluence tries to save a new draft on another page.

2012-12-19 23:44:56,278 WARN [http-8090-4] [confluence.pages.actions.AbstractCreateAndEditPageAction] getCancelResult Removing invalid draft: Draft = { id: 851971, type: page, title: X, pageId: 851970}, for user : EmbeddedCrowdUser{name='admin', displayName='Septa Cahyadiputra', directoryId=294913} 
 -- url: /pages/doeditpage.action | userName: admin | referer: http://localhost:8090/pages/editpage.action?pageId=524292
com.atlassian.confluence.core.service.NotValidException: Page not found with id 851970

Cause

This issue is caused by a bug which recorded and discussed here:

CONF-27594 - Getting issue details... STATUS

Workaround

In order to ensure that the stack trace noise does not appear in the Confluence logs, it is necessary to delete the orphaned page from the Confluence database.

(warning) Please notice that the Drafts feature works differently In Confluence 5.7 and above than it works on 5.6 and bellow. Also, when Collaborative Editing is disabled  the drafts feature behave as if it was running on Confluence 5.6 and bellow independent of its version. For more info on this, please check How drafts work on Confluence

Here are the steps:

(info) Please make sure to test the SQL queries on your database system engine in a test instance before running them in production as it may have slightly differences on the syntax depending on which engine is used.

Always backup your data before performing any modifications to the database.

The following queries were prepared for Confluence 5.7 and above with Collaborative edit ENABLED .

  1. Shutdown Confluence
  2. Execute the following SQL queries (you may need to alter these for your database system)
    1. Create temps table and insert it with DRAFT page contents that will be referred to

      CREATE TABLE TC LIKE CONTENT;
      CREATE TABLE TCPS LIKE CONTENT_PERM_SET;
      INSERT INTO TC SELECT * FROM CONTENT WHERE CONTENTTYPE = 'DRAFT';
      INSERT INTO TC SELECT * FROM CONTENT WHERE PAGEID IN (SELECT CONTENTID FROM TC);
      INSERT INTO TCPS SELECT * FROM CONTENT_PERM_SET WHERE CONTENT_ID IN (SELECT CONTENTID FROM TC);
    2. Delete the content of the drafts

      DELETE FROM BODYCONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM TC);
    3. Delete content properties of the contents we will remove:

      DELETE FROM CONTENTPROPERTIES WHERE CONTENTID IN (SELECT CONTENTID FROM TC);
    4. Delete image details of attachments we will remove in the next query:

      DELETE FROM IMAGEDETAILS WHERE ATTACHMENTID IN (SELECT CONTENTID FROM TC);
    5. Delete the content label still referred to the content

      DELETE FROM CONTENT_LABEL WHERE CONTENTID IN (SELECT CONTENTID FROM TC);
    6. Delete content permission and the content permission settings referred in content table

      DELETE FROM CONTENT_PERM WHERE CPS_ID IN (SELECT ID FROM TCPS);
      DELETE FROM CONTENT_PERM_SET WHERE CONTENT_ID IN (SELECT CONTENTID FROM TC);


    7. Delete any attachments associated with the orphaned drafts

      DELETE FROM CONTENT WHERE PAGEID IN (SELECT CONTENTID FROM TC);


    8. Delete any entries present in the Link table   

      DELETE FROM LINKS WHERE CONTENTID IN (SELECT CONTENTID FROM TC);



    9. Delete the orphaned drafts themselves

      DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM TC);
    10. Delete the temp tables

      DROP TABLE TC;
      DROP TABLE TCPS;
  3. Restart Confluence
The following queries was prepared for when Collaborative editing is DISABLED  or Confluence version is 5.6 and below.

Always backup your data before performing any modifications to the database.

  1. Shutdown Confluence
  2. Execute the following SQL queries (you may need to alter these for your database system)
    1. Delete the content of the drafts

      DELETE FROM BODYCONTENT where CONTENTID IN
       (select CONTENTID from CONTENT where CONTENTTYPE='DRAFT' and DRAFTPAGEID not in
        (select CONTENTID from CONTENT));
    2. Delete image metadata of attachments we will remove in the next query:

      For Confluence 5.6 and below
       DELETE FROM IMAGEDETAILS WHERE ATTACHMENTID IN
       (SELECT ATTACHMENTID from ATTACHMENTS where PAGEID in
        (select CONTENTID from CONTENT where CONTENTTYPE='DRAFT' and DRAFTPAGEID not in
         (select CONTENTID from CONTENT)));


    3. Delete any attachments associated with the orphaned drafts

      For Confluence 5.6 and below
      DELETE from ATTACHMENTS where PAGEID in
       (select CONTENTID from CONTENT where CONTENTTYPE='DRAFT' and DRAFTPAGEID not in
        (select CONTENTID from CONTENT));
    4. Delete the orphaned drafts themselves

      PostgreSQL

      DELETE from CONTENT where CONTENTID in
       (select CONTENTID from CONTENT where CONTENTTYPE='DRAFT' and DRAFTPAGEID not in
        (select CONTENTID from CONTENT)); 

      MySQL

       create table content_temp as select * from CONTENT; 
      delete from CONTENT where CONTENTID in (select CONTENTID from content_temp where CONTENTTYPE='DRAFT' and DRAFTPAGEID not in (select CONTENTID from content_temp));
      drop table content_temp;
Last modified on Mar 21, 2024

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.