Confluence 6 删除垃圾内容

honeymoose · January 6, 2021, 2:18pm

属性（profile）垃圾

属性垃圾的定义为，一个垃圾用户在 Confluence 创建了用户，但是这个用户在自己的属性页面中添加了垃圾 URL。

如果你有很多垃圾用户在你的系统中创建了属性，你可以使用下面的 SQL 很容易的一次性删除。

如何删除属性中的垃圾：

停止 Confluence 实例然后备份好你的数据库。
备注： 这个步骤应该在你在数据库运行下面的 SQL 之前进行操作。
找到最后的真实属性：

 SELECT bodycontentid,body FROM bodycontent WHERE contentid IN
  (SELECT contentid FROM content WHERE contenttype='USERINFO')
  ORDER BY bodycontentid DESC;

查看属性页面中的内容，直到你找到垃圾用户的状态。你可能需要要求一系列的区间。
找到需要删除的集合：

CREATE TEMP TABLE killset AS SELECT bc.bodycontentid,c.contentid,c.username FROM
  bodycontent bc JOIN content c ON bc.contentid=c.contentid WHERE
  bodycontentid >= BOTTOM_OF_SPAM_RANGE AND bodycontentID <= TOP_OF_SPAM_RANGE
  AND  c.contenttype='USERINFO';
 
DELETE FROM bodycontent WHERE bodycontentid IN (SELECT bodycontentid FROM killset);
 
DELETE FROM links WHERE contentid IN (SELECT contentid FROM killset);
 
DELETE FROM content WHERE prevver IN (SELECT contentid FROM killset);
 
DELETE FROM content WHERE pageid IN (SELECT contentid FROM killset);
  
DELETE FROM content WHERE contentid IN (SELECT contentid FROM killset);
 
DELETE FROM os_user_group WHERE user_id IN (SELECT id FROM killset k JOIN os_user o ON o.username=k.username);
 
DELETE FROM os_user WHERE username IN (SELECT username FROM killset);

如果你使用的是 Confluence 5.6 或者早期的版本，请使用下面的 SQL 命令：

 CREATE TEMP TABLE killset AS SELECT bc.bodycontentid,c.contentid,c.username FROM
  bodycontent bc JOIN content c ON bc.contentid=c.contentid WHERE
  bodycontentid >= BOTTOM_OF_SPAM_RANGE AND bodycontentID <= TOP_OF_SPAM_RANGE
  AND  c.contenttype='USERINFO';
 
DELETE FROM bodycontent WHERE bodycontentid IN (SELECT bodycontentid FROM killset);
 
DELETE FROM links WHERE contentid IN (SELECT contentid FROM killset);
 
DELETE FROM content WHERE prevver IN (SELECT contentid FROM killset);
 
DELETE FROM attachments WHERE pageid IN (SELECT contentid FROM killset);
  
DELETE FROM content WHERE contentid IN (SELECT contentid FROM killset);
 
DELETE FROM os_user_group WHERE user_id IN (SELECT id FROM killset k JOIN os_user o ON o.username=k.username);
 
DELETE FROM os_user WHERE username IN (SELECT username FROM killset);

一旦垃圾内容被删除后，重启 Confluence 然后重构内容索引。这操作将会从索引查找对话框中删除任何相关的垃圾内容。