Five New Optimizely Certifications are Here! Validate your expertise and advance your career with our latest certification exams. Click here to find out more

AI OnAI Off

Catalog Import throws "sql duplicate key error" although overwrite option is set as "true"

David Ortiz
David Ortiz
Vote:
 

When we call to Mediachase.Commerce.Catalog.ImportExport.Import method in order to import one catalog from an xml file , we are getting an Sql error regarding "Violation of Unique Key" but it shouldn't happen because we set the method param "overwrite" as true.

Error Number:2627,State:2,Class:14
System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'UQ__#B5DEE65__C86C24AB46D15906'. Cannot insert duplicate key in object 'dbo.@ContentProperty'. The duplicate key value is (101, 1, <NULL>, 17, en).
The data for table-valued parameter "@ContentProperty" doesn't conform to the table type of the parameter. SQL Server error is: 3602, state: 30

Full stacktrace:

 2019-10-31 12:21:11,173 INFO [1] EPiServer.Logging.Compatibility.LogManager+CompatibilityWrapper.Info  - Initialize EPiServer.Enterprise
 2019-10-31 12:21:19,871 WARN [347] EPiServer.Logging.Compatibility.LogManager+CompatibilityWrapper.Warn  - Could not load type 'Valtech.DotFoods.Commerce.Catalog.Implementation.Models.Variants.CustomProperties.NutritionFactPropertyProperty', falling back to base type instead
 2019-10-31 12:22:24,696 INFO [1] EPiServer.Logging.Compatibility.LogManager+CompatibilityWrapper.Info  - Initialization from PreInitialize to Initialized completed in 95,592.0 ms
 2019-10-31 12:22:27,828 ERROR [339] Valtech.Base.Core.Implementation.Manager.Log.LogManager.WriteError EslCatalogImport - Starting import...
 2019-10-31 12:22:27,844 ERROR [339] Valtech.Base.Core.Implementation.Manager.Log.LogManager.WriteError EslCatalogImport - Start importing catalog properties
 2019-10-31 12:22:29,271 ERROR [339] Valtech.Base.Core.Implementation.Manager.Log.LogManager.WriteError EslCatalogImport - Finished importing catalog properties
 2019-10-31 12:22:29,272 ERROR [339] Valtech.Base.Core.Implementation.Manager.Log.LogManager.WriteError EslCatalogImport - Importing catalog Products
 2019-10-31 12:22:29,278 ERROR [339] Valtech.Base.Core.Implementation.Manager.Log.LogManager.WriteError EslCatalogImport - Start importing catalog nodes
 2019-10-31 12:22:30,494 ERROR [339] Valtech.Base.Core.Implementation.Manager.Log.LogManager.WriteError EslCatalogImport - Error in ImportWithHandlers
System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'UQ__#B5DEE65__C86C24AB46D15906'. Cannot insert duplicate key in object 'dbo.@ContentProperty'. The duplicate key value is (101, 1, <NULL>, 17, en).
The data for table-valued parameter "@ContentProperty" doesn't conform to the table type of the parameter. SQL Server error is: 3602, state: 30
The statement has been terminated.
   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.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   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.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
   at Mediachase.Data.Provider.SqlDataProvider.ExecuteNonExec(DataCommand command)
   at Mediachase.MetaDataPlus.Common.DBHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, Int32 commandTimeout, DataParameter[] commandParameters)
   at Mediachase.MetaDataPlus.Configurator.MetaObjectDB.UpdateBatchOfMetaObject(MetaDataContext context, IDictionary`2 metaObjectsByLanguage)
   at Mediachase.MetaDataPlus.Internal.CatalogMetaObjectRepository.UpdateBatch(IDictionary`2 metaObjectsByLanguage)
   at Mediachase.Commerce.Catalog.ImportExport.Import.MetaDataReader.AcceptChanges(Dictionary`2 metaObjects)
   at Mediachase.Commerce.Catalog.ImportExport.Import.NodesReader.SaveNodes(ImportCatalogNodeCache relationCache, CatalogNodeDto workingDto, Dictionary`2 metaObjects)
   at Mediachase.Commerce.Catalog.ImportExport.Import.NodesReader.Read(ImportCatalogNodeCache relationCache)
   at Mediachase.Commerce.Catalog.ImportExport.Import.Importer.Import(Stream stream, String sourceDirectory, Boolean overwrite)
   at Valtech.DotFoods.Commerce.Catalog.Implementation.Manager.CatalogImport.CatalogImportManager.CatalogImport(Stream catalogMemoryStream) in C:\Workspace\Dotfoods\Episerver\Valtech.DotFoods.Commerce.Catalog.Implementation\Manager\CatalogImport\CatalogImportManager.cs:line 61
ClientConnectionId:8c136d02-58f5-40e6-82e6-c3dee7279c33
Error Number:2627,State:2,Class:14
 System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'UQ__#B5DEE65__C86C24AB46D15906'. Cannot insert duplicate key in object 'dbo.@ContentProperty'. The duplicate key value is (101, 1, <NULL>, 17, en).
The data for table-valued parameter "@ContentProperty" doesn't conform to the table type of the parameter. SQL Server error is: 3602, state: 30
The statement has been terminated.
   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.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   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.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
   at Mediachase.Data.Provider.SqlDataProvider.ExecuteNonExec(DataCommand command)
   at Mediachase.MetaDataPlus.Common.DBHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, Int32 commandTimeout, DataParameter[] commandParameters)
   at Mediachase.MetaDataPlus.Configurator.MetaObjectDB.UpdateBatchOfMetaObject(MetaDataContext context, IDictionary`2 metaObjectsByLanguage)
   at Mediachase.MetaDataPlus.Internal.CatalogMetaObjectRepository.UpdateBatch(IDictionary`2 metaObjectsByLanguage)
   at Mediachase.Commerce.Catalog.ImportExport.Import.MetaDataReader.AcceptChanges(Dictionary`2 metaObjects)
   at Mediachase.Commerce.Catalog.ImportExport.Import.NodesReader.SaveNodes(ImportCatalogNodeCache relationCache, CatalogNodeDto workingDto, Dictionary`2 metaObjects)
   at Mediachase.Commerce.Catalog.ImportExport.Import.NodesReader.Read(ImportCatalogNodeCache relationCache)
   at Mediachase.Commerce.Catalog.ImportExport.Import.Importer.Import(Stream stream, String sourceDirectory, Boolean overwrite)
   at Valtech.DotFoods.Commerce.Catalog.Implementation.Manager.CatalogImport.CatalogImportManager.CatalogImport(Stream catalogMemoryStream) in C:\Workspace\Dotfoods\Episerver\Valtech.DotFoods.Commerce.Catalog.Implementation\Manager\CatalogImport\CatalogImportManager.cs:line 61
ClientConnectionId:8c136d02-58f5-40e6-82e6-c3dee7279c33

Any comment or direction will be appreciated. 

#208766
Edited, Oct 31, 2019 18:14
Praful Jangid
Praful Jangid
Vote:
 

It sounds like a bug, please contact to support.

#208778
Nov 01, 2019 9:01
Quan Mai
Quan Mai
Vote:
 

It would be more helpful it you can provide a full stacktrace. It sounds like you have some anomaly in your catalog data...

#208918
Nov 04, 2019 22:34
David Ortiz - Nov 05, 2019 13:24
Thanks Quan! I've added the full stracktrace. 

I see there a warn regarding a variant custom property that can't be loaded. Do you think that it could be the error reason?  
Sujit Senapati
Sujit Senapati
Vote:
 

We had the same issue. It looks like the issue was a result of the order of <Guid> and <Languages> node under catalog xml. It should be first and second child of <Catalog> respectively. The reason this error occurs is because how Mediachase code reads the xml stream. The import process expects <Guid> value to be first child of catalog and if it does not find in first child it assigns a new Guid and fails to process the catalog file as it would try to add a new catalog instead of overriding it. Moving the node orders solved the issues for us.

~ Sujit

#268069
Dec 08, 2021 17:40
David Ortiz - Feb 07, 2022 15:21
Thanks Sujit !! Would be awesome work with you some day! :P ..
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* 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.