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.

#117116 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

    #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
  • 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
  • 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
  • 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