uSync is a tool in Umbraco which allows developers to create a file system version of their Umbraco database. Using source control teams of developers can quickly share database updates without swapping db backups back and forth. uSync does a fairly good job of it as well, but there are times when uSync hits an error and dies somewhat violently. Usually you can piece together which content sync is causing the error and go from there, but other times… well…
We can see there is something going on with the propertyData/propertyType foreign key constraint, but beyond that we do not know which content item is conflicting with uSync. We have a few options: A) delete all local content and import all, B) export from local to overwrite the conflict from source control, or C) guess until it works. None of those options sound too appealing, and someone is probably losing their content in the process (if anyone has another way to debug this kind of error please comment below)
The fourth option I came up with is to run the SQL profiler while uSync is running to pinpoint the exact SQL command that is throwing the error. To do this, open the SQL Server Profiler under the Tools dropdown and switch tabs to the Events Selector. Uncheck all of the pre-checked boxes and check the following:
Click Run to begin. Now return to the Umbraco backoffice and run the sync. When the error pops up again click the Pause button in the SQL profiler. Locate the red line which looks like this:
This will be the same error you saw in the usync window in the backoffice. Above this line is a trace log called “RPC:Starting”. This is the sql command that was executed when the error was thrown. Clicking this reveals some more information:
Now we have found the culprit! “delete from cmsPropertyType where contentTypeId = 5412”. Back to SQL Server, let’s run a select all from cmsPropertyType where contentTypeId=5412
That is the line that uSync is trying to delete, let’s look for the entry in cmsPropertyData that is blocking us from removing that line. Run a “select from cmdPropertyData where propertyTypeId = 414” and you will see several entries that match up. I forgot to take a screenshot at this point, but what I found in dataNvarchar was the json format to a grid editor that I had been using in previous versions of a content item. Delete these entries and run uSync again, this time it should work! (or hit another error)
So what is going on here? The page that broke was originally using a document type which had a grid editor, but due to changing requirements we needed to change document types to one which did not have one. Umbraco does not delete the propertyData entry because they are trying to maintain content versioning, which is still tied to the content item. When uSync attempted to sync my updates to the content item with what was in the file directory it does not delete the old versions in cmsPropertyData before attempting to delete the cmsPropertyType.