Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • mmaty 74 posts 177 karma points
    Mar 26, 2019 @ 13:12
    mmaty
    0

    Delete old versions

    In Umbraco 7 we got used to delete old versions with some SQL statements. But these statements don't work anymore in Umbraco 8, since the database structure changed. I tried to use the DeleteVersions method of the ContentService, but it doesn't work:

        // Get all nodes with old versions
        static readonly string sqlForOldVersions = 
           @"SELECT distinct [nodeId] FROM[umbracoContentVersion] 
           where[current] <> 1";
        ...
        List<int> ids = new List<int>();
        using (var reader = handler.Execute( sqlForOldVersions, true ))
        while (reader.Read())
        {
            ids.Add( reader.GetInt32( 0 ) );
        }
    
        foreach (var id in ids)
        {
            this.contentService.DeleteVersions( id, dt );
        }
    

    It throws with the following error:

    The primary key value cannot be deleted because references to this key 
    still exist. [ Foreign key constraint name = 
      FK_umbracoDocumentVersion_umbracoContentVersion_id ]
    

    Does anybody have any insights regarding this issue?

  • Richard Soeteman 3650 posts 11166 karma points MVP
    Mar 26, 2019 @ 13:44
    Richard Soeteman
    0

    If the contentservice is throwing that error I assume it's a bug in V8.

  • mmaty 74 posts 177 karma points
    Mar 27, 2019 @ 10:41
    mmaty
    0

    There should definitely be a housekeeping functionality to delete old versions. The API only allows to delete old versions of a given content. But how do we determine, which contents have old versions?

    The SQL query in my sample code above is a quick hack.

    Why isn't there an API like

    DeleteAllContentVersionsBefore(DateTime dt)?
    

    This should have been part of Umbraco since the v4 days… ;-)

  • Remko 76 posts 196 karma points
    Aug 30, 2019 @ 15:06
    Remko
    0

    I stumbled upon this one as well.. Did you figure out how to fix this?

  • Steve Megson 120 posts 732 karma points c-trib
    Aug 30, 2019 @ 20:47
    Steve Megson
    2

    That does look like a bug in v8. That foreign key was missing before, so it didn't matter which order things were deleted. I think it might also need a tweak to how DeleteVersions decides which versions can be deleted.

    For deleting old versions of all content, this SQL should work for v8

    SELECT cv.id
    INTO #toDelete
    FROM umbracoDocumentVersion dv
    INNER JOIN umbracoContentVersion cv ON dv.id = cv.id
    WHERE cv.[current] != 1 AND dv.published != 1 AND cv.VersionDate < 'xxxx-xx-xx'
    
    DELETE FROM umbracoPropertyData WHERE versionId IN (select id from #toDelete)
    DELETE FROM umbracoDocumentVersion WHERE id IN (select id from #toDelete)
    DELETE FROM umbracoContentVersion WHERE id IN (select id from #toDelete)
    
    DROP TABLE #toDelete
    

    Adding a ContentService.DeleteAllVersionsBefore(DateTime dt) would be nice, though that SQL only works for SQL Server. We'd need some other solution for SQL CE.

  • Michael Argentini 20 posts 125 karma points
    Oct 10, 2019 @ 20:11
    Michael Argentini
    0

    This is great. I've been looking for any kind of help with compacting an Umbraco 8 database; removing old versions, pruning the log, etc. If you happen to have any other "clean up" scripts to share please do!

  • Patrick van Kemenade 8 posts 78 karma points
    30 days ago
    Patrick van Kemenade
    0

    Note if you have multiple language variations of your content the query above won't work because it doesn't cleanup table: [umbracoContentVersionCultureVariation]

    I'll let you know when I have a version which clean this as well.

  • Remko 76 posts 196 karma points
    30 days ago
    Remko
    0

    Already made that one:

    SELECT cv.id
    INTO #toDelete
    FROM umbracoDocumentVersion dv
    INNER JOIN umbracoContentVersion cv ON dv.id = cv.id
    WHERE cv.nodeId = @nodeId AND cv.[current] != 1 AND dv.published != 1 AND cv.VersionDate < @toDate
    
    DELETE FROM umbracoPropertyData WHERE versionId IN (select id from #toDelete)
    DELETE FROM umbracoDocumentVersion WHERE id IN (select id from #toDelete)
    DELETE FROM umbracoContentVersionCultureVariation WHERE versionId IN (select id from #toDelete)
    DELETE FROM umbracoContentVersion WHERE id IN (select id from #toDelete)
    
    DROP TABLE #toDelete
    
  • Patrick van Kemenade 8 posts 78 karma points
    30 days ago
    Patrick van Kemenade
    0

    Thanks Remco ! ..

    I've used your query (removed the "cv.nodeId = @nodeId AND" part) and it worked perfectly.

    I noticed when clicking on the Info it still reported the historical versions, it got this information from the Log. So adding this statement completely cleaned it.

    TRUNCATE TABLE umbracoLog

    P.S. disclaimer, it never hurts to create a backup before doing any of this....

  • Remko 76 posts 196 karma points
    30 days ago
    Remko
    0

    Ok, thanks.

    Think you'd better do:

    DELETE FROM [dbo].[umbracoLog] WHERE DateStamp < @toDate

    this then. Because the versions you remove are also selected by their date.

Please Sign in or register to post replies

Write your reply to:

Draft