SQL コマンドを使い、データベースでページを手動で削除する方法

お困りですか?

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

コミュニティに質問

シナリオ

These SQL commands provide an optional way to remove pages which are not working or causing instability within confluence.  Before using this approach, please be sure to try removing the page from confluence interface first .


If you're unsure with how to proceed with executing these commands, please contact your DBA.  Also, be sure to create a database back up before going further.

While these SQL commands have been tested to work as intended, there may be situations where a data corruption doesn't allow the commands to complete as expected due to foreign key constraints.  In this event, please record the results of the SQL commands and open a Support ticket.

Also, these commands may need adjustment for other DBMSs as they were built and tested with Postgres.

ソリューション

  1.  Get the content ID of the page. 
    1. 対象のページを表示します。
    2. Click on the ... > Page Information.
    3. The content ID will be listed at the end of the resulting URL string.  For example, https://yoursite.com/pages/viewinfo.action?pageId=12345
  2. Now, stop Confluence.
  3. Create a full database backup prior to running these queries against your database
  4. Run the SQL commands below, in order, since the database contains constraints that prevent foreign keys from being violated.  Replace the <content_id> with the actual content ID you identified earlier:

    /* Create a set of tables to process the deletion of the page and its related records. */
    DROP TABLE IF EXISTS DELETE_CONTENT_PREP;
    DROP TABLE IF EXISTS DELETE_CONTENT;
    DROP TABLE IF EXISTS DELETE_CONTENT_COMMENTS_PREP_1;
    DROP TABLE IF EXISTS DELETE_CONTENT_COMMENTS_PREP_2;
    DROP TABLE IF EXISTS DELETE_CONTENT_COMMENTS;
    DROP TABLE IF EXISTS DELETE_CONTENT_ALL;
    
    /* Make first pass of the main content */
    CREATE TABLE DELETE_CONTENT_PREP AS (SELECT CONTENTID FROM CONTENT C WHERE CONTENTID = <content_id>);
    
    /* Get the rest of the main content */
    CREATE TABLE DELETE_CONTENT AS 
    (SELECT CONTENTID FROM DELETE_CONTENT_PREP 
    UNION 
    SELECT CONTENTID FROM CONTENT WHERE PAGEID IN (SELECT CONTENTID FROM DELETE_CONTENT_PREP)
    UNION
    SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (SELECT CONTENTID FROM DELETE_CONTENT_PREP));
    
    /* Get the content related to comments and inline comments */
    CREATE TABLE DELETE_CONTENT_COMMENTS_PREP_1 AS 
    (SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE='COMMENT' AND PAGEID IN (SELECT CONTENTID FROM DELETE_CONTENT_PREP)
    UNION
    SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE='CUSTOM' AND PAGEID IN (SELECT CONTENTID FROM DELETE_CONTENT_PREP));
    
    /* Get the previous versions of these comments */
    CREATE TABLE DELETE_CONTENT_COMMENTS_PREP_2 AS 
    (SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (SELECT CONTENTID FROM DELETE_CONTENT_COMMENTS_PREP_1)
    UNION
    SELECT CONTENTID FROM CONTENT WHERE PAGEID IN (SELECT CONTENTID FROM DELETE_CONTENT_COMMENTS_PREP_1));
    
    /* Combine both sets of comments */
    CREATE TABLE DELETE_CONTENT_COMMENTS AS (SELECT * FROM DELETE_CONTENT_COMMENTS_PREP_1 UNION SELECT * FROM DELETE_CONTENT_COMMENTS_PREP_2);
    
    /* Combine both sets of content and comments. This resultset will be used to query against tables related to the content table */
    CREATE TABLE DELETE_CONTENT_ALL AS (SELECT * FROM DELETE_CONTENT UNION SELECT * FROM DELETE_CONTENT_COMMENTS);
    
    /* Add a primary key to prevent dupes */
    ALTER TABLE DELETE_CONTENT ADD PRIMARY KEY (CONTENTID);
    ALTER TABLE DELETE_CONTENT_COMMENTS ADD PRIMARY KEY (CONTENTID);
    ALTER TABLE DELETE_CONTENT_ALL ADD PRIMARY KEY (CONTENTID);
    
    /* Delete records from EXTRNLNKS  Note: this table has been dropped in newer versions of Confluence.  Skip, if this is the case. */
    DELETE FROM EXTRNLNKS WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
    
    /* Delete records from TRACKBACK LINKS  Note: this table has been dropped in newer versions of Confluence. Skip, if this is the case.  */
    DELETE FROM TRACKBACKLINKS WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
    
    /* Delete records from CONFANCESTORS */
    DELETE FROM CONFANCESTORS WHERE DESCENDENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL) OR ANCESTORID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
    
    /* Delete records from BODYCONTENT */
    DELETE FROM BODYCONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
    
    /* Delete records from CONTENT_LABEL */
    DELETE FROM CONTENT_LABEL WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
    
    /* Delete records from OS_PROPERTYENTRY */
    DELETE FROM OS_PROPERTYENTRY WHERE ENTITY_ID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
    
    /* Delete records from LINKS */
    DELETE FROM LINKS WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
    
    /* Delete records from IMAGEDETAILS  */
    DELETE FROM IMAGEDETAILS WHERE ATTACHMENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
     
    /* Delete records from ATTACHMENT DATA */
    DELETE FROM ATTACHMENTDATA WHERE ATTACHMENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
    
    /* Delete records from CONTENTPROPERTIES */
    DELETE FROM CONTENTPROPERTIES WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
    
    /* Delete records from CONTENT_PERM */
    DELETE FROM CONTENT_PERM WHERE CPS_ID IN (SELECT ID FROM CONTENT_PERM_SET WHERE CONTENT_ID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL));
    
    /* Delete records from CONTENT_PERM_SET */
    DELETE FROM CONTENT_PERM_SET WHERE CONTENT_ID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
    
    /* Delete records from NOTIFICATIONS (content) */
    DELETE FROM NOTIFICATIONS WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
    
    /* Delete records from LIKES */
    DELETE FROM LIKES WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
    
    /* Delete records from USERCONTENT_RELATION */
    DELETE FROM USERCONTENT_RELATION WHERE TARGETCONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
    
    /* Delete records from COMMENTS */
    DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_COMMENTS);
    
    /* Delete main records from CONTENT */
    DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT); 

    (info) In case you encounter the below error, while running the delete query on the content table, please ensure to first delete the child pages of the page that you are currently trying to delete and then proceed with the current page deletion

    DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT)
    Error report -
    ORA-02292: integrity constraint (XXX.FKOXTT893WEUJKYH0IICOXSM37V) violated - child record found
  5. Rebuild the indexes from scratch for server or Rebuild the indexes from scratch for DC
  6. Restart Confluence for the changes.





Last modified on Mar 21, 2024

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

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