As part of migrating our site to Umbraco V8, we needed to copy backoffice users from our staging database to the new V8 database. I did this via SQL, taking care to preserve the user IDs in umbracoUser table.
However, now when attempting to save any content item, I get the following SQL exception:
"System.Data.SqlClient.SqlException (0x80131904): The UPDATE statement conflicted with the FOREIGN KEY constraint \"FKumbracoNodeumbracoUser_id\". The conflict occurred in database \"Umbraco-Prod-Final\", table \"dbo.umbracoUser\", column 'id'.\r\nThe statement has been terminated.
As I understand, this error occurs when the PK ID changes, and no longer matches with the FK ID. However in my case the PK ID does exist in umbracoUser.
I tried changing the nodeUser ID in umbracoNode table to NULL, 0, and the ID of the logged-in user (which matches the ID in umbracoUser table). None of these made any difference.
I am assuming I did something incorrectly when copying the users to the new database.
Any ideas or suggestions of how to resolve this issue?
@Mehmet, thanks for the suggestion; yes, we did try creating a new user, that didn't work either.
The issue appeared to be a difference in identity ID of the system user between our two databases; it was 0 in one, and -1 in the other. Once we recreated our umbracoUsers data with the identity which matched all related tables (ie umbracoNode), then we were again able to save content.
I'm not able to mark my own post as the solution, but this was it.
Content save error after migrating users
As part of migrating our site to Umbraco V8, we needed to copy backoffice users from our staging database to the new V8 database. I did this via SQL, taking care to preserve the user IDs in umbracoUser table.
However, now when attempting to save any content item, I get the following SQL exception:
"System.Data.SqlClient.SqlException (0x80131904): The UPDATE statement conflicted with the FOREIGN KEY constraint \"FKumbracoNodeumbracoUser_id\". The conflict occurred in database \"Umbraco-Prod-Final\", table \"dbo.umbracoUser\", column 'id'.\r\nThe statement has been terminated.
As I understand, this error occurs when the PK ID changes, and no longer matches with the FK ID. However in my case the PK ID does exist in umbracoUser.
I tried changing the nodeUser ID in umbracoNode table to NULL, 0, and the ID of the logged-in user (which matches the ID in umbracoUser table). None of these made any difference.
I am assuming I did something incorrectly when copying the users to the new database.
Any ideas or suggestions of how to resolve this issue?
No replies or suggestions? Can anyone at least point out where in the Umbraco source code to find the SQL statement for updating content?
Hi Scott,
Just a wild idea, have you tried same with a new BO user, instead of using imported ones?
@Mehmet, thanks for the suggestion; yes, we did try creating a new user, that didn't work either.
The issue appeared to be a difference in identity ID of the system user between our two databases; it was 0 in one, and -1 in the other. Once we recreated our umbracoUsers data with the identity which matched all related tables (ie umbracoNode), then we were again able to save content.
I'm not able to mark my own post as the solution, but this was it.
Hi Scott,
-1 is actually a magic number there. That is the root user. If there is no user with id "-1" all crumbles down.
So actually you have found your problem there :)
Best,
Mehmet
is working on a reply...