SQL constraint violation when creating new block

Vote:
 

Sometimes when we try to create a new block we get the following message:

The INSERT statement conflicted with the FOREIGN KEY constraint 'FK_tblContent_tblContentType'. The conflict occurred in database 'dbCMS', table 'dbo.tblContentType', column 'pkID'.


At first i thought it was nothing to be worried about, but now that i have seen this message at least 10 times, at completely random moments, i thought i'd mention it on the forums.

Restarting the website in IIS fixes the problem (until the next time it appears).

I hope someone might shed some light on this one.

#70415
Apr 18, 2013 15:48
Vote:
 

I just added a block to a contentarea and got the following message:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblWorkContentProperty_tblPropertyDefinition". The conflict occurred in database "dbCMS", table "dbo.tblPropertyDefinition", column 'pkID'.

This seems somewhat related.

#70416
Apr 18, 2013 16:02
JN
Vote:
 

We seems to be getting this same error in our project. When we try to create a new block, and that specifick block type has some required fields. We get a: RequestError: Unable to load /EPi/UI/cms/Stores/contentdata/ status: 500

With the underlying error: 

[SqlException]: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblContent_tblContentType". The conflict occurred in database "[Snip]", table "dbo.tblContentType", column pkID.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

....

 

Restarting the website in IIS does not fix this problem for us though. Did you find any solution? (And we are running EPi 7.1 with all the latest updates)

 

/ Johan

#75559
Sep 30, 2013 10:55
Vote:
 

I haven't had this error in a while now, and i'm not sure what's causing it. However i do have a gut feeling about this one;

I think this error could orrur when multiple developers share the same episerver database, while they don't have exactly the same pagedata.
So, to get an idea, the following could happen:

  1. Developer A and B do a get latest version
  2. Developer A makes some pagetypes/blocktypes (but doesnt check in yet)
  3. Developer B makes some other pagetypes/blocktypes
  4. Both developers play around with their new pagetypes/blocktypes, effectively putting those pagetypes into the central episerver database
  5. Somewhere, some ID counter in cache gets bugged/out-of-sync, resulting in consecutive SQL constraint conflicts (Really no idea here, just guessing)

If i recall correctly, when this error happened to me, i did the following:

  1. Restart IIS / Restart website / Recycle application pool
  2. Clear browser cache
  3. Restart browser

If those steps won't help, maybe try and check-in all your pagetypes and make sure all developers are working with the same version again. Else i don't know...

Good luck!

#75639
Edited, Oct 02, 2013 10:30
Vote:
 

A quick peek in the database and I'm inclined to agree with Martijn's guess.  This only seems to occur with adding new properties, etc. Updates and deletes don't have this problem.  Although I haven't checked, I'd expect the same thing for the foreign keys in other parts of the system.  

Further to Martijn's suggestion, I'd recommend that only one developer adds new things to an object type at any time.  The error basically states that you're trying to add an item that already exists (or more specifically, the unique identity for your new item belongs to an existing one) which you're not allowed to do.  This can happen when you're in the process of creating a new object and the database had worked out the next logical ID number for your new object.  If someone else tries something similar (even if you're working on different content), you'll both be allocated the same new ID number and whoever commits first, gets that ID number while the other loses out and gets the FK violation error.

In short, only one person can add a content property to a content object at a time.  The same goes with similar conceptual objects.

Regarding JN's issue, sounds like something has gone awry and the ID generator didn't fire meaning the table thinks the next primary key ID is n rather than n+1 where n is the most recent primary key ID.  If this is the case, you'll need to re-seed the primary key to generate its next ID as n+1 where it *should* carry on fine.

The usual caveats apply.  Back up the database before trying any updates or tweaks.

#76192
Oct 18, 2013 17:51
Vote:
 

Well said Steve. Seems like a good workaround, not ideal, but life can't always be perfect :)

#76599
Oct 29, 2013 13:58
This thread is locked and should be used for reference only. Please use the Episerver CMS 7 and earlier versions forum to open new discussions.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.