cls_contact error after upgrade

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

#117116
Feb 11, 2015 22:20
GOSSO
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
#117118
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.

/T

#117119
Feb 11, 2015 22:31
GOSSO
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?

 

#117124
Feb 12, 2015 9:16
 

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

/J

#117145
Feb 12, 2015 11:51
GOSSO
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.

#117156
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?

/J

#117157
Feb 12, 2015 13:29
GOSSO
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	

ALTER TABLE cls_Contact ADD UNIQUE NONCLUSTERED ([UserId]) ON [PRIMARY]

IISRESET

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

#117275
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.