Catalog Import throws "sql duplicate key error" although overwrit

volume_up

A critical vulnerability was discovered in React Server Components (Next.js). Our systems remain protected but we advise to update packages to newest version. Learn More.

Home / Forums / Developers (Main Products) Forum / Customized Commerce 13 and earlier versions /

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

David Ortiz

Vote:

expand_less 0 expand_more

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

Vote:

expand_less 0 expand_more

It sounds like a bug, please contact to support.

#208778

Nov 01, 2019 9:01

Quan Mai

Vote:

expand_less 0 expand_more

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

- 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

Vote:

expand_less 0 expand_more

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

- Feb 07, 2022 15:21

Thanks Sujit !! Would be awesome work with you some day! :P ..

error 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.