mysql - 清除 MySQL 中的 Umbraco 历史记录

Question

将 umbraco 从 7.1 版更新到 7.2 版后，我开始在获取数据时遇到问题。我注意到我无法让 InternalIndexer 检查索引完成重建，我认为这可以解决我的问题。我读到拥有一个大数据库可能会导致这个问题，所以我正在尝试清理我的历史数据数据库。我找到了执行此操作的脚本，但它是为 SQL Server 编写的，我在将其转换为 MySQL 时遇到问题。

任何人都可以帮我转换这个脚本或者我可以使用不同的脚本吗？SQL Server 脚本是:

CREATE PROCEDURE [dbo].[DeleteHistory]

AS

BEGIN

Select VersionID into #tmp FROM cmsDocument

WHERE versionID NOT IN  (SELECT D.versionId FROM cmsDocument D 

WHERE D.versionId IN (SELECT versionId FROM (SELECT CV.versionId, published, newest, RANK() OVER(ORDER BY CV.versionDate DESC) RowNum
FROM cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId
WHERE DD.nodeId = D.nodeId) AS tmp
WHERE tmp.RowNum <= 3 OR tmp.published = 1 OR tmp.newest = 1)  )

DELETE FROM cmsPreviewXml WHERE VersionId IN (select #tmp.VersionId from #tmp)

DELETE FROM cmsContentVersion WHERE VersionId IN (select #tmp.VersionId from #tmp)

DELETE FROM cmsPropertyData WHERE VersionId IN (select #tmp.VersionId from #tmp)

DELETE FROM cmsDocument WHERE VersionId IN (select #tmp.VersionId from #tmp)

END

到目前为止，我所拥有的是:

CREATE TEMPORARY TABLE tmp

Select      VersionID FROM      cmsDocument  WHERE versionID NOT IN (   SELECT          D.versionId, D.NodeId   FROM        cmsDocument D   WHERE       D.versionId IN (            SELECT 
                versionId           FROM (
                SELECT 
                    CV.versionId, published, newest, @curRank := @curRank + 1 AS RowNum  
                FROM 
                    cmsContentVersion CV
                        JOIN cmsDocument DD ON CV.versionId = DD.versionId
                    , (SELECT @curRank := 0) r
                WHERE 
                    DD.nodeId = D.NodeId
                ORDER BY
                    CV.versionDate DESC
                ) AS tmp                WHERE
                tmp.RowNum <= 3 OR tmp.published = 1 OR tmp.newest = 1
        ) )

DELETE FROM cmsPreviewXml WHERE VersionId IN (select VersionId from tmp)

DELETE FROM cmsContentVersion WHERE VersionId IN (select VersionId from tmp)

DELETE FROM cmsPropertyData WHERE VersionId IN (select VersionId from tmp)

DELETE FROM cmsDocument WHERE VersionId IN (select VersionId from tmp)

DROP TEMPORARY TABLE tmp

希望大家能帮帮我。

谢谢

Answer 1

我遇到了同样的问题，我是这样解决的:

SET GLOBAL connect_timeout=28800;
SET GLOBAL wait_timeout=28800;
SET GLOBAL interactive_timeout=28800;
DROP TEMPORARY TABLE IF EXISTS tmp;
CREATE TEMPORARY TABLE tmp (
    Select versionId from (
        Select * From (
            SELECT CV.versionID , @curRank := @curRank + 1 AS RowNum
            FROM cmsContentVersion CV
            JOIN cmsDocument DD ON CV.versionId = DD.versionId, (SELECT @curRank := 0) r
            WHERE (Newest=0 and published=0) 
            ORDER BY DD.nodeId, CV.VersionDate DESC
            ) Old
        Where RowNum > 3
    ) Old2
);
SELECT Count(*) from tmp;
DELETE FROM cmsPreviewXml WHERE VersionId IN (select VersionId from tmp);
DELETE FROM cmsContentVersion WHERE VersionId IN (select VersionId from tmp);
DELETE FROM cmsPropertyData WHERE cmsPropertyData.VersionId IN (select VersionId from tmp);
DELETE FROM cmsDocument WHERE VersionId IN (select VersionId from tmp);
DELETE FROM umbracolog WHERE LOGHEADER='Error'

再见， 米歇尔