Sql script error when upgrading from 8.2 to 9

jeff McDaniel asked on February 6, 2017 20:03

Hi All,

We are in the process of upgrading from Kentico 8.2.48 to Kentico 9. Everything is going okay except for two statements at the end of the SQL script. The sql script is having a problem deleting some things from the CMS_Document table and CMS_attachment table due to some foreign key constraints. I didn't see anything in the upgrading to Kentico 9 documentation that had to do with deleting attachments manually, and the log file doesn't show the IDs that are having problems.

Has anyone had any problems upgrading from Kentico 8.2 to 9 like this before or have any suggestions? I've copied the errors below.

    ALTER TABLE [CMS_Tree] WITH CHECK ADD  CONSTRAINT [FK_CMS_Tree_NodeParentID_CMS_Tree] FOREIGN KEY([NodeParentID]) REFERENCES [CMS_Tree] ([NodeID])
    ALTER TABLE [CMS_Tree] CHECK CONSTRAINT [FK_CMS_Tree_NodeParentID_CMS_Tree]
    ALTER TABLE [CMS_Tree] DROP CONSTRAINT [FK_CMS_Tree_CMS_Tree]

    The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_Attachment_AttachmentDocumentID_CMS_Document". The conflict occurred in database "KenticoCMS8", table "dbo.CMS_Attachment", column 'AttachmentDocumentID'.
    The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_Document_DocumentNodeID_CMS_Tree". The conflict occurred in database "KenticoCMS8", table "dbo.CMS_Document", column 'DocumentNodeID'.
    The ALTER TABLE statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_CMS_Tree_NodeParentID_CMS_Tree". The conflict occurred in database "KenticoCMS8", table "dbo.CMS_Tree", column 'NodeID'.

    ALTER TABLE [CMS_Tree] WITH CHECK ADD  CONSTRAINT [FK_CMS_Tree_NodeParentID_CMS_Tree] FOREIGN KEY([NodeParentID]) REFERENCES [CMS_Tree] ([NodeID])
ALTER TABLE [CMS_Tree] CHECK CONSTRAINT [FK_CMS_Tree_NodeParentID_CMS_Tree]
ALTER TABLE [CMS_Tree] DROP CONSTRAINT [FK_CMS_Tree_CMS_Tree]

    The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_Attachment_AttachmentDocumentID_CMS_Document". The conflict occurred in database "KenticoCMS8", table "dbo.CMS_Attachment", column 'AttachmentDocumentID'.
The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_Document_DocumentNodeID_CMS_Tree". The conflict occurred in database "KenticoCMS8", table "dbo.CMS_Document", column 'DocumentNodeID'.
The ALTER TABLE statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_CMS_Tree_NodeParentID_CMS_Tree". The conflict occurred in database "KenticoCMS8", table "dbo.CMS_Tree", column 'NodeID'.

Any help would be greatly appreciated, anything really to point me in the right direction.

Thanks

Correct Answer

Zach Perry answered on February 6, 2017 22:05

This might help, it is a script I have used in the past for handling Orphaned documents:

BEGIN TRAN;  

--DROP TABLE #Orphans
SELECT ct.NodeID INTO #Orphans
FROM cms_tree ct
WHERE ct.NodeParentID NOT IN( 
                              SELECT ct.NodeID
                              FROM dbo.CMS_Tree ct )
  AND NodeAliasPath != '/';

--Abandon Orpans
DELETE FROM dbo.CMS_EventLog
WHERE dbo.CMS_EventLog.NodeID IN( SELECT *
                                  FROM #Orphans );
DELETE FROM dbo.CMS_DocumentAlias
WHERE dbo.CMS_DocumentAlias.AliasNodeID IN( SELECT *
                                            FROM #Orphans );
DELETE FROM dbo.CMS_Attachment
WHERE dbo.CMS_Attachment.AttachmentDocumentID IN( SELECT cd.DocumentID
                                                  FROM dbo.CMS_Document cd
                                                  WHERE cd.DocumentNodeID IN( 
                                                                              SELECT *
                                                                              FROM #Orphans o ));
DELETE FROM dbo.CMS_DocumentTag
WHERE dbo.CMS_DocumentTag.DocumentID IN( SELECT cd.DocumentID
                                         FROM dbo.CMS_Document cd
                                         WHERE cd.DocumentNodeID IN( 
                                                                     SELECT *
                                                                     FROM #Orphans ));
DELETE FROM dbo.Blog_Comment
WHERE dbo.Blog_Comment.CommentPostDocumentID IN( SELECT cd.DocumentID
                                                 FROM dbo.CMS_Document cd
                                                 WHERE cd.DocumentNodeID IN( 
                                                                             SELECT *
                                                                             FROM #Orphans ));
DELETE FROM dbo.CMS_Document
WHERE dbo.CMS_Document.DocumentNodeID IN( SELECT *
                                          FROM #Orphans );
DELETE FROM CMS_tree
WHERE CMS_tree.NodeID IN( SELECT *
                          FROM #Orphans );
COMMIT;
--ROLLBACK

1 votesVote for this answer Unmark Correct answer

Recent Answers

Brenden Kehren answered on February 6, 2017 20:54

Sounds like there may be some orphaned data or bad data in the attachments, documents or node tables. Couple things I'd look at are:

Do you have any custom code directly accessing the database? If so, most likely causing bad data problems.
Have you run any queries on the data to see if there are any parent/child relationship problems?

0 votesVote for this answer Mark as a Correct answer

jeff McDaniel answered on February 6, 2017 22:38

Thank you both for your responses! They both were a big help. Zachary your script worked perfectly and I was able to get through the upgrade without any problems. Out of curiosity, what causes orphans to happen in Kentico like that, specifically with document attachments?

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on February 7, 2017 15:37

@Jeff, if your v8.2.x instance was upgraded from a previous version like v6 or v7, there were issues there which were resolved in v8 and newer so I'm guessing that is what caused the problem. Also writing directly to the database or bad custom code can cause this too.

0 votesVote for this answer Mark as a Correct answer