Kentico 7->8 Upgrade failure

Craig Hazard asked on December 5, 2014 01:50

Hey guys,

I've been trying to update a legacy site from 6 -> 8.1
I manage to upgrade the site from 6 -> 7 and open up the site/admin without any issues.
When i attempt to go from 7 -> 8 I keep getting this error in the log file on DB upgrade.

DECLARE @resourceID int;
SET @resourceID = (SELECT [ResourceID] FROM [CMS_Resource] WHERE [ResourceGUID] = '07a7176d-b966-43dc-ae21-665599b92f7d')
DELETE FROM [CMS_ResourceSite]
    WHERE [ResourceID] = @resourceID

    WHERE [ResourceID] = @resourceID

Failed to run SQLscript: The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_UIElement_ElementResourceID_CMS_Resource". The conflict occurred in database "", table "dbo.CMS_UIElement", column 'ElementResourceID'.
The statement has been terminated.
Message: The DELETE statement conflicted with the REFERENCE constraint "FK_CMS_UIElement_ElementResourceID_CMS_Resource". The conflict occurred in database "", table "dbo.CMS_UIElement", column 'ElementResourceID'.
The statement has been terminated.
Stack Trace: 
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Hotfix.HotfixHelper.RunSQLScript(String fileName, String connString, String defaultUICulture, IMessageLog messageLog)

I have tried a few different times and had a look on the forums suggesting to run the site between updates, which I have done.
Still can't seem to get rid of this error, any ideas?

Recent Answers

Juraj Ondrus answered on December 6, 2014 11:32

Hi Craig,

Did you executed the manual upgrade or automatic via KIM?

It seems that there is an inconsistence in the DB. Could you please check the CMS_UIElement table and search for any records that have the same ElementResourceID - duplicates? If possible, I would recommend to remove the duplicates form the DB and then try to repeat the operation.

Also, were there added any custom elements in version 7? If so, please make sure they are marked as custom before upgrading. Otherwise the upgrade thinks they are the default ones and tries to upgrade them as well.
The DB should be OK though. There could be some obsolete UI elements and resources that were removed in Kentico 8.

You can run these two queries to get the old UI elements and then you can delete those elements - either through UI or in the DB directly (please, be sure you have actual backup of the DB ready - just in case).

Select *
From CMS_UIElement

Where ElementResourceID IN
SELECT [ResourceID]     
  FROM [CMS_Resource]
  WHERE ResourceGUID = '07a7176d-b966-43dc-ae21-665599b92f7d'


Select *
WHERE ElementIDPath LIKE '%/' + CONVERT (nvarchar, (Select ElementID FROM CMS_UIElement WHERE ElementGUID = 'fb9af80e-8d78-481e-97cb-b276a673b8ba')) + '/%'

Best regards,
Juraj Ondrus

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.