How to Bulk Delete Blogposts of a Confluence Space Manually from Confluence Database Using SQL Commands
プラットフォームについて: 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 は除く
データベースの操作方法が不明な場合はデータベース管理者にお問い合わせください。先に進む前にデータベースの完全なバックアップを作成するようにしてください。これらの SQL コマンドはいくつかの環境でテストを行い、意図したとおりに動作することを確認しています。
しかしながら、特定の状況や Confluence の新しいバージョンでは動作しない場合があります。これは、Confluence のデータベース構造に変更が行われている可能性があるためです。このため、問題が発生し、過去に動作していた状態の Confluence データベースへの巻き戻しを行う必要がある場合に備えたデータベース バックアップが必須です。
目的
This KB outlines how to bulk delete blogposts in a Confluence Space manually from Confluence Database should it not be possible to delete them from Confluence UI itself, e.g. due to the large number of blogposts, the deletion process is then interrupted as it had reached the maximum threshold. shown in the log snippet below:
2016-08-09 16:13:50,579 WARN [Long running task: Space removal long running task] [confluence.util.profiling.DurationThresholdWarningTimingHelperFactory] logMessage Execution time for publishing event com.atlassian.confluence.event.events.content.blogpost.BlogPostRemoveEvent[source=com.atlassian.confluence.pages.DefaultPageManager@7ae889c] took 5711 ms (warning threshold is 5000 ms)
-- referer: <Confluence-Base-URL>/spaces/removespace.action?key=ConfSupport | url: /spaces/doremovespace.action | userName: admin | action: doremovespace | space: 18808910
2016-08-09 16:14:42,909 WARN [Long running task: Space removal long running task] [confluence.util.profiling.DurationThresholdWarningTimingHelperFactory] logMessage Execution time for publishing event com.atlassian.confluence.event.events.content.blogpost.BlogPostRemoveEvent[source=com.atlassian.confluence.pages.DefaultPageManager@7ae889c] took 5829 ms (warning threshold is 5000 ms)Before you proceed to the Resolution section, please do take note that this is an alternative method, should deleting blogposts from Confluence UI fails.
Please do try to first delete the blog posts through Confluence REST API content-delete function before then proceeding with the DB manipulation below.
ソリューション
データベースの変更を行う場合は 必ず事前にバックアップを取得してください。可能な場合はテスト サーバーで変更を試すことをおすすめします。
To delete blogposts of a specific Confluence Space in your Confluence instance, you may execute the following queries in your Confluence database.
SELECT spaceid
FROM SPACES
WHERE spacename = '<AffectedSpaceName>';CREATE TABLE IDTODELETE AS SELECT CONTENTID
FROM CONTENT WHERE contenttype = 'BLOGPOST'
AND spaceid = '<SpaceID>';
To delete the blogposts together all of its relevant data from the DB
DELETE FROM CONTENTPROPERTIES
WHERE CONTENTID IN(SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'ATTACHMENT' AND PAGEID IN (SELECT CONTENTID from IDTODELETE));DELETE FROM CONTENTPROPERTIES
WHERE CONTENTID IN(SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (SELECT CONTENTID from IDTODELETE));DELETE FROM IMAGEDETAILS WHERE ATTACHMENTID IN(SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'ATTACHMENT' AND PAGEID IN (SELECT CONTENTID from IDTODELETE));DELETE FROM LINKS
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);DELETE FROM CONTENT_LABEL
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);DELETE FROM EXTRNLNKS
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);DELETE FROM CONTENT
WHERE CONTENTTYPE = 'ATTACHMENT'
AND PAGEID IN (SELECT CONTENTID FROM IDTODELETE);DELETE FROM CONTENTPROPERTIES
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);DELETE FROM BODYCONTENT
WHERE CONTENTID in (SELECT CONTENTID FROM IDTODELETE);DELETE FROM BODYCONTENT
WHERE CONTENTID in (SELECT CONTENTID FROM CONTENT WHERE PREVVER IN(SELECT CONTENTID FROM IDTODELETE));DELETE FROM NOTIFICATIONS
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);DELETE FROM CONTENT
WHERE PREVVER IN (SELECT CONTENTID FROM IDTODELETE);DELETE FROM CONTENT
WHERE contenttype = 'BLOGPOST'
AND spaceid = '<SpaceID>';