How to remove all shared drafts and start with a clean state using Collaborative Editing

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問


プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。

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.

*Fisheye および Crucible は除く

目的

Important Definitions

To understand more about this KB, the following definitions are necessary:

  • CE: Collaborative Editing.
  • Personal draft: a draft that is visible only by its creator and was saved while Collaborative Editing was disabled up to version 7.5.
  • Shared draft: a draft that can be shared with collaborators other than just its creator and was saved while Collaborative Editing was enabled. This type of draft is still accessible in versions 7.5 and newer even with CE disabled due to the removal of Limited mode. More details here.

More information about drafts can be found in How Do Drafts Work on Confluence.


You have plans to enable Collaborative Editing (CE) after the following scenario:

  1. Confluence was running with CE enabled for some time.
    1. This means users have created shared drafts associated to published content.
  2. CE was disabled for any reason (maintenance or company constraints) and Confluence has been running like this for quite some time.
    1. This means users have been creating personal drafts.
  3. At this point, you would like to enable CE as it was enabled from scratch without any reference to old shared drafts.
    1. Any shared draft (and associated content, such as attachments) should be removed from the database before enabling CE.
    2. Removing old shared drafts prevents users from editing old versions of drafts that are likely outdated.


Part of this KB may be interesting to you on the following scenario as well:

  1. Confluence has been running with CE enabled for some time.
  2. You plan to permanently disable CE.
  3. Because of this requirement, you would like to clean any record associated with shared drafts.

ソリューション

Since there's no way to bulk delete shared drafts from the UI, the Confluence administrator should perform this operation from the database level.

This KB will walk you through the necessary steps to delete shared drafts and any of their associated contents, such as attachments.

データベースの変更を行う場合は必ず事前にバックアップを取得してください。可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。

 Before starting, we can identify all shared drafts which are going to delete after following further steps. Below is the SQL that will help you make a decision for removing the shared drafts from the system so that we will be aware about that:

1. Stop Confluence following your standard procedure.

  • If running Confluence data center, you must stop the application on all nodes and confirm Synchrony process is stopped as well.

2. Run the following SQL queries in your Confluence database to delete every record associated to shared drafts.

  • Each sub-step has two queries: 1) to count the number of records that will be deleted; and 2) to actually delete them.
  • If you confirm there's no record to be deleted, there's no meaning on running the delete statement.
  • (info) The queries below were tested on MySQL and on PostgreSQL. You may need to pair with your DBA to port them to your database engine if using anything different than that.


------- Make child of drafts orphan pages -------
--- SQL query to count affected records ---
select count(c.CONTENTID) from CONTENT c join CONTENT d on d.CONTENTID=c.PARENTID where d.CONTENT_STATUS='draft';

--- SQL query to update affected records ---
update CONTENT set PARENTID=null where CONTENTID in (select c.CONTENTID from CONTENT c join CONTENT d on d.CONTENTID=c.PARENTID where d.CONTENT_STATUS='draft');



------- Delete notifications related to drafts -------
--- SQL query to count affected records ---
select count(*) from NOTIFICATIONS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from NOTIFICATIONS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete drafts that are child in CONFANCESTORS -------
--- SQL query to count affected records ---
select count(*) from CONFANCESTORS where DESCENDENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONFANCESTORS where DESCENDENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete drafts that are ancestors in CONFANCESTORS -------
--- SQL query to count affected records ---
select count(*) from CONFANCESTORS where ANCESTORID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONFANCESTORS where ANCESTORID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete body of drafts -------
--- SQL query to count affected records ---
select count(CONTENTID) from BODYCONTENT where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from BODYCONTENT where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete links related to drafts -------
--- SQL query to count affected records ---
select count(*) from LINKS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from LINKS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete labels relation to drafts -------
--- SQL query to count affected records ---
select count(*) from CONTENT_LABEL where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONTENT_LABEL where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to count affected records ---
select count(*) from CONTENT_LABEL where CONTENTID in (select c.CONTENTID from CONTENT c join CONTENT_LABEL cl on cl.CONTENTID=c.CONTENTID where c.CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE'));

--- SQL query to update affected records ---
delete from CONTENT_LABEL where CONTENTID in (select c.CONTENTID from CONTENT c join CONTENT_LABEL cl on cl.CONTENTID=c.CONTENTID where c.CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE'));



------- Delete users relation to drafts -------
--- SQL query to count affected records ---
select count(*) from USERCONTENT_RELATION where TARGETCONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from USERCONTENT_RELATION where TARGETCONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');


--- !! THIS TABLE DOES NOT EXIST AFTER CONFLUENCE 7.X !! ---
------- Delete external links related to drafts -------
--- SQL query to count affected records ---
select count(*) from EXTRNLNKS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from EXTRNLNKS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete likes related to drafts -------
--- SQL query to count affected records ---
select count(*) from LIKES where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from LIKES where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete attachment properties related to drafts -------
--- SQL query to count affected records ---
select count(PROPERTYID) from CONTENTPROPERTIES where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to delete affected records ---
delete from CONTENTPROPERTIES where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to count affected records ---
SELECT count(CONTENTID) from LINKS where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to delete affected records ---
delete from LINKS where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete body of attachments related to drafts -------
--- SQL query to count affected records ---
select count(bc.BODYCONTENTID) from BODYCONTENT bc join CONTENT c on c.CONTENTID=bc.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';
 
--- SQL query to update affected records ---
delete from BODYCONTENT where BODYCONTENTID in (select bc.BODYCONTENTID from BODYCONTENT bc join CONTENT c on c.CONTENTID=bc.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 


------- Delete image properties related to drafts -------
--- SQL query to count affected records ---
select count(*) from IMAGEDETAILS where ATTACHMENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from IMAGEDETAILS where ATTACHMENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete previous versions of attachments related to drafts -------
--- SQL query to count affected records ---
select count(a.CONTENTID) from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and a.PREVVER is not NULL and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from CONTENT where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and a.PREVVER is not NULL and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete attachments related to drafts -------
--- SQL query to count affected records ---
select count(CONTENTID) from CONTENT where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONTENT where CONTENTID in (select CONTENTID from CONTENT where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE'));



------- Delete properties of comments related to drafts -------
--- SQL query to count affected records ---
select count(cp.PROPERTYID) from CONTENTPROPERTIES cp join CONTENT c on c.CONTENTID=cp.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='COMMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from CONTENTPROPERTIES where PROPERTYID in (select cp.PROPERTYID from CONTENTPROPERTIES cp join CONTENT c on c.CONTENTID=cp.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='COMMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete body of comments related to drafts -------
--- SQL query to count affected records ---
select count(bc.BODYCONTENTID) from BODYCONTENT bc join CONTENT c on c.CONTENTID=bc.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='COMMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from BODYCONTENT where BODYCONTENTID in (select bc.BODYCONTENTID from BODYCONTENT bc join CONTENT c on c.CONTENTID=bc.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='COMMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete any other contents related to drafts -------
--- SQL query to count affected records ---
select count(CONTENTID) from CONTENT where PAGEID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONTENT where CONTENTID in (select CONTENTID from CONTENT where PAGEID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE'));



------- Delete restrictions related to drafts – first query -------
--- SQL query to count affected records ---
select count(cp.ID) from CONTENT_PERM cp join CONTENT_PERM_SET cps on cps.ID=cp.CPS_ID join CONTENT d on d.CONTENTID=cps.CONTENT_ID where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from CONTENT_PERM where ID in (select cp.ID from CONTENT_PERM cp join CONTENT_PERM_SET cps on cps.ID=cp.CPS_ID join CONTENT d on d.CONTENTID=cps.CONTENT_ID where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete restrictions related to drafts – second query -------
--- SQL query to count affected records ---
select count(cps.ID) from CONTENT_PERM_SET cps join CONTENT d on d.CONTENTID=cps.CONTENT_ID where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from CONTENT_PERM_SET where ID in (select cps.ID from CONTENT_PERM_SET cps join CONTENT d on d.CONTENTID=cps.CONTENT_ID where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete properties related to drafts -------
--- SQL query to count affected records ---
select count(*) from CONTENTPROPERTIES where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONTENTPROPERTIES where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete contents that are drafts -------
--- SQL query to count affected records ---
select count(d.CONTENTID) from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from CONTENT where CONTENT_STATUS = 'draft' and CONTENTTYPE = 'PAGE';



------- Clear Synchrony IDs from the BANDANA table -------
--- SQL query to update affected records ---
delete from BANDANA where BANDANAKEY = 'synchrony_collaborative_editor_app_registered'
         or BANDANAKEY = 'synchrony_collaborative_editor_app_secret'
         or BANDANAKEY = 'synchrony_collaborative_editor_app_id';



------- Clear tables associated to Synchrony -------
truncate table "EVENTS";
truncate table "SECRETS";
truncate table "SNAPSHOTS";
/******* Make child of drafts orphan pages *******/
/* SQL query to count affected records */
select count(c.CONTENTID) from CONTENT c join CONTENT d on d.CONTENTID=c.PARENTID where d.content_status='draft';
 
/* SQL query to update affected records */
create temporary table TEMP_CONTENTID (select c.CONTENTID from CONTENT c join CONTENT d on d.CONTENTID=c.PARENTID where d.content_status='draft' order by c.CONTENTID);
update CONTENT set PARENTID=null where CONTENTID in (select CONTENTID from TEMP_CONTENTID);
drop temporary table TEMP_CONTENTID;
 
 
 
/******* Delete notifications related to drafts *******/
/* SQL query to count affected records */
select count(*) from NOTIFICATIONS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
/* SQL query to update affected records */
delete from NOTIFICATIONS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
 
 
/******* Delete drafts that are child in CONFANCESTORS *******/
/* SQL query to count affected records */
select count(*) from CONFANCESTORS where DESCENDENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
/* SQL query to update affected records */
delete from CONFANCESTORS where DESCENDENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
 
 
/******* Delete drafts that are ancestors in CONFANCESTORS *******/
/* SQL query to count affected records */
select count(*) from CONFANCESTORS where ANCESTORID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
/* SQL query to update affected records */
delete from CONFANCESTORS where ANCESTORID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
 
 
/******* Delete body of drafts *******/
/* SQL query to count affected records */
select count(CONTENTID) from BODYCONTENT where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
/* SQL query to update affected records */
delete from BODYCONTENT where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
 
 
/******* Delete links related to drafts *******/
/* SQL query to count affected records */
select count(*) from LINKS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
/* SQL query to update affected records */
delete from LINKS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
 
 
/******* Delete labels relation to drafts *******/
/* SQL query to count affected records */
select count(*) from CONTENT_LABEL where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
/* SQL query to update affected records */
delete from CONTENT_LABEL where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');


/* SQL query to count affected records */
select count(*) from CONTENT_LABEL where CONTENTID in (select c.CONTENTID from CONTENT c join CONTENT_LABEL cl on cl.CONTENTID=c.CONTENTID where c.CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE'));

/* SQL query to update affected records */
delete from CONTENT_LABEL where CONTENTID in (SELECT * FROM (select c.CONTENTID from CONTENT c join CONTENT_LABEL cl on cl.CONTENTID=c.CONTENTID where c.CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE')) as t);

 
 
 
/******* Delete users relation to drafts *******/
/* SQL query to count affected records */
select count(*) from USERCONTENT_RELATION where TARGETCONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
/* SQL query to update affected records */
delete from USERCONTENT_RELATION where TARGETCONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
 
--- !! THIS TABLE DOES NOT EXIST AFTER CONFLUENCE 7.X !! ---
/******* Delete external links related to drafts *******/
/* SQL query to count affected records */
select count(*) from EXTRNLNKS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
/* SQL query to update affected records */
delete from EXTRNLNKS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
 
 
/******* Delete likes related to drafts *******/
/* SQL query to count affected records */
select count(*) from LIKES where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
/* SQL query to update affected records */
delete from LIKES where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
 
 
/******* Delete attachment properties related to drafts *******/
/* SQL query to count affected records */
select count(PROPERTYID) from CONTENTPROPERTIES where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
/* SQL query to update affected records */
delete from CONTENTPROPERTIES where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
 
 
/******* Delete image properties related to drafts *******/
/* SQL query to count affected records */
select count(*) from IMAGEDETAILS where ATTACHMENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
/* SQL query to update affected records */
delete from IMAGEDETAILS where ATTACHMENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
 
 
/******* Delete previous versions of attachments related to drafts *******/
/* SQL query to count affected records */
select count(a.CONTENTID) from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and a.PREVVER is not NULL and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';
 
/* SQL query to update affected records */
create temporary table TEMP_CONTENTID (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and a.PREVVER is not NULL and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE' order by a.CONTENTID);
delete from CONTENT where CONTENTID in (select CONTENTID from TEMP_CONTENTID);
drop temporary table TEMP_CONTENTID;
 
 
 
/******* Delete attachments related to drafts *******/
/* SQL query to count affected records */
select count(CONTENTID) from CONTENT where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
/* SQL query to update affected records */
create temporary table TEMP_CONTENTID (select CONTENTID from CONTENT where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE') order by CONTENTID);
delete from CONTENT where CONTENTID in (select CONTENTID from TEMP_CONTENTID);
drop temporary table TEMP_CONTENTID;
 
 
 
/******* Delete properties of comments related to drafts *******/
/* SQL query to count affected records */
select count(cp.PROPERTYID) from CONTENTPROPERTIES cp join CONTENT c on c.CONTENTID=cp.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='COMMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';
 
/* SQL query to update affected records */
create temporary table TEMP_PROPERTYID (select cp.PROPERTYID from CONTENTPROPERTIES cp join CONTENT c on c.CONTENTID=cp.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='COMMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE' order by cp.PROPERTYID);
delete from CONTENTPROPERTIES where PROPERTYID in (select PROPERTYID from TEMP_PROPERTYID);
drop temporary table TEMP_PROPERTYID;
 
 
 
/******* Delete body of comments related to drafts *******/
/* SQL query to count affected records */
select count(bc.BODYCONTENTID) from BODYCONTENT bc join CONTENT c on c.CONTENTID=bc.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='COMMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';
 
/* SQL query to update affected records */
create temporary table TEMP_BODYCONTENTID (select bc.BODYCONTENTID from BODYCONTENT bc join CONTENT c on c.CONTENTID=bc.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='COMMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE' order by bc.BODYCONTENTID);
delete from BODYCONTENT where BODYCONTENTID in (select BODYCONTENTID from TEMP_BODYCONTENTID);
drop temporary table TEMP_BODYCONTENTID;
 
 
 
/******* Delete any other contents related to drafts *******/
/* SQL query to count affected records */
select count(CONTENTID) from CONTENT where PAGEID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
/* SQL query to update affected records */
create temporary table TEMP_CONTENTID (select CONTENTID from CONTENT where PAGEID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE') order by CONTENTID);
SET FOREIGN_KEY_CHECKS=0;
delete from CONTENT where CONTENTID in (select CONTENTID from TEMP_CONTENTID);
SET FOREIGN_KEY_CHECKS=1;
drop temporary table TEMP_CONTENTID;
 
 
 
/******* Delete restrictions related to drafts – first query *******/
/* SQL query to count affected records */
select count(cp.ID) from CONTENT_PERM cp join CONTENT_PERM_SET cps on cps.ID=cp.CPS_ID join CONTENT d on d.CONTENTID=cps.CONTENT_ID where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';
 
/* SQL query to update affected records */
create temporary table TEMP_CONTENT_PERM_ID (select cp.ID from CONTENT_PERM cp join CONTENT_PERM_SET cps on cps.ID=cp.CPS_ID join CONTENT d on d.CONTENTID=cps.CONTENT_ID where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE' order by cp.ID);
delete from CONTENT_PERM where ID in (select ID from TEMP_CONTENT_PERM_ID);
drop temporary table TEMP_CONTENT_PERM_ID;
 
 
 
/******* Delete restrictions related to drafts – second query *******/
/* SQL query to count affected records */
select count(cps.ID) from CONTENT_PERM_SET cps join CONTENT d on d.CONTENTID=cps.CONTENT_ID where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';
 
/* SQL query to update affected records */
create temporary table TEMP_CONTENT_PERM_SET_ID (select cps.ID from CONTENT_PERM_SET cps join CONTENT d on d.CONTENTID=cps.CONTENT_ID where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE' order by cps.ID);
delete from CONTENT_PERM_SET where ID in (select ID from TEMP_CONTENT_PERM_SET_ID);
drop temporary table TEMP_CONTENT_PERM_SET_ID;
 
 
 
/******* Delete properties related to drafts *******/
/* SQL query to count affected records */
select count(*) from CONTENTPROPERTIES where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
/* SQL query to update affected records */
delete from CONTENTPROPERTIES where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');
 
 
 
/******* Delete contents that are drafts *******/
/* SQL query to count affected records */
select count(d.CONTENTID) from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';
 
/* SQL query to update affected records */
SET FOREIGN_KEY_CHECKS=0;
delete from CONTENT where CONTENT_STATUS = 'draft' and CONTENTTYPE = 'PAGE';
SET FOREIGN_KEY_CHECKS=1;
 
 
/******* Clear Synchrony IDs from the BANDANA table *******/
/* SQL query to update affected records */
delete from BANDANA where bandanakey = 'synchrony_collaborative_editor_app_registered'
         or bandanakey = 'synchrony_collaborative_editor_app_secret'
         or bandanakey = 'synchrony_collaborative_editor_app_id';
 
 
 
/******* Clear tables associated to Synchrony *******/
truncate table EVENTS;
truncate table SECRETS;
truncate table SNAPSHOTS;

------- Make child of drafts orphan pages -------
--- SQL query to count affected records ---
select count(c.CONTENTID) from CONTENT c join CONTENT d on d.CONTENTID=c.PARENTID where d.content_status='draft';

--- SQL query to update affected records ---
update CONTENT set PARENTID=null where CONTENTID in (select c.CONTENTID from CONTENT c join CONTENT d on d.CONTENTID=c.PARENTID where d.content_status='draft');



------- Delete notifications related to drafts -------
--- SQL query to count affected records ---
select count(*) from NOTIFICATIONS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from NOTIFICATIONS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete drafts that are child in CONFANCESTORS -------
--- SQL query to count affected records ---
select count(*) from CONFANCESTORS where DESCENDENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONFANCESTORS where DESCENDENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete drafts that are ancestors in CONFANCESTORS -------
--- SQL query to count affected records ---
select count(*) from CONFANCESTORS where ANCESTORID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONFANCESTORS where ANCESTORID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete body of drafts -------
--- SQL query to count affected records ---
select count(CONTENTID) from BODYCONTENT where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from BODYCONTENT where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete links related to drafts -------
--- SQL query to count affected records ---
select count(*) from LINKS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from LINKS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete labels relation to drafts -------
--- SQL query to count affected records ---
select count(*) from CONTENT_LABEL where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONTENT_LABEL where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to count affected records ---
select count(*) from CONTENT_LABEL where CONTENTID in (select c.CONTENTID from CONTENT c join CONTENT_LABEL cl on cl.CONTENTID=c.CONTENTID where c.CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE'));

--- SQL query to update affected records ---
delete from CONTENT_LABEL where CONTENTID in (select c.CONTENTID from CONTENT c join CONTENT_LABEL cl on cl.CONTENTID=c.CONTENTID where c.CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE'));



------- Delete users relation to drafts -------
--- SQL query to count affected records ---
select count(*) from USERCONTENT_RELATION where TARGETCONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from USERCONTENT_RELATION where TARGETCONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');


--- !! THIS TABLE DOES NOT EXIST AFTER CONFLUENCE 7.X !! ---
------- Delete external links related to drafts -------
--- SQL query to count affected records ---
select count(*) from EXTRNLNKS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from EXTRNLNKS where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete likes related to drafts -------
--- SQL query to count affected records ---
select count(*) from LIKES where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from LIKES where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete attachment properties related to drafts -------
--- SQL query to count affected records ---
select count(PROPERTYID) from CONTENTPROPERTIES where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONTENTPROPERTIES where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete image properties related to drafts -------
--- SQL query to count affected records ---
select count(*) from IMAGEDETAILS where ATTACHMENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from IMAGEDETAILS where ATTACHMENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete previous versions of attachments related to drafts -------
--- SQL query to count affected records ---
select count(a.CONTENTID) from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and a.PREVVER is not NULL and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from CONTENT where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and a.PREVVER is not NULL and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete attachments related to drafts -------
--- SQL query to count affected records ---
select count(CONTENTID) from CONTENT where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONTENT where CONTENTID in (select CONTENTID from CONTENT where CONTENTID in (select a.CONTENTID from CONTENT a join CONTENT d on d.CONTENTID=a.PAGEID where a.CONTENTTYPE='ATTACHMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE'));



------- Delete properties of comments related to drafts -------
--- SQL query to count affected records ---
select count(cp.PROPERTYID) from CONTENTPROPERTIES cp join CONTENT c on c.CONTENTID=cp.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='COMMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from CONTENTPROPERTIES where PROPERTYID in (select cp.PROPERTYID from CONTENTPROPERTIES cp join CONTENT c on c.CONTENTID=cp.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='COMMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete body of comments related to drafts -------
--- SQL query to count affected records ---
select count(bc.BODYCONTENTID) from BODYCONTENT bc join CONTENT c on c.CONTENTID=bc.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='COMMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from BODYCONTENT where BODYCONTENTID in (select bc.BODYCONTENTID from BODYCONTENT bc join CONTENT c on c.CONTENTID=bc.CONTENTID join CONTENT d on d.CONTENTID=c.PAGEID where c.CONTENTTYPE='COMMENT' and d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete any other contents related to drafts -------
--- SQL query to count affected records ---
select count(CONTENTID) from CONTENT where PAGEID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONTENT where CONTENTID in (select CONTENTID from CONTENT where PAGEID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE'));



------- Delete restrictions related to drafts – first query -------
--- SQL query to count affected records ---
select count(cp.ID) from CONTENT_PERM cp join CONTENT_PERM_SET cps on cps.ID=cp.CPS_ID join CONTENT d on d.CONTENTID=cps.CONTENT_ID where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from CONTENT_PERM where ID in (select cp.ID from CONTENT_PERM cp join CONTENT_PERM_SET cps on cps.ID=cp.CPS_ID join CONTENT d on d.CONTENTID=cps.CONTENT_ID where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete restrictions related to drafts – second query -------
--- SQL query to count affected records ---
select count(cps.ID) from CONTENT_PERM_SET cps join CONTENT d on d.CONTENTID=cps.CONTENT_ID where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from CONTENT_PERM_SET where ID in (select cps.ID from CONTENT_PERM_SET cps join CONTENT d on d.CONTENTID=cps.CONTENT_ID where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete properties related to drafts -------
--- SQL query to count affected records ---
select count(*) from CONTENTPROPERTIES where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');

--- SQL query to update affected records ---
delete from CONTENTPROPERTIES where CONTENTID in (select d.CONTENTID from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE');



------- Delete contents that are drafts -------
--- SQL query to count affected records ---
select count(d.CONTENTID) from CONTENT d where d.CONTENT_STATUS = 'draft' and d.CONTENTTYPE = 'PAGE';

--- SQL query to update affected records ---
delete from CONTENT where CONTENT_STATUS = 'draft' and CONTENTTYPE = 'PAGE';



------- Clear Synchrony IDs from the BANDANA table -------
--- SQL query to update affected records ---
delete from bandana where bandanakey = 'synchrony_collaborative_editor_app_registered'
         or bandanakey = 'synchrony_collaborative_editor_app_secret'
         or bandanakey = 'synchrony_collaborative_editor_app_id';



------- Clear tables associated to Synchrony -------
truncate table "EVENTS";
truncate table "SECRETS";
truncate table "SNAPSHOTS";


3. Start Confluence following your standard procedure.

4. Turn Collaborative Editing OFF.

5. Rebuilding the Ancestor Table

6. Turn Collaborative Editing On.


紐づく課題

Would like to see this as an enhancement in Confluence?

Please vote and comment on the following Jira Issue: CONFSERVER-35921 - Getting issue details... STATUS


説明 This page describes how to delete shared drafts from the database so Collaborative Editing can be enabled from scratch
製品Confluence



最終更新日: 2023 年 2 月 20 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.