After a space import fails, it can't be re-imported
Related content
- No related content found
Summary
After a space import times out or fails, the space and its content can't be accessed or removed from the Space Admin page.
See: CONF-31528 - Getting issue details... STATUS
Resolution 1
The easiest way to delete a space whose import has failed is through the UI. We can remove it, even if it fails and doesn't show in the space directory, by using this url:
- <confluence base url>/spaces/removespace.action?key=<space key>
- Make sure to change the tags with the correct information.
- With this url, the removespace page for the respective space will be shown, by clicking OK, you will initiate the space removal.
This is only possible because, during the import, it starts by creating the "space" object. If you have a "space" object, you should be able to delete the space and everything related to it. As usual, make sure to create a database backup before doing so.
Resolution 2
The imported Space and related content can be removed manually from the Confluence database.
Before you proceed with the database updates, please note that:
- The queries provided below are for informational purposes, as this is an unsupported process.
- Make sure to read Unable to Delete Space first.
- After following these resolution steps, attachments for this space will be orphaned in the attachment directory. Attachment files related to the space can be removed manually from the file server disk by deleting the space sub-folder (as noted by the space ID in question) as well as all of its sub-folders. This ONLY applies for attachments stored under the old ver003 folder structure on Confluence versions prior to v8.1.0. From v8.1.0 onwards, Confluence uses version 4 storage layout format.
Prior to deleting the space ID folder and its subfolders, create a backup of these and store in a separate location. - For more information on how Confluence stores attachments on the file system under ver003, please see ver003 Hierarchical File System Attachment Storage.
While these queries were tested against MySQL 5.7, Postgres 9.5 and Microsoft SQL Server (2016), please bear in mind that these scripts may need minor adjustments before they work on other platforms.
As of Confluence 7.x, the EXTRNLNKS and TRACKBACKLINKS tables should no longer be present in the database as these have been deprecated. As such, any commands for these two tables can be skipped.
Always backup your data before performing any modifications to the database.
Delete Space database queries
Preparation:
Determine the space key and space ID of the space to be removed. This can be found by executing the following query:
SELECT SPACEID, SPACEKEY, SPACENAME FROM SPACES WHERE SPACENAME = '<enter-the-space-name>';- Stop Confluence before running these queries.
- Next, create a backup of the Confluence database.
MySQL Queries
/* Create a set of tables to process the deletion of space records */
DROP TABLE IF EXISTS DELETE_SPACE;
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;
DROP TABLE IF EXISTS DELETE_TEMPLATE_CURRENT;
DROP TABLE IF EXISTS DELETE_TEMPLATE_PREVVER;
DROP TABLE IF EXISTS DELETE_TEMPLATE_ALL;
/* Create temp table to hold space id and key */
CREATE TABLE DELETE_SPACE AS (SELECT SPACEID, SPACEKEY FROM SPACES WHERE SPACEKEY = 'YOUR_SPACEKEY_HERE');
/* Remove reference to the deleted space */
UPDATE SPACES SET HOMEPAGE=NULL, SPACEDESCID=NULL WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* Make first pass of the main content */
CREATE TABLE DELETE_CONTENT_PREP AS (SELECT CONTENTID FROM CONTENT C JOIN SPACES AS S ON S.SPACEID = C.SPACEID WHERE S.SPACEKEY IN (SELECT SPACEKEY FROM DELETE_SPACE));
/* 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);
/* Before performing the deletion statements below, check to see if there are any records associated with spaces other than the one being deleted, but which still have a reference to a record in the space being deleted. This has been documented to occur wehn importing spaces from the cloud in CONFCLOUD-64563. If records are returned from any of these queries, STOP further processing and share the results of these queries with support as these will need to be manually dealt with by either moving or deleting them via the UI if possible */
SELECT * FROM CONTENT WHERE PARENTID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PAGEID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PREVVER IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
/* 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 destspacekey IN (SELECT SPACEKEY FROM DELETE_SPACE);
DELETE FROM LINKS WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
/* Delete records from EXTRNLNKS */
DELETE FROM EXTRNLNKS 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 TRACKBACK LINKS */
DELETE FROM TRACKBACKLINKS WHERE CONTENTID 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 NOTIFICATIONS (space) */
DELETE FROM NOTIFICATIONS WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* Get currently active templates from PAGETEMPLATES */
CREATE TABLE DELETE_TEMPLATE_CURRENT AS (SELECT TEMPLATEID FROM PAGETEMPLATES WHERE SPACEID=(SELECT SPACEID FROM DELETE_SPACE));
/* Get previous versions of these templates, if any, from PAGETEMPLAES */
CREATE TABLE DELETE_TEMPLATE_PREVVER AS (SELECT TEMPLATEID FROM PAGETEMPLATES WHERE PREVVER IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT));
/* Combine these two sets of records */
CREATE TABLE DELETE_TEMPLATE_ALL AS (SELECT * FROM DELETE_TEMPLATE_CURRENT) UNION (SELECT * FROM DELETE_TEMPLATE_PREVVER);
/* Delete template related records from CONTENT_LABEL */
DELETE FROM CONTENT_LABEL WHERE PAGETEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_ALL);
/* Delete previous versions of PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_PREVVER);
/* Delete records from PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT);
/* Delete space's space permissions from SPACEPERMISSIONS */
DELETE FROM SPACEPERMISSIONS WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* 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 CONTENT_RELATION (particularly relevant if the imported space was from Cloud) */
DELETE FROM CONTENT_RELATION WHERE SOURCECONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
/* Delete records from COMMENTS */
DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_COMMENTS) AND PREVVER IS NOT NULL;
DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_COMMENTS) AND PARENTCOMMENTID IS NOT NULL ORDER BY CONTENTID DESC;
DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_COMMENTS) ORDER BY CONTENTID DESC;
/* Delete main records from CONTENT */
DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT) AND PREVVER IS NOT NULL;
DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT) AND PAGEID IS NOT NULL ORDER BY PAGEID DESC;
UPDATE CONTENT SET PARENTID = NULL WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT ORDER BY CONTENTID DESC) AND PARENTID IS NOT NULL;
DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT) ORDER BY CONTENTID DESC;
/* Delete records related to space from DECORATOR */
DELETE FROM DECORATOR WHERE SPACEKEY IN (SELECT SPACEKEY FROM DELETE_SPACE);
/* Delete the space from SPACES */
DELETE FROM SPACES WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* Delete record related to space from BANDANA */
DELETE FROM BANDANA WHERE BANDANACONTEXT IN (SELECT SPACEKEY FROM DELETE_SPACE);
Microsoft SQL Server Queries
/* DISCLAIMER: the commands below were created and tested with SQL Server 2016. If you're using older versions of SQL Server, these may need some adjustment */
/* Create a set of tables to process the deletion of space records */
DROP TABLE IF EXISTS DELETE_SPACE;
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;
DROP TABLE IF EXISTS DELETE_TEMPLATE_CURRENT;
DROP TABLE IF EXISTS DELETE_TEMPLATE_PREVVER;
DROP TABLE IF EXISTS DELETE_TEMPLATE_ALL;
/* Create temp table to hold space id and key */
SELECT SPACEID, SPACEKEY INTO DELETE_SPACE FROM SPACES WHERE SPACEKEY = 'YOUR_SPACEKEY_HERE';
/* Remove reference to the deleted space */
UPDATE SPACES SET HOMEPAGE=NULL, SPACEDESCID=NULL WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* Make first pass of the main content */
SELECT CONTENTID INTO DELETE_CONTENT_PREP FROM CONTENT C JOIN SPACES S ON S.SPACEID = C.SPACEID WHERE S.SPACEKEY IN (SELECT SPACEKEY FROM DELETE_SPACE);
/* Get the rest of the main content */
SELECT CONTENTID INTO DELETE_CONTENT
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 */
SELECT u.CONTENTID INTO DELETE_CONTENT_COMMENTS_PREP_1
FROM
(
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)
) u;
/* Get the previous versions of these comments */
SELECT u.CONTENTID INTO DELETE_CONTENT_COMMENTS_PREP_2
FROM
(
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)
) u;
/* Combine both sets of comments */
SELECT u.CONTENTID INTO DELETE_CONTENT_COMMENTS FROM (SELECT CONTENTID FROM DELETE_CONTENT_COMMENTS_PREP_1 UNION SELECT CONTENTID FROM DELETE_CONTENT_COMMENTS_PREP_2) u;
/* Combine both sets of content and comments. This resultset will be used to query against tables related to the content table */
SELECT u.CONTENTID INTO DELETE_CONTENT_ALL FROM (SELECT CONTENTID FROM DELETE_CONTENT UNION SELECT CONTENTID FROM DELETE_CONTENT_COMMENTS) u;
/* 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);
/* Before performing the deletion statements below, check to see if there are any records associated with spaces other than the one being deleted, but which still have a reference to a record in the space being deleted. This has been documented to occur wehn importing spaces from the cloud in CONFCLOUD-64563. If records are returned from any of these queries, STOP further processing and share the results of these queries with support as these will need to be manually dealt with by either moving or deleting them via the UI if possible */
SELECT * FROM CONTENT WHERE PARENTID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PAGEID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PREVVER IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
/* 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 DESTSPACEKEY IN (SELECT SPACEKEY FROM DELETE_SPACE);
DELETE FROM LINKS WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
/* Delete records from EXTRNLNKS */
DELETE FROM EXTRNLNKS 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 TRACKBACK LINKS */
DELETE FROM TRACKBACKLINKS WHERE CONTENTID 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 NOTIFICATIONS (space) */
DELETE FROM NOTIFICATIONS WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* Get currently active templates from PAGETEMPLATES */
SELECT TEMPLATEID INTO DELETE_TEMPLATE_CURRENT FROM PAGETEMPLATES WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* Get previous versions of these templates, if any, from PAGETEMPLAES */
SELECT TEMPLATEID INTO DELETE_TEMPLATE_PREVVER FROM PAGETEMPLATES WHERE PREVVER IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT);
/* Combine these two sets of records */
SELECT u.TEMPLATEID INTO DELETE_TEMPLATE_ALL FROM (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT UNION SELECT TEMPLATEID FROM DELETE_TEMPLATE_PREVVER) u;
/* Delete template related records from CONTENT_LABEL */
DELETE FROM CONTENT_LABEL WHERE PAGETEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_ALL);
/* Delete previous versions of PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_PREVVER);
/* Delete records from PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT);
/* Delete space's space permissions from SPACEPERMISSIONS */
DELETE FROM SPACEPERMISSIONS WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* 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 CONTENT_RELATION (particularly relevant if the imported space was from Cloud) */
DELETE FROM CONTENT_RELATION WHERE SOURCECONTENTID 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);
/* Delete records related to space from DECORATOR */
DELETE FROM DECORATOR WHERE SPACEKEY IN (SELECT SPACEKEY FROM DELETE_SPACE);
/* Delete the space from SPACES */
DELETE FROM SPACES WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* Delete record related to space from BANDANA */
DELETE FROM BANDANA WHERE BANDANACONTEXT IN (SELECT SPACEKEY FROM DELETE_SPACE);
Postgres Queries
/* Create a set of tables to process the deletion of space records */
DROP TABLE IF EXISTS DELETE_SPACE;
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;
DROP TABLE IF EXISTS DELETE_TEMPLATE_CURRENT;
DROP TABLE IF EXISTS DELETE_TEMPLATE_PREVVER;
DROP TABLE IF EXISTS DELETE_TEMPLATE_ALL;
/* Create temp table to hold space id and key */
CREATE TABLE DELETE_SPACE AS (SELECT SPACEID, SPACEKEY FROM SPACES WHERE SPACEKEY = 'YOUR_SPACEKEY_HERE');
/* Remove reference to the deleted space */
UPDATE SPACES SET HOMEPAGE=NULL, SPACEDESCID=NULL WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* Make first pass of the main content */
CREATE TABLE DELETE_CONTENT_PREP AS (SELECT CONTENTID FROM CONTENT C JOIN SPACES AS S ON S.SPACEID = C.SPACEID WHERE S.SPACEKEY IN (SELECT SPACEKEY FROM DELETE_SPACE));
/* 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)
UNION
SELECT CONTENTID FROM CONTENT WHERE PAGEID IN (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);
/* Before performing the deletion statements below, check to see if there are any records associated with spaces other than the one being deleted, but which still have a reference to a record in the space being deleted. This has been documented to occur wehn importing spaces from the cloud in CONFCLOUD-64563. If records are returned from any of these queries, STOP further processing and share the results of these queries with support as these will need to be manually dealt with by either moving or deleting them via the UI if possible */
SELECT * FROM CONTENT WHERE PARENTID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PAGEID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PREVVER IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
/* 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 destspacekey IN (SELECT SPACEKEY FROM DELETE_SPACE);
DELETE FROM LINKS WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
/* Delete records from EXTRNLNKS */
DELETE FROM EXTRNLNKS 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 TRACKBACK LINKS */
DELETE FROM TRACKBACKLINKS WHERE CONTENTID 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 NOTIFICATIONS (space) */
DELETE FROM NOTIFICATIONS WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* Get currently active templates from PAGETEMPLATES */
CREATE TABLE DELETE_TEMPLATE_CURRENT AS (SELECT TEMPLATEID FROM PAGETEMPLATES WHERE SPACEID=(SELECT SPACEID FROM DELETE_SPACE));
/* Get previous versions of these templates, if any, from PAGETEMPLAES */
CREATE TABLE DELETE_TEMPLATE_PREVVER AS (SELECT TEMPLATEID FROM PAGETEMPLATES WHERE PREVVER IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT));
/* Combine these two sets of records */
CREATE TABLE DELETE_TEMPLATE_ALL AS (SELECT * FROM DELETE_TEMPLATE_CURRENT UNION SELECT * FROM DELETE_TEMPLATE_PREVVER);
/* Delete template related records from CONTENT_LABEL */
DELETE FROM CONTENT_LABEL WHERE PAGETEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_ALL);
/* Delete previous versions of PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_PREVVER);
/* Delete records from PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT);
/* Delete space's space permissions from SPACEPERMISSIONS */
DELETE FROM SPACEPERMISSIONS WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* 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 CONTENT_RELATION (particularly relevant if the imported space was from Cloud) */
DELETE FROM CONTENT_RELATION WHERE SOURCECONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
DELETE FROM CONTENT_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);
/* Delete records related to space from DECORATOR */
DELETE FROM DECORATOR WHERE SPACEKEY IN (SELECT SPACEKEY FROM DELETE_SPACE);
/* Delete the space from SPACES */
DELETE FROM SPACES WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* Delete record related to space from BANDANA */
DELETE FROM BANDANA WHERE BANDANACONTEXT IN (SELECT SPACEKEY FROM DELETE_SPACE);
Oracle Queries
-- Run this SQL FIRST to generate the DROP TABLE SQL statements
SELECT owner, 'drop table ' || table_name || ';'
FROM all_tables where
table_name in ('DELETE_SPACE',
'DELETE_CONTENT_PREP',
'DELETE_CONTENT',
'DELETE_CONTENT_COMMENTS_PREP_1',
'DELETE_CONTENT_COMMENTS_PREP_2',
'DELETE_CONTENT_COMMENTS',
'DELETE_CONTENT_ALL',
'DELETE_TEMPLATE_CURRENT',
'DELETE_TEMPLATE_PREVVER',
'DELETE_TEMPLATE_ALL'
)
and owner = 'YOUR_ORACLE_DB_SCHEMA';
-- *************************************************************************************************
-- !! IMPORTANT - REPLACE **YOUR_ORACLE_DB_SCHEMA** above with your Oracle Confluence DB Schema Name
--
-- If any rows come back from the above, run the generated SQL to drop the temporary tables
-- If no rows came back, continue on :)
--
-- *************************************************************************************************
/* Create temp table to hold space id and key */
CREATE TABLE DELETE_SPACE AS (SELECT SPACEID, SPACEKEY FROM SPACES WHERE SPACEKEY = 'YOUR_SPACEKEY_HERE');
/* Remove reference to the deleted space */
UPDATE SPACES SET HOMEPAGE=NULL, SPACEDESCID=NULL WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* Make first pass of the main content */
CREATE TABLE DELETE_CONTENT_PREP AS (SELECT CONTENTID FROM CONTENT C JOIN SPACES S ON S.SPACEID = C.SPACEID WHERE S.SPACEKEY IN (SELECT SPACEKEY FROM DELETE_SPACE));
/* 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);
/* Before performing the deletion statements below, check to see if there are any records associated with spaces other than the one being deleted, but which still have a reference to a record in the space being deleted. This has been documented to occur wehn importing spaces from the cloud in CONFCLOUD-64563. If records are returned from any of these queries, STOP further processing and share the results of these queries with support as these will need to be manually dealt with by either moving or deleting them via the UI if possible */
SELECT * FROM CONTENT WHERE PARENTID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PAGEID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PREVVER IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
/* 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 destspacekey IN (SELECT SPACEKEY FROM DELETE_SPACE);
DELETE FROM LINKS WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
/* Delete records from EXTRNLNKS */
DELETE FROM EXTRNLNKS 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 TRACKBACK LINKS */
DELETE FROM TRACKBACKLINKS WHERE CONTENTID 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 NOTIFICATIONS (space) */
DELETE FROM NOTIFICATIONS WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* Get currently active templates from PAGETEMPLATES */
CREATE TABLE DELETE_TEMPLATE_CURRENT AS (SELECT TEMPLATEID FROM PAGETEMPLATES WHERE SPACEID=(SELECT SPACEID FROM DELETE_SPACE));
/* Get previous versions of these templates, if any, from PAGETEMPLAES */
CREATE TABLE DELETE_TEMPLATE_PREVVER AS (SELECT TEMPLATEID FROM PAGETEMPLATES WHERE PREVVER IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT));
/* Combine these two sets of records */
CREATE TABLE DELETE_TEMPLATE_ALL AS (SELECT * FROM DELETE_TEMPLATE_CURRENT UNION SELECT * FROM DELETE_TEMPLATE_PREVVER);
/* Delete template related records from CONTENT_LABEL */
DELETE FROM CONTENT_LABEL WHERE PAGETEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_ALL);
/* Delete previous versions of PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_PREVVER);
/* Delete records from PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT);
/* Delete space's space permissions from SPACEPERMISSIONS */
DELETE FROM SPACEPERMISSIONS WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* 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 CONTENT_RELATION (particularly relevant if the imported space was from Cloud) */
DELETE FROM CONTENT_RELATION WHERE SOURCECONTENTID 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);
/* Delete records related to space from DECORATOR */
DELETE FROM DECORATOR WHERE SPACEKEY IN (SELECT SPACEKEY FROM DELETE_SPACE);
/* Delete the space from SPACES */
DELETE FROM SPACES WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* Delete record related to space from BANDANA */
DELETE FROM BANDANA WHERE BANDANACONTEXT IN (SELECT SPACEKEY FROM DELETE_SPACE);
H2 Queries
/* Create a set of tables to process the deletion of space records */
DROP TABLE IF EXISTS DELETE_SPACE;
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;
DROP TABLE IF EXISTS DELETE_TEMPLATE_CURRENT;
DROP TABLE IF EXISTS DELETE_TEMPLATE_PREVVER;
DROP TABLE IF EXISTS DELETE_TEMPLATE_ALL;
/* Create temp table to hold space id and key */
CREATE TABLE DELETE_SPACE AS (SELECT SPACEID, SPACEKEY FROM SPACES WHERE SPACEKEY = 'YOUR_SPACEKEY_HERE');
/* Remove reference to the deleted space */
UPDATE SPACES SET HOMEPAGE=NULL, SPACEDESCID=NULL WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* Make first pass of the main content */
CREATE TABLE DELETE_CONTENT_PREP AS (SELECT CONTENTID FROM CONTENT C JOIN SPACES AS S ON S.SPACEID = C.SPACEID WHERE S.SPACEKEY IN (SELECT SPACEKEY FROM DELETE_SPACE));
/* 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 ALTER COLUMN CONTENTID SET NOT NULL;
ALTER TABLE DELETE_CONTENT ADD PRIMARY KEY (CONTENTID);
ALTER TABLE DELETE_CONTENT_COMMENTS ALTER COLUMN CONTENTID SET NOT NULL;
ALTER TABLE DELETE_CONTENT_COMMENTS ADD PRIMARY KEY (CONTENTID);
ALTER TABLE DELETE_CONTENT_ALL ALTER COLUMN CONTENTID SET NOT NULL;
ALTER TABLE DELETE_CONTENT_ALL ADD PRIMARY KEY (CONTENTID);
/* Before performing the deletion statements below, check to see if there are any records associated with spaces other than the one being deleted, but which still have a reference to a record in the space being deleted. This has been documented to occur wehn importing spaces from the cloud in CONFCLOUD-64563. If records are returned from any of these queries, STOP further processing and share the results of these queries with support as these will need to be manually dealt with by either moving or deleting them via the UI if possible */
SELECT * FROM CONTENT WHERE PARENTID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PAGEID IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
SELECT * FROM CONTENT WHERE PREVVER IN (SELECT * FROM DELETE_CONTENT) AND SPACEID NOT IN (SELECT SPACEID FROM DELETE_SPACE);
/* 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 destspacekey IN (SELECT SPACEKEY FROM DELETE_SPACE);
DELETE FROM LINKS WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT_ALL);
/* Delete records from EXTRNLNKS */
DELETE FROM EXTRNLNKS 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 TRACKBACK LINKS */
DELETE FROM TRACKBACKLINKS WHERE CONTENTID 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 NOTIFICATIONS (space) */
DELETE FROM NOTIFICATIONS WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* Get currently active templates from PAGETEMPLATES */
CREATE TABLE DELETE_TEMPLATE_CURRENT AS (SELECT TEMPLATEID FROM PAGETEMPLATES WHERE SPACEID=(SELECT SPACEID FROM DELETE_SPACE));
/* Get previous versions of these templates, if any, from PAGETEMPLAES */
CREATE TABLE DELETE_TEMPLATE_PREVVER AS (SELECT TEMPLATEID FROM PAGETEMPLATES WHERE PREVVER IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT));
/* Combine these two sets of records */
CREATE TABLE DELETE_TEMPLATE_ALL AS (SELECT * FROM DELETE_TEMPLATE_CURRENT UNION SELECT * FROM DELETE_TEMPLATE_PREVVER);
/* Delete template related records from CONTENT_LABEL */
DELETE FROM CONTENT_LABEL WHERE PAGETEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_ALL);
/* Delete previous versions of PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_PREVVER);
/* Delete records from PAGETEMPLATES */
DELETE FROM PAGETEMPLATES WHERE TEMPLATEID IN (SELECT TEMPLATEID FROM DELETE_TEMPLATE_CURRENT);
/* Delete space's space permissions from SPACEPERMISSIONS */
DELETE FROM SPACEPERMISSIONS WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* 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 CONTENT_RELATION (particularly relevant if the imported space was from Cloud) */
DELETE FROM CONTENT_RELATION WHERE SOURCECONTENTID 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);
/* Delete records related to space from DECORATOR */
DELETE FROM DECORATOR WHERE SPACEKEY IN (SELECT SPACEKEY FROM DELETE_SPACE);
/* Delete the space from SPACES */
DELETE FROM SPACES WHERE SPACEID IN (SELECT SPACEID FROM DELETE_SPACE);
/* Delete record related to space from BANDANA */
DELETE FROM BANDANA WHERE BANDANACONTEXT IN (SELECT SPACEKEY FROM DELETE_SPACE);
Confluence Cloud Queries
Note: Perform a Content Indexing prior to running the commands below.
Copy the script below, then do a find and replace for:
- YOUR_SPACEKEY_HERE = <your space key>
/* REMOVE REFERENCES IN SPACES TO CONTENT */
UPDATE SPACES SET homepage=NULL, spacedescid=NULL WHERE spaceid IN (select spaceid from SPACES where spacekey = 'YOUR_SPACEKEY_HERE');
/* CREATE A DELETE SET */
DROP TABLE IF EXISTS content_delete;
CREATE TABLE content_delete AS (SELECT contentid FROM content c JOIN SPACES s ON s.spaceid = c.spaceid WHERE s.spacekey = 'YOUR_SPACEKEY_HERE');
/* INSERT INTO THE DELETE SET, ALL CHILD PAGES */
INSERT INTO content_delete (SELECT contentid FROM content WHERE prevver IN(SELECT contentid FROM content c JOIN SPACES s ON s.spaceid = c.spaceid WHERE s.spacekey='YOUR_SPACEKEY_HERE') and contentid not in (select contentid from content_delete));
/* INSERT INTO THE DELETE SET, ALL COMMENTS (a few times to make sure we add them all)*/
INSERT INTO content_delete (SELECT contentid FROM content WHERE pageid IN (SELECT * FROM content_delete) and contentid not in (select contentid from content_delete));
INSERT INTO content_delete (SELECT contentid FROM content WHERE pageid IN (SELECT * FROM content_delete) and contentid not in (select contentid from content_delete));
INSERT INTO content_delete (SELECT contentid FROM content WHERE pageid IN (SELECT * FROM content_delete) and contentid not in (select contentid from content_delete));
INSERT INTO content_delete (SELECT contentid FROM content WHERE pageid IN (SELECT * FROM content_delete) and contentid not in (select contentid from content_delete));
INSERT INTO content_delete (SELECT contentid FROM content WHERE pageid IN (SELECT * FROM content_delete) and contentid not in (select contentid from content_delete));
INSERT INTO content_delete (SELECT contentid FROM content WHERE pageid IN (SELECT * FROM content_delete) and contentid not in (select contentid from content_delete));
INSERT INTO content_delete (SELECT contentid FROM content WHERE pageid IN (SELECT * FROM content_delete) and contentid not in (select contentid from content_delete));
/* INSERT INTO THE DELETE SET ALL VERSIONS OF ALL COMMENTS */
INSERT INTO content_delete (SELECT contentid FROM content WHERE contenttype ='COMMENT' AND prevver IN (SELECT * FROM content_delete) and contentid not in (select contentid from content_delete));
/* DELETE BODYCONTENT */
DELETE FROM bodycontent WHERE contentid IN (SELECT contentid FROM content_delete);
/* DELETE CONTENT_LABEL */
DELETE FROM content_label WHERE contentid IN (SELECT contentid FROM content_delete);
/* DELETE OS_PROPERTYENTRY */
DELETE FROM os_propertyentry WHERE entity_id IN (SELECT contentid FROM content_delete);
/* DELETE LINKS */
DELETE FROM LINKS WHERE destspacekey IN (SELECT SPACEKEY FROM DELETE_SPACE);
DELETE FROM links WHERE contentid IN (SELECT contentid FROM content_delete);
/* DELETE EXTRNLNKS */
DELETE FROM extrnlnks WHERE contentid IN (SELECT contentid FROM content_delete);
/* DELETE IMAGEDETAILS */
DELETE FROM imagedetails WHERE attachmentid IN (SELECT c.contentid FROM content c WHERE c.pageid IN (SELECT contentid FROM content_delete));
/* DELETE ATTACHMENT DATA */
DELETE FROM attachmentdata WHERE attachmentid IN (SELECT c.contentid FROM content c WHERE c.pageid IN (SELECT contentid FROM content_delete));
/* DELETE CONTENTPROPERTIES */
DELETE FROM contentproperties WHERE contentid IN (SELECT contentid FROM content_delete);
/* DELETE ATTACHMENTS */
DELETE FROM content WHERE contenttype = 'ATTACHMENT' AND pageid IN (SELECT contentid FROM content_delete);
/* DELETE CONTENT_PERM */
DELETE FROM content_perm WHERE cps_id IN (SELECT id FROM content_perm_SET WHERE content_id IN (SELECT contentid FROM content_delete));
/* DELETE CONTENT_PERM_SET */
DELETE FROM content_perm_SET WHERE content_id IN (SELECT contentid FROM content_delete);
/* DELETE TRACKBACK LINKS */
DELETE FROM trackbacklinks WHERE contentid IN (SELECT contentid FROM content_delete);
/* DELETE NOTIFICATIONS (CONTENT) */
DELETE FROM notifications WHERE contentid IN (SELECT contentid FROM content_delete);
/* DELETE NOTIFICATIONS (SPACE) */
DELETE FROM notifications WHERE spaceid IN (select spaceid from SPACES where spacekey = 'YOUR_SPACEKEY_HERE');
/* DELETE PREVIOUS VERSIONS OF PAGE TEMPLATES */
CREATE TABLE pagetemp_delete AS (SELECT templateid FROM pagetemplates WHERE spaceid=(select spaceid from SPACES where spacekey = 'YOUR_SPACEKEY_HERE'));
DELETE FROM pagetemplates WHERE prevver IN (SELECT templateid from pagetemp_delete);
/* DELETE PAGETEMPLATES */
DELETE FROM pagetemplates WHERE spaceid=(select spaceid from SPACES where spacekey = 'YOUR_SPACEKEY_HERE');
/*DELETE SPACEPERMISSIONS */
DELETE FROM spacepermissions WHERE spaceid=(select spaceid from SPACES where spacekey = 'YOUR_SPACEKEY_HERE');
/* DELETE LIKES */
DELETE FROM likes WHERE contentid IN (SELECT contentid FROM content_delete);
/* DELETE USER CONTENT RELATION */
DELETE from usercontent_relation where targetcontentid in (SELECT contentid FROM content_delete);
/* DELETE CONTENT */
DELETE FROM content WHERE contentid IN (SELECT contentid FROM content_delete);
/* DELETE ANY DECORATORS FOR THE SPACE */
DELETE FROM decorator where SPACEKEY='YOUR_SPACEKEY_HERE';
/* DELETE SPACE */
DELETE FROM SPACES WHERE spaceid=(select spaceid from SPACES where spacekey = 'YOUR_SPACEKEY_HERE');
/* DELETE REFERENCE FROM BANDANA */
DELETE FROM bandana WHERE bandanacontext='YOUR_SPACEKEY_HERE';A listing of all the deleted content is located in the content_delete table that has been created. You can drop these table when you're done with them by executing:
DROP TABLE content_delete;
DROP TABLE pagetemp_delete;Finally, you'll need to rebuild your index from scratch and rebuild the ancestor table.
Please note: Deleting records from the main CONTENT table sometimes results in an integrity constraint violation error. Since it's very difficult to pinpoint the exact record(s) causing the violation, one workaround would be to temporarily disable the constraint check referenced in the error message. Here are examples for how to do this:
MySQL
-- Disable single constraint at the session level
SET FOREIGN_KEY_CHECKS=0;
-- rerun the deletion command for the main CONTENT table in the script above...
DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT);
-- Enable single constraint
SET FOREIGN_KEY_CHECKS=1;
Microsoft SQL Server
-- Disable single constraint
ALTER TABLE content NOCHECK CONSTRAINT constraint_name;
-- rerun the deletion command for the main CONTENT table in the script above...
DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT);
-- Enable single constraint
ALTER TABLE content CHECK CONSTRAINT constraint_name;
Postgres
-- Disable single constraint at the session level
ALTER TABLE content DISABLE TRIGGER constraint_name;
-- rerun the deletion command for the main CONTENT table in the script above...
DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT);
-- Enable single constraint
ALTER TABLE content ENABLE TRIGGER constraint_name;
Oracle
-- Disable single constraint at the session level
ALTER TABLE content DISABLE CONSTRAINT constraint_name;
-- rerun the deletion command for the main CONTENT table in the script above...
DELETE FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM DELETE_CONTENT);
-- Enable single constraint
ALTER TABLE content ENABLE CONSTRAINT constraint_name;
Related Pages:
How to drop and recreate the database constraints on PostgreSQL.
Last modified on Nov 18, 2024
Related content
- No related content found
Powered by Confluence and Scroll Viewport.