Removing orphaned draft
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
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 851970Cause
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.
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:
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 .
- Shutdown Confluence
- Execute the following SQL queries (you may need to alter these for your database system)
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);Delete the content of the drafts
DELETE FROM BODYCONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM TC);Delete content properties of the contents we will remove:
DELETE FROM CONTENTPROPERTIES WHERE CONTENTID IN (SELECT CONTENTID FROM TC);Delete image details of attachments we will remove in the next query:
DELETE FROM IMAGEDETAILS WHERE ATTACHMENTID IN (SELECT CONTENTID FROM TC);Delete the content label still referred to the content
DELETE FROM CONTENT_LABEL WHERE CONTENTID IN (SELECT CONTENTID FROM TC);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);Delete any attachments associated with the orphaned drafts
DELETE FROM CONTENT WHERE PAGEID IN (SELECT CONTENTID FROM TC);
Delete any entries present in the Link table
DELETE FROM LINKS WHERE CONTENTID IN (SELECT CONTENTID FROM TC);Delete the orphaned drafts themselves
DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM TC);Delete the temp tables
DROP TABLE TC; DROP TABLE TCPS;
- 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.
- Shutdown Confluence
- Execute the following SQL queries (you may need to alter these for your database system)
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));Delete image metadata of attachments we will remove in the next query:
For Confluence 5.6 and belowDELETE 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)));Delete any attachments associated with the orphaned drafts
For Confluence 5.6 and belowDELETE from ATTACHMENTS where PAGEID in (select CONTENTID from CONTENT where CONTENTTYPE='DRAFT' and DRAFTPAGEID not in (select CONTENTID from CONTENT));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;