Copied to clipboard

Flag this post as spam?

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


  • damian vander wilt 12 posts 56 karma points
    Feb 21, 2013 @ 22:05
    damian vander wilt
    0

    Need community consult for modified database constraints :: resolve Courier/Recycle Bin issues

    HI All!

    We've recently had quite a bit of trouble with getting Courier 2.7.3 working on an Umbraco 4.8.1 site.  We re getting lots of provider and other errors. Yesterday, before starting on figuring out how to fix Courier, i got frustrated with all the documents in the recycle bin - there were close to 1000.  Much like other forum posts - emptying the recyle bin wasn't working well  (sorry but i cannot find the related threads or remember exact error messages ATM) and the problem turned out to be ghost-documents in the database.

    When trying to delete these ghost nodes - i ran into a bunch of database constraints issues. -Curious, because the default-install constraints weren't quite enough to keep the data issue from happening in the first place, but it the constraints were preventing me from deleting the offenders to resolve the data integrity errors... hmm.

    So, i've gone through and updated our 4.8.1 database with the following constraints - all required to complete the aforementioned tasks. My recycle bin is now empty, i have no ghost documents, and the Courier errors that i alluded to have all been resolved as result of cleaning up these ghost-docs from the databse.  All the backoffice interaction i've had in the last 24 hours has been perfect and i have not noticed any issues creating/editing/deleting , but i want to run it through the community and hopefully some Core members (shannon?;) for consult if keeping these contraints may cause future issue that i haven't considered or am otherwise not aware of.

     

     

    umbracoNode fkconstraints
    ALTER TABLE [dbo].[umbracoNode]
       DROP CONSTRAINT [FK_umbracoNode_umbracoNode]   -- or whatever it's called
    
    ALTER TABLE [dbo].[umbracoNode]
       ADD CONSTRAINT [FK_umbracoNode_umbracoNode]
       FOREIGN KEY([parentID])
       REFERENCES [dbo].[umbracoNode] ([id]) ON DELETE CASCADE
    cmsContent fkconstraints
    ALTER TABLE [dbo].[cmsContentVersion]
       DROP CONSTRAINT FK_cmsContentVersion_cmsContent   -- or whatever it's called
    
    ALTER TABLE [dbo].[cmsContentVersion]
       ADD  CONSTRAINT FK_cmsContentVersion_cmsContent FOREIGN KEY([ContentId])
    REFERENCES [dbo].[cmsContent] ([nodeId])ON DELETE CASCADE

     

    update cmsPreviewXml constraints
    ALTER TABLE [dbo].[cmsPreviewXml]
     DROP CONSTRAINT [FK_cmsPreviewXml_cmsContentVersion]   -- or whatever it's called
    
    ALTER TABLE [dbo].[cmsPreviewXml]
       ADD  CONSTRAINT [FK_cmsPreviewXml_cmsContentVersion] FOREIGN KEY([versionId])
    REFERENCES [dbo].[cmsContentVersion] ([VersionId]) ON DELETE CASCADE
    update cmsContentXmlconstraints
    ALTER TABLE [dbo].[cmsContentXml]
     DROP CONSTRAINT [FK_cmsContentXml_cmsContent]   -- or whatever it's called
    
    ALTER TABLE [dbo].[cmsContentXml]
       ADD   CONSTRAINT [FK_cmsContentXml_cmsContent] FOREIGN KEY([nodeId])
    

     

     

    update cmsDocument constraints
    ALTER TABLE [dbo].[cmsDocument]
     DROP CONSTRAINT [FK_cmsDocument_cmsContent]   -- or whatever it's called
    
    ALTER TABLE [dbo].[cmsDocument]
       ADD   CONSTRAINT [FK_cmsDocument_cmsContent] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[cmsContent] ([nodeId]) ON DELETE CASCADE

     

     

    update cmsContent constraints
    ALTER TABLE [dbo].[cmsContent]
     DROP CONSTRAINT [FK_cmsContent_umbracoNode]   -- or whatever it's called
    
    ALTER TABLE [dbo].[cmsContent]
       ADD   CONSTRAINT [FK_cmsContent_umbracoNode] FOREIGN KEY([nodeId])
    REFERENCES [dbo].[umbracoNode] ([id]) ON DELETE CASCADE

     

     

    Hopefully the format is okay (happy to reformat and repost if need be). Please let me know any little or big consideration here that i might have missed. We have completed this in a test copy of a site, but i want to make sure that nobody raises any flags before we put this on the live Production site.

    Thanks tons!


Please Sign in or register to post replies

Write your reply to:

Draft