How to cleanup older drafts than page content?

Question

Hi,

we just upgrade to Confluence (Local) 6.8.1 and reactivate Collaborative Editing. In 6.2 (version before) we disabled Collaborative Editing about 2 weeks ago, due to Issue CONFSERVER-51998.

Now we are facing the problem, some users report pages containing old information in Edit-Mode. If they view a page, everything is on current mode, but in Edit-Mode the page sometimes is empty and sometimes contain older data from 2 or 3 versions ago. This seems to be an older draft, which was not updated due to disabled Collaborative Editing.

Is there a way, to clean up all drafts in the whole system? Just an Update on database to all pages with status 'draft' seems not working (not sure why).

Thanks,

Markus

unknown · Answer

Hi Shannon,

thanks for that bugreport. I've tested to delete by the above statement, but I got an foreign key constraint error to body content.

Due to this error I've tried to delete by following the steps mentioned in knowlegde base articel "How to Remove a Page Manually in the Database Using SQL Commands" (https://confluence.atlassian.com/confkb/how-to-remove-a-page-manually-in-the-database-using-sql-commands-385321460.html). I've changed the sqls to CONTENTTYPE 'PAGE' and CONTENT_STATUS 'draft'. And also added IMAGEDETAILS, EXTRNLNKS, CONTENT_PERM, CONTENT_PERM_SET, CONFANCESTORS to the list of tables with entries to delete.

DELETE FROM IMAGEDETAILS WHERE ATTACHMENTID IN (SELECT XID FROM (SELECT C.CONTENTID AS XID FROM CONTENT C WHERE PAGEID IN (SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'PAGE' AND CONTENT_STATUS = 'draft'))AS X);
DELETE FROM EXTRNLNKS WHERE CONTENTID IN (SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'PAGE' AND CONTENT_STATUS = 'draft');
DELETE FROM CONTENT_PERM WHERE CPS_ID IN (SELECT ID AS CPS_ID FROM CONTENT_PERM_SET WHERE CONTENT_ID IN (SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'PAGE' AND CONTENT_STATUS = 'draft'));
DELETE FROM CONTENT_PERM_SET WHERE CONTENT_ID IN (SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'PAGE' AND CONTENT_STATUS = 'draft');
DELETE FROM CONFANCESTORS WHERE ANCESTORID IN (SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'PAGE' AND CONTENT_STATUS = 'draft');

Still with all delete Statements, I get an error on "Delete" Statement from Content

DELETE FROM CONTENT WHERE CONTENTTYPE = 'PAGE' AND CONTENT_STATUS = 'draft';

A special page shows a hierarchy containing a draft "in the middle". This seems a bit strange.

I have no idea where this could come from.

Best Regards,

Markus

Shannon S · Answer

Hi Markus,

I believe this is the behavior described in the following bug:

* CONFSERVER-53589

I would recommend using the solution there to remove your older drafts. Please note the recommendation there is to remove all drafts so make sure that nothing is unsaved that needs retaining.

As for only deleting certain drafts earlier than a specific date, I am not aware of the SQL required to do that, but hopefully, the instructions there can get you started:

* Existing shared drafts remain in the database. The following SQL query returns result(s).SELECT * FROM CONTENT WHERE CONTENTTYPE = 'PAGE' AND CONTENT_STATUS = 'draft';

* Discard the drafts via *Profile Picture* > *Drafts*.Reference: https://confluence.atlassian.com/doc/drafts-149040.html#Drafts-deleteDeleteadraft

* Remove all drafts from the database using the following SQL query.DELETE FROM CONTENT WHERE CONTENTTYPE = 'PAGE' AND CONTENT_STATUS = 'draft';

It is important that you backup your data before performing any modifications to the database. Please test any {{INSERT}}, {{UPDATE}} or {{DELETE}} queries on a staging environment prior to rolling out changes onto production environment.

Let me know if you have any questions!

Regards,

Shannon