How to Remove a Page Manually in the Database Using SQL Commands

Still need help?

The Atlassian Community is here for you.

Ask the community

Scenario

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.

Resolution

  1. First, stop Confluence.
  2. Create a full database backup prior to running these queries against your database.
  3. Next, assuming that you're unable to delete the page from the UI, get the content ID of the page. 
    1. View the page.
    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
  4. Run the SQL commands below, in order, since the database contains constraints which 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); 
  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 Aug 4, 2022

Was this helpful?

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