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 19 posts 141 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 19 posts 141 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 19 posts 141 karma points c-trib
    Jul 18, 2019 @ 16:01
    Jack Lawry
    101

    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!

  • Heather Floyd 522 posts 755 karma points MVP 2x c-trib
    Jul 02, 2020 @ 15:48
    Heather Floyd
    0

    Hi Jack,

    Thanks for sharing this code. I am working on a 7.4.3 upgrade to 7.15.5 and came across this error message.

    When I use your provided code, it returns 1,110 rows... but I'm not sure what I should now do about those rows...

    Should these rows be deleted? Edited in some way? Any further direction you could provide would be helpful.

    Thanks in advance :-)

  • Jack Lawry 19 posts 141 karma points c-trib
    Jul 02, 2020 @ 17:09
    Jack Lawry
    0

    Yes I just removed these items.

    (TAKE A FULL DB BACKUP FIRST)

    I just ran this:

    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
        delete 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)
    
  • Heather Floyd 522 posts 755 karma points MVP 2x c-trib
    Jul 02, 2020 @ 18:35
    Heather Floyd
    0

    Thanks, Jack :-)

    After running the delete I am still getting the errors. Using Ismail's code to look at the specific versionID mentioned in the Install error message, I am seeing other duplicated data...

    declare @alias varchar(30) = 'umbracoFile' 
    declare @nodeObjectType uniqueidentifier = N'B796F64C-1F99-4FFB-B886-4BF4BC011A9C'
    
    declare @versionToCheck uniqueidentifier = 'fed7eb58-71c2-452d-aa97-1b452818d62b' --**Update this to match Error msg GUID
    
    SELECT cmsPropertyData.id, cmsPropertyData.dataNvarchar, 
    cmsPropertyData.dataNtext, 
    umbracoNode.id, 
    cmsContentVersion.VersionId,cmsPropertyData.propertytypeid
    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=@versionToCheck
    

    Interestingly, I am getting duplicate data with different property ids...

    SQL Results

    All I can figure is that at some point the original developers changed the property type or something, but the old data is still present.

    I have been able to deal with them individually and in a rather manual fashion by grabbing the first "id" value and running

    DELETE FROM cmsPropertyData
    WHERE id = XXX;
    

    But, it's been pretty painful [sigh]...

  • Heather Floyd 522 posts 755 karma points MVP 2x c-trib
    Jul 02, 2020 @ 20:39
    Heather Floyd
    0

    Well, just in case someone else has a similar issue... I finally was able to get these "bulk" deleted by using Excel for help.

    First, I ran this query:

    declare @alias varchar(30) = 'umbracoFile' 
    declare @nodeObjectType uniqueidentifier = N'B796F64C-1F99-4FFB-B886-4BF4BC011A9C'
    
    SELECT cmsPropertyData.id, cmsPropertyData.dataNvarchar, 
    cmsPropertyData.dataNtext, 
    umbracoNode.id, 
    cmsContentVersion.VersionId,cmsPropertyData.propertytypeid
    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) 
    ORDER BY umbracoNode.id, 
    cmsContentVersion.VersionId, 
    cmsPropertyData.id
    

    Then, I took the results and pasted into Excel.

    I added a column to check if the row matched the next row (to get all the duplicates) with the cell formula:

    =AND(E2=E3,F2=F3)
    

    (Results in TRUE or FALSE on each row)

    And then another column to copy the 'id' of the TRUE rows:

    =IF(H2=TRUE,B2,"")
    

    I then copied the column and pasted it as VALUES, resulting in a full list of the IDs of rows which needed to be deleted. excel example The final list of IDs was then formatted into a comma-separated list and then this SQL was run, using the list of over 500 ids grabbed from excel:

    DELETE
    FROM cmsPropertyData
    WHERE id in (1962,1967,2008,2487,4409,4425,4433,...)
    

    I was finally able to get the Upgrade wizard to complete after this. :-)

Please Sign in or register to post replies

Write your reply to:

Draft