Copied to clipboard

Flag this post as spam?

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


  • Jack Lawry 16 posts 137 karma points c-trib
    Jul 16, 2019 @ 14:28
    Jack Lawry
    0

    Upgrade from 7.7.13 fails due to media issues

    I am also getting the same error as This post

    Howecver I have added this as a new item as this other is marked as solved and my issue isn't.

    I have the issue when upgrading from 7.7.13 to anything higher with the error:

    Error during installation
    
    The database failed to upgrade. ERROR: The database configuration failed with the following message: Violation of PRIMARY KEY constraint 'PK_cmsMedia'. Cannot insert duplicate key in object 'dbo.cmsMedia'. The duplicate key value is (ef660e33-c44b-48cb-8f8a-00006c7ef608).
     The statement has been terminated. Please check log file for additional information (can be found in '/App_Data/Logs/UmbracoTraceLog.txt')
    
    See the log for full details (logs can typically be found in the App_Data\Logs folder).
    

    When trying to manually remove them using the SQL from Ismail

    declare @alias varchar(30) = 'umbracoFile' declare @nodeObjectType uniqueidentifier = N'B796F64C-1F99-4FFB-B886-4BF4BC011A9C'
    SELECT cmsPropertyData.dataNvarchar, cmsPropertyData.dataNtext, umbracoNode.id, cmsContentVersion.VersionId FROM cmsPropertyData INNER JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid INNER JOIN umbracoNode ON umbracoNode.id = cmsPropertyData.contentNodeId INNER JOIN cmsContentVersion ON cmsContentVersion.ContentId = umbracoNode.id WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.id IN (SELECT umbracoNode.id FROM umbracoNode INNER JOIN cmsContent ON cmsContent.nodeId = umbracoNode.id INNER JOIN cmsContentType ON cmsContentType.nodeId = cmsContent.contentType INNER JOIN cmsPropertyType ON cmsPropertyType.contentTypeId = cmsContentType.nodeId WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.nodeObjectType = (@nodeObjectType)) AND (cmsPropertyData.dataNvarchar IS NOT NULL OR cmsPropertyData.dataNtext IS NOT NULL) AND cmsContentVersion.VersionId='6ff5d8cb-02ee-4cbf-8d1f-0d979eeb1e5e'
    

    It appears to apply to lots of the images, I have been through the process and removed a few and it moves on to the next on the next upgrade... Is there any easy way to remove the duplicates without doing the upgrade, then waiting for it to fail and then getting the next guid to check... This site has well over 200,000 images so even if its 1% that's have this issue a lot to update.

    I wonder if this is due to their being multiple different media types and each image belongs to one media type but it appears to also belong to the main media type too?

  • Jack Lawry 16 posts 137 karma points c-trib
    Jul 17, 2019 @ 15:32
    Jack Lawry
    0

    If I run SQL

    DELETE FROM cmsPropertyData
    WHERE (propertytypeid = 6) OR
             (propertytypeid = 7) OR
             (propertytypeid = 8) OR
             (propertytypeid = 9) OR
             (propertytypeid = 10)
    

    It removes 1,034,040 rows and now the site will upgrade but the media folder is partly empty, the media is listed but there is just the option to upload an image...

    Anyone able to help with SQL query to only delete from 6, 7, 8, 9 & 10 where there is also another (non 6, 7, 8, 9 & 10) entry for that versionId ?

  • Jack Lawry 16 posts 137 karma points c-trib
    Jul 18, 2019 @ 16:01
    Jack Lawry
    100

    So If anyone has this same issue and finds this post this was the SQL that fixed the issue... Upgraded to 7.14.0 (as there are known issues with media and 7.15.0) and from a quick check all seems to have worked well.

    declare @alias varchar(30) = 'umbracoFile' declare @nodeObjectType uniqueidentifier = N'B796F64C-1F99-4FFB-B886-4BF4BC011A9C'
    --SELECT cmsPropertyData.dataNvarchar, cmsPropertyData.dataNtext, umbracoNode.id, cmsContentVersion.VersionId FROM cmsPropertyData INNER JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid INNER JOIN umbracoNode ON umbracoNode.id = cmsPropertyData.contentNodeId INNER JOIN cmsContentVersion ON cmsContentVersion.ContentId = umbracoNode.id WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.id IN (SELECT umbracoNode.id FROM umbracoNode INNER JOIN cmsContent ON cmsContent.nodeId = umbracoNode.id INNER JOIN cmsContentType ON cmsContentType.nodeId = cmsContent.contentType INNER JOIN cmsPropertyType ON cmsPropertyType.contentTypeId = cmsContentType.nodeId WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.nodeObjectType = (@nodeObjectType)) AND (cmsPropertyData.dataNvarchar IS NOT NULL OR cmsPropertyData.dataNtext IS NOT NULL)
    --SELECT cmsContentVersion.VersionId, count(1) FROM cmsPropertyData INNER JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid INNER JOIN umbracoNode ON umbracoNode.id = cmsPropertyData.contentNodeId INNER JOIN cmsContentVersion ON cmsContentVersion.ContentId = umbracoNode.id WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.id IN (SELECT umbracoNode.id FROM umbracoNode INNER JOIN cmsContent ON cmsContent.nodeId = umbracoNode.id INNER JOIN cmsContentType ON cmsContentType.nodeId = cmsContent.contentType INNER JOIN cmsPropertyType ON cmsPropertyType.contentTypeId = cmsContentType.nodeId WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.nodeObjectType = (@nodeObjectType)) AND (cmsPropertyData.dataNvarchar IS NOT NULL OR cmsPropertyData.dataNtext IS NOT NULL) group by cmsContentVersion.VersionId
    select * from cmsPropertyData where versionId in
    (select versionId from
    (select id, versionId, propertytypeid, case when propertytypeid <= 10 then -1 else 1 end as validPropType  from cmsPropertyData where versionid in 
    (SELECT cmsContentVersion.VersionId
    FROM cmsPropertyData INNER JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid 
    INNER JOIN umbracoNode ON umbracoNode.id = cmsPropertyData.contentNodeId 
    INNER JOIN cmsContentVersion ON cmsContentVersion.ContentId = umbracoNode.id 
    WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.id IN 
    (SELECT umbracoNode.id FROM umbracoNode 
    INNER JOIN cmsContent ON cmsContent.nodeId = umbracoNode.id 
    INNER JOIN cmsContentType ON cmsContentType.nodeId = cmsContent.contentType 
    INNER JOIN cmsPropertyType ON cmsPropertyType.contentTypeId = cmsContentType.nodeId 
    WHERE cmsPropertyType.Alias = (@alias) AND umbracoNode.nodeObjectType = (@nodeObjectType)) 
    AND (cmsPropertyData.dataNvarchar IS NOT NULL OR cmsPropertyData.dataNtext IS NOT NULL) 
    group by cmsContentVersion.VersionId having count(1) > 1))  t  group by versionId having sum(validproptype) = 0) and propertytypeid in (6,7,8,9,10)
    

    Huge thanks to Owain and Nik for your support and advice H5YR!

Please Sign in or register to post replies

Write your reply to:

Draft