Error staging files with attachments

jeff McDaniel asked on March 24, 2017 19:17

Hi,

I am seeing an issue staging from one of my sites. I can't push certain files to one of my sites. This occurs with files like .pdf, .jpeg and .pngs as well as pages that have images on them. The problem is with the database. It is trying to update the attachment history ID, but receives an error due to a foreign key constraint. This seems like a Kentico bug to me.

The error I am seeing is listed below.

SyncServer.ServerError: Exception occurred: [DataConnection.HandleError]: Query: INSERT INTO CMS_AttachmentHistory ([AttachmentName], [AttachmentExtension], [AttachmentSize], [AttachmentMimeType], [AttachmentBinary], [AttachmentImageWidth], [AttachmentImageHeight], [AttachmentDocumentID], [AttachmentGUID], [AttachmentIsUnsorted], [AttachmentOrder], [AttachmentGroupGUID], [AttachmentHash], [AttachmentTitle], [AttachmentDescription], [AttachmentCustomData], [AttachmentLastModified], [AttachmentHistoryGUID], [AttachmentSiteID], [AttachmentSearchContent], [AttachmentVariantDefinitionIdentifier], [AttachmentVariantParentID]) VALUES (@AttachmentName, @AttachmentExtension, @AttachmentSize, @AttachmentMimeType, @AttachmentBinary, @AttachmentImageWidth, @AttachmentImageHeight, @AttachmentDocumentID, @AttachmentGUID, @AttachmentIsUnsorted, @AttachmentOrder, @AttachmentGroupGUID, @AttachmentHash, @AttachmentTitle, @AttachmentDescription, @AttachmentCustomData, @AttachmentLastModified, @AttachmentHistoryGUID, @AttachmentSiteID, @AttachmentSearchContent, @AttachmentVariantDefinitionIdentifier, @AttachmentVariantParentID); SELECT SCOPE_IDENTITY() AS [ID] Caused exception: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CMS_AttachmentHistory_AttachmentSiteID_CMS_Site". The conflict occurred in database "KenticoCMS10", table "dbo.CMS_Site", column 'SiteID'. The statement has been terminated.

I've tried everything I can think of, deleting the page on both source and destination and trying again, destroying the page on source and destination and trying again, I've even tried to push the file to the a freshly created site and get the error every time. This only occurs on one site on my install which confuses me even more. Has anyone seem this error before? Or any advice would definitely be appreciated.

Thanks Jeff

Correct Answer

jeff McDaniel answered on April 3, 2017 19:19

Hey Anton and anyone else who may see this problem.

I have figured out a solution for this. It was probably something weird from the upgrade and doing the upgrade again was not an option. The solution to fix this staging error is below.

I have four environments (dev, test, staging, production) and this error was only seen when pushing files/attachments from staging -> prod.

The fix: I had to recreate a new site for each instance on the installation and then stage everything from the old site to the new site (forms, pages, roles ect)

So in the staging application I pushed everything from the dev -> new dev and repeated the process for test, staging, and production.

After pushing everything I switched the domain name settings and site names from the newly created sites to the old site's info and deleted the old site. This fixed my staging error and allowed me to retain all of my content on the sites.

Jeff

0 votesVote for this answer Unmark Correct answer

Recent Answers

Anton Grekhovodov answered on March 25, 2017 05:35

Hi Jeff,
I guess it's a duplicated question with this one SQL error when staging pdf file
Check what ids your sites have on local machine and staging. I guess they are different. Sync task tries to insert some data, which related on one siteid, in database where the site has another id, that's why error occures.
Did the error occure before upgrade?

0 votesVote for this answer Mark as a Correct answer

jeff McDaniel answered on March 27, 2017 17:30

Hey Anton,

Thanks for the response. I guess I forgot that I made this question before. Sorry for the duplicate.

As per your response, I did notice the site IDs were different. However, they were different before the upgrade as well. (sourceid = 6 destinationid = 7). In the same instance, the staging works correctly on sites that have different ids as well. (sourceid = 9 destinationid = 10, sourceid = 10 destination id = 14). It's just this one site that is having problems. I didn't see this before upgrading either, although I would think if it was an upgrade issue I'd be seeing it everywhere, which makes me think this is a site specific error.

Could it be possible that the siteID got messed up on just the attachments?

Thanks, Jeff

0 votesVote for this answer Mark as a Correct answer

Anton Grekhovodov answered on March 27, 2017 19:57

You're welcome)
Have you tried to sync attachments before?
It's really weird error. Also there is a section in the documentation about wrong relations between object after staging. They suggest to avoid custom fields with ids
Image Text But that field is not custom) Try to write a question to support, they have to have an answer:) Maybe it's just a bug

0 votesVote for this answer Mark as a Correct answer

jeff McDaniel answered on March 28, 2017 17:22

Hi Anton,

Yes I've been able to sync attachments before too. The weird thing is that this issue is occurring when I am trying to sync a .jpg image. It isn't an attachment on a page, its simple a file I'm trying to push.

Kentico support may be my best bet. I'll give this one more day of trying and then I'll see what they say.

Thanks for your help.

Jeff

0 votesVote for this answer Mark as a Correct answer

Anton Grekhovodov answered on April 4, 2017 17:30

Hi Jeff, Thanks for your reply, It is very useful information)

0 votesVote for this answer Mark as a Correct answer