cls_contact error after upgrade

Member since: 2007

Hi experts!

Getting this after CMS and Commerce update from 6r2 -> 7.0 -> 7.5 -> 7.19.2 (db production upgrade) ECF upgrade from r2 to 8.7

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.cls_Contact' and the index name 'UQ__cls_Cont__1788CC4D749A04ED'. The duplicate key value is ().
Could not create constraint. See previous errors.
Warning! The maximum key length is 900 bytes. The index 'UQ__cls_Cont__1788CC4D749A04ED' has maximum length of 1024 bytes. For some combination of large values, the insert/update operation will fail.
The statement has been terminated.

Any Idea where to start looking? can i delete any index? Change a length of something? (im no dba) It has something with the Commerce Contacts thats for sure.

Normaly i should at this step come to /episerver/commerce/migrate (I do have database version 7015)

#upgradeError? From db version 7006 i do run EPiUpdatePackage from nuget Export-EPiUpdates, And i get two "Found no files to process" <-- mening error? but end up with version 7015 (good).>

Also, worth mentioning, our membership provider for the commerce users accout (connected to Contacts) is pointing to EpiserverDB (but shouldn-t have to cause any problem)

The upgrade with DeploymentCenter is going smooth.

#meta We have 1500 contacts

Thanks for any help.

Feb 11, 2015 22:20
Member since: 2007

Also, everytime i reload the page it changes guid/no, eg: 'UQ__cls_Cont__1788CC4D7A52DE43' 'UQ__cls_Cont__1788CC4D7D2F4AEE'

Here is the whole stack:

[SqlException (0x80131904): The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.cls_Contact' and the index name 'UQ__cls_Cont__1788CC4D749A04ED'. The duplicate key value is ().
Could not create constraint. See previous errors.
Warning! The maximum key length is 900 bytes. The index 'UQ__cls_Cont__1788CC4D749A04ED' has maximum length of 1024 bytes. For some combination of large values, the insert/update operation will fail.
The statement has been terminated.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +388
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +815
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4515
   System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) +1387
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +533
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +287
   Mediachase.BusinessFoundation.Data.Sql.SqlHelper.ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters) +95
   Mediachase.BusinessFoundation.Data.Sql.SqlHelper.ExecuteNonQuery(SqlContext context, CommandType commandType, String commandText) +74
   Mediachase.BusinessFoundation.Data.Meta.Management.DefaultMetaFieldInstaller.CreateSqlRelation(MetaField field, Table srcTable, Column newColumn) +216
   Mediachase.BusinessFoundation.Data.Meta.Management.DefaultMetaFieldInstaller.AssignDataSource(MetaField field) +858
   Mediachase.BusinessFoundation.Data.Meta.Management.MetaClass.CreateMetaField(String Name, String FriendlyName, String TypeName, Boolean IsNullable, String DefaultValue, AttributeCollection Attributes) +793
   Mediachase.BusinessFoundation.Data.Meta.Management.MetaFieldBuilder.CreateText(String name, String friendlyName, Boolean isNullable, Int32 maxLength, Boolean isUnique) +352
   Mediachase.Commerce.Customers.Plugins.AutoInstallMetaDataModule.InstallUserId(String connectionString) +227
   Mediachase.Commerce.Customers.Plugins.AutoInstallMetaDataModule.Execute(BusinessContext context) +261
   Mediachase.BusinessFoundation.Data.Business.BusinessManager.ExecutePlugins(EventPipeLineStage eventPipeLineStage) +113
   Mediachase.BusinessFoundation.Data.Business.BusinessManager.Execute(Request request) +271
   Mediachase.Commerce.Customers.CustomerContext.InnerGetContactByUserId(String userId) +254
   Mediachase.Commerce.Customers.<>c__DisplayClass5b.<GetContactByUserId>b__5a() +20
   Mediachase.Commerce.Customers.CustomerContext.GetCachedValue(String key, TimeSpan timeout, Func`1 cachedValueGetter) +194
   Mediachase.Commerce.Customers.CustomerContext.GetContactByUserId(String userId) +375
   Mediachase.Commerce.Security.PrincipalExtensions.GetCustomerContact(IPrincipal principal) +247
   Mediachase.Commerce.Security.PrincipalExtensions.GetContactId(IPrincipal principal) +197
   Mediachase.Commerce.Core.Modules.BusinessFoundationInitializeModule.context_AuthorizeRequest(Object sender, EventArgs e) +31
   System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +165
Edited, Feb 11, 2015 22:24

Do you have existing rows in cls_Contact? Maybe the error is that the key the index is created for might have a duplicate value in the table. If that's the case, clearing the table should fix that.

Then you need to create a script that converts the old cls_Contact rows from the old table definition to the new one.


Feb 11, 2015 22:31
Member since: 2007

I'm trying to find out whats happening. When i cleared the table. the Mediachase code did update cls_contacts table with userid and password column. And put in the contactId column data in UserId. (i know this since i compare with an other database that is updated allready) Example data in UserId column: "string:{uniqGuidFromContactID}", or 'string:admin' or 'string:{username}' or un unknown guid.

I rather find out which row causes the duplicate UserID and remove that one before running the site since im missing out a lot of logic if clearing the tables cls_Contact and cls_Address

Feels like a bug in the update script.

Anyone knows what the new UserId and Password is ment to be used?


Feb 12, 2015 9:16

We had the exact same issue, deleted only the duplicated rows, then the migration step completed.


Feb 12, 2015 11:51
Member since: 2007

I'm trying to figure that out, thanks. But how? I have reflected the mediachase code. and it uses the membership.GetAllUsers() and then Mediachase.Commerce.Customers.MapUserKey.ToTypedString(membershipuser.Username) to create the userid string.

I guess i have to run all that to find any duplicates.

Feb 12, 2015 13:16

In the migration step all duplicates was specified for us, wasn't in your case? Then we specified a search query for the whole database for those users (only usernames was specified), then we deleted those users, both in some tables in the frontweb database, and in aspnet_users and cls_contact in CM db. And as well all relations to membership, usersinroles etc.

If you read the summary from where the migration step failed, you should see the duplicates usernames?


Feb 12, 2015 13:29
Member since: 2007

Thanks to the Episerver Support i did pass thru this error. The main difference was that i did have <Customers autoInstall="true"> in ecf.customer.config. Changed that to false and in appsettings too. Then i did get an error about UserId not existed.

So i first changed all duplicate emails (or delete them)

select * from [dbEpiCommerce].[dbo].[cls_Contact] where email in (SELECT email as antal
  FROM [dbEpiCommerce].[dbo].[cls_Contact]
GROUP BY email
HAVING (COUNT(*) > 1))
order by email

Since our commerce users account usernames are their emailaddress. we did create the UserId column, and added "string:emailaddress" as userid and applied the key.

ALTER TABLE cls_Contact ADD [UserId] nvarchar(512) DEFAULT NULL

update [dbo].[cls_Contact]
set [cls_Contact].UserId = 'String:' + convert(nvarchar(100), [cls_Contact].[Email])

ALTER TABLE cls_Contact ADD [Password] nvarchar(512) DEFAULT NULL	



After that i did get to the migration page. I seems that the cls_Contact and membership useraccount are now connected.

Feb 16, 2015 14:17
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.