Brand ID Conflict in SKU Creation

SOS Childrensvillages asked on September 20, 2018 15:49

Hi, after upgrading to K11.033 a new table COM_Brand is causing issues when trying to update and publish a product page:

The debugged sql statement shows:

DECLARE @SKULastModified datetime2 = '9/20/2018 3:38:16 PM';
DECLARE @SKUBrandID int = 0;
DECLARE @SKUCollectionID int = 0;
DECLARE @SKUID int = 249;

UPDATE COM_SKU SET [SKULastModified] = @SKULastModified, [SKUBrandID] = @SKUBrandID, [SKUCollectionID] = @SKUCollectionID
WHERE [SKUID] = @SKUID
@SKULastModified (9/20/2018 3:38:16 PM)
@SKUBrandID (0)
@SKUCollectionID (0)
@SKUID (249)

I don't have any brands defined and I see the table allows null values so it shouldn't try to give it a value of 0. I tried creating a brand as a default fix but it's ID is 1.

Please let me know if you need any more info.

Also, same goes for SKUCollectionID ForeignKey.

In the meantime I have applied a dirty hack using SET IDENTITY_INSERT [COM_Collection/COM_Brand] ON; and creating rows with IDs of 0.

ecommerce kentico 11

Recent Answers

David te Kloese answered on September 21, 2018 08:59

Can you share the exact error / stack trace?

From what version did you upgrade to 11?

Did you check the Event Log for any errors during the upgrade?

Did you do the DB upgrade manually?

0 votesVote for this answer Mark as a Correct answer

SOS Childrensvillages answered on September 21, 2018 11:35 (last edited on September 21, 2018 11:36)

Hi David,

We upgraded from Kentico 10.0.27.

The DB was updated using the Kentico provided scripts.

The only event log errors during upgrade were related to the naming convention of an old cms form.

Stacktrace as follows:

An error occurred during the page action. Please see event log for more details.
Message: 
[DataConnection.HandleError]: 

Query: 
UPDATE COM_SKU SET [SKUNumber] = @SKUNumber, [SKUName] = @SKUName, [SKUPrice] = @SKUPrice, [SKUWeight] = @SKUWeight, [SKULastModified] = @SKULastModified, [SKUCreated] = @SKUCreated, [SKUBrandID] = @SKUBrandID, [SKUCollectionID] = @SKUCollectionID
WHERE [SKUID] = @SKUID

Caused exception: 
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_COM_SKU_SKUBrandID_COM_Brand". The conflict occurred in database "dbname", table "dbo.COM_Brand", column 'BrandID'.
The statement has been terminated.


Exception type: System.Exception
Stack trace: 
at CMS.DataEngine.AbstractDataConnection.HandleError(String queryText, Exception ex)
at CMS.DataEngine.AbstractDataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction)
at CMS.DataEngine.GeneralConnection.RunQuery(QueryParameters query)
at CMS.DataEngine.GeneralConnection.RunQueryWithRetry(QueryParameters query, Int32 retryCount)
at CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query, Int32& totalRecords)
at CMS.DataEngine.DataQueryBase`1.GetDataFromDBInternal()
at CMS.DataEngine.DataQueryBase`1.GetDataFromDB()
at CMS.DataEngine.DataQueryBase`1.GetData()
at CMS.DataEngine.SimpleDataClass.Update()
at CMS.DataEngine.AbstractInfoBase`1.UpdateData()
at CMS.DataEngine.AbstractInfoProvider`3.SetInfo(TInfo info)
at CMS.Ecommerce.SKUInfoProvider.SetSKUInfoInternal(SKUInfo skuObj)
at CMS.Ecommerce.SKUInfo.SetObject()
at CMS.Ecommerce.SKUTreeNode.SetSKU()
at CMS.Ecommerce.SKUTreeNode.UpdateTreeNodeData()
at CMS.DocumentEngine.TreeNode.UpdateInternal()
at CMS.DocumentEngine.TreeNode.Update(Boolean useDocumentHelper)
at CMS.DocumentEngine.VersionManager.PublishVersionInternal(Int32 versionHistoryId, Boolean checkPublish)
at CMS.DocumentEngine.WorkflowManager.MoveToStepInternal(TreeNode node, WorkflowStepInfo currentStep, WorkflowStepInfo step, String comment, Boolean increasePublishVersionNumber, WorkflowTransitionTypeEnum transitionType, Boolean handleActions)
at CMS.DocumentEngine.WorkflowManager.MoveToStepInternal(TreeNode node, WorkflowStepInfo currentStep, WorkflowStepInfo step, String comment, WorkflowTransitionTypeEnum transitionType)
at CMS.DocumentEngine.WorkflowManager.MoveToNextStepInternal(TreeNode node, String comment, WorkflowTransitionTypeEnum transitionType)
at CMS.DocumentEngine.WorkflowManager.MoveToNextStep(TreeNode node, String comment, WorkflowTransitionTypeEnum transitionType)
at CMS.FormEngine.Web.UI.CMSDocumentManager.ApproveDocument(Int32 stepId, String comment)

Message: The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_COM_SKU_SKUBrandID_COM_Brand". The conflict occurred in database "dbname", table "dbo.COM_Brand", column 'BrandID'.
The statement has been terminated.

Exception type: System.Data.SqlClient.SqlException
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.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at CMS.DataEngine.AbstractDataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction)

0 votesVote for this answer Mark as a Correct answer