I've tried upgrading via NuGet to 7.7.4 (also tried 7.6.5), but I receive the same error with both attempts.
The database failed to upgrade. ERROR: The database configuration failed with the following message: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.umbracoRelationType' and the index name 'IXumbracoRelationTypeUniqueId'. The duplicate key value is (23d1ac88-a2a5-3b05-b034-224a14cbbaf6).
The statement has been terminated. Please check log file for additional information (can be found in '/App_Data/Logs/UmbracoTraceLog.txt')
Here is additional info from the mentioned log...
2017-10-26 11:57:14,027 [P35384/D10/T6] INFO Umbraco.Core.Persistence.Migrations.MigrationRunner - Executing sql statement 10: CREATE UNIQUE NONCLUSTERED INDEX [IXumbracoRelationTypeUniqueId] ON [umbracoRelationType] ([typeUniqueId])
2017-10-26 11:57:14,031 [P35384/D10/T6] ERROR Umbraco.Core.Persistence.UmbracoDatabase - Exception (69f27867).
System.Data.SqlClient.SqlException (0x80131904): The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.umbracoRelationType' and the index name 'IXumbracoRelationTypeUniqueId'. The duplicate key value is (23d1ac88-a2a5-3b05-b034-224a14cbbaf6).
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() in c:\Code\github\SamSaffron\MiniProfiler\StackExchange.Profiling\Data\ProfiledDbCommand.cs:line 266
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass1.<ExecuteNonQueryWithRetry>b__0()
at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func)
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy)
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy retryPolicy)
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command)
at Umbraco.Core.Persistence.Database.Execute(String sql, Object[] args)
ClientConnectionId:9b192688-af2e-4d21-a4d6-e6099638e431
Error Number:1505,State:1,Class:16
2017-10-26 11:57:14,038 [P35384/D10/T6] ERROR Umbraco.Core.DatabaseContext - Database configuration failed
System.Data.SqlClient.SqlException (0x80131904): The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.umbracoRelationType' and the index name 'IXumbracoRelationTypeUniqueId'. The duplicate key value is (23d1ac88-a2a5-3b05-b034-224a14cbbaf6).
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() in c:\Code\github\SamSaffron\MiniProfiler\StackExchange.Profiling\Data\ProfiledDbCommand.cs:line 266
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass1.<ExecuteNonQueryWithRetry>b__0()
at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func)
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy)
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy retryPolicy)
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command)
at Umbraco.Core.Persistence.Database.Execute(String sql, Object[] args)
at Umbraco.Core.Persistence.Migrations.MigrationRunner.ExecuteMigrations(IMigrationContext context, Database database)
at Umbraco.Core.Persistence.Migrations.MigrationRunner.Execute(Database database, DatabaseProviders databaseProvider, Boolean isUpgrade)
at Umbraco.Core.Persistence.Migrations.MigrationRunner.Execute(Database database, Boolean isUpgrade)
at Umbraco.Core.DatabaseContext.UpgradeSchemaAndData(IMigrationEntryService migrationEntryService)
ClientConnectionId:9b192688-af2e-4d21-a4d6-e6099638e431
Error Number:1505,State:1,Class:16
2017-10-26 11:57:14,042 [P35384/D10/T6] INFO Umbraco.Core.DatabaseContext - The database schema validation produced the following summary:
The following tables were found in the database, but are not in the current schema:
umbracoDeployChecksum,umbracoDeployDependency,umbracoLock
The following columns were found in the database, but are not in the current schema:
umbracoDeployChecksum,id,umbracoDeployChecksum,entityType,umbracoDeployChecksum,entityGuid,umbracoDeployChecksum,entityPath,umbracoDeployChecksum,localChecksum,umbracoDeployChecksum,compositeChecksum,umbracoDeployDependency,sourceId,umbracoDeployDependency,targetId,umbracoDeployDependency,mode,cmsMacro,uniqueId,cmsMacroProperty,uniquePropertyId,umbracoRelationType,typeUniqueId,umbracoLock,id,umbracoLock,value,umbracoLock,name
The following constraints (Primary Keys, Foreign Keys and Indexes) were found in the database, but are not in the current schema:
FKumbracoDeployDependencyumbracoDeployChecksumid1,FKumbracoDeployDependencyumbracoDeployChecksumid2,PKumbracoDeployChecksum,PKumbracoDeployDependency,PKumbracoLock,IXumbracoNodeTrashed,IXumbracoNodeParentId,IXumbracoNodePath,IXumbracoNodeUniqueID,IXumbracoNodeObjectType,IXcmsContentType,IXcmsContentTypeicon,IXcmsTemplatenodeId,IXcmsContent,IXcmsContentVersionContentId,IXcmsContentVersionVersionId,IXcmsDocument,IXcmsDocumentpublished,IXcmsDocumentnewest,IXcmsDataTypenodeId,IXcmsDictionaryid,IXumbracoLanguagelanguageISOCode,IXumbracoLog,IXcmsMacroUniqueId,IXcmsMacroPropertyAlias,IXcmsMacroPropertyUniquePropertyId,IXcmsMacroPropertyAlias,IXcmsMemberLoginName,IXcmsPropertyTypeGroupUniqueID,IXcmsPropertyTypeUniqueID,IXcmsPropertyData1,IXcmsPropertyData2,IXcmsPropertyData3,IXumbracoRelationTypeUniqueId,IXumbracoRelationTypename,IXumbracoRelationTypealias,IXumbracoRelationparentChildType,IXcmsTags,IXumbracoUseruserLogin,IXcmsTaskTypealias,IXumbracoUser2NodePermissionnodeId,IXcomputerName,IXumbracoServerisActive,IXumbracoAccessnodeId,IXumbracoAccessRule,IXumbracoMigration,IXumbracoRedirectUrl
The following indexes were found in the database, but are not in the current schema:
IXumbracoDeployChecksum,IXumbracoNodePath,IXcmsMacroUniqueId,IXcmsMacroPropertyUniquePropertyId,IXcmsMemberLoginName,IXumbracoRelationTypeUniqueId,IXumbracoRelationTypename,IXumbracoRelationTypealias,IXumbracoRelationparentChildType,IXumbracoUser2NodePermissionnodeId
I've found similar issues with mentioned solutions, but unfortunately I've been unable to resolve mine. I have a feeling it can be resolved with some sql manipulation, but I'm not an expert in that arena. Any help will be much appreciated!
Just ran into this today when going form 7.5.14 to 7.7.4, what we did was disable the index, then generated new ids for the duplicate one and then rebuild the index, are you in a position where you can run some t-sql on the database? I'll post the snippet here
DB Error when upgrading from 7.5.7 to 7.7.4
I've tried upgrading via NuGet to 7.7.4 (also tried 7.6.5), but I receive the same error with both attempts.
The database failed to upgrade. ERROR: The database configuration failed with the following message: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.umbracoRelationType' and the index name 'IXumbracoRelationTypeUniqueId'. The duplicate key value is (23d1ac88-a2a5-3b05-b034-224a14cbbaf6). The statement has been terminated. Please check log file for additional information (can be found in '/App_Data/Logs/UmbracoTraceLog.txt')
Here is additional info from the mentioned log...
2017-10-26 11:57:14,027 [P35384/D10/T6] INFO Umbraco.Core.Persistence.Migrations.MigrationRunner - Executing sql statement 10: CREATE UNIQUE NONCLUSTERED INDEX [IXumbracoRelationTypeUniqueId] ON [umbracoRelationType] ([typeUniqueId])
2017-10-26 11:57:14,031 [P35384/D10/T6] ERROR Umbraco.Core.Persistence.UmbracoDatabase - Exception (69f27867). System.Data.SqlClient.SqlException (0x80131904): The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.umbracoRelationType' and the index name 'IXumbracoRelationTypeUniqueId'. The duplicate key value is (23d1ac88-a2a5-3b05-b034-224a14cbbaf6). The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() in c:\Code\github\SamSaffron\MiniProfiler\StackExchange.Profiling\Data\ProfiledDbCommand.cs:line 266 at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass1.<ExecuteNonQueryWithRetry>b__0() at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy retryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command) at Umbraco.Core.Persistence.Database.Execute(String sql, Object[] args) ClientConnectionId:9b192688-af2e-4d21-a4d6-e6099638e431 Error Number:1505,State:1,Class:16 2017-10-26 11:57:14,038 [P35384/D10/T6] ERROR Umbraco.Core.DatabaseContext - Database configuration failed System.Data.SqlClient.SqlException (0x80131904): The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.umbracoRelationType' and the index name 'IXumbracoRelationTypeUniqueId'. The duplicate key value is (23d1ac88-a2a5-3b05-b034-224a14cbbaf6). The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() in c:\Code\github\SamSaffron\MiniProfiler\StackExchange.Profiling\Data\ProfiledDbCommand.cs:line 266 at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass1.<ExecuteNonQueryWithRetry>b__0() at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy retryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command) at Umbraco.Core.Persistence.Database.Execute(String sql, Object[] args) at Umbraco.Core.Persistence.Migrations.MigrationRunner.ExecuteMigrations(IMigrationContext context, Database database) at Umbraco.Core.Persistence.Migrations.MigrationRunner.Execute(Database database, DatabaseProviders databaseProvider, Boolean isUpgrade) at Umbraco.Core.Persistence.Migrations.MigrationRunner.Execute(Database database, Boolean isUpgrade) at Umbraco.Core.DatabaseContext.UpgradeSchemaAndData(IMigrationEntryService migrationEntryService) ClientConnectionId:9b192688-af2e-4d21-a4d6-e6099638e431 Error Number:1505,State:1,Class:16 2017-10-26 11:57:14,042 [P35384/D10/T6] INFO Umbraco.Core.DatabaseContext - The database schema validation produced the following summary: The following tables were found in the database, but are not in the current schema: umbracoDeployChecksum,umbracoDeployDependency,umbracoLockThe following columns were found in the database, but are not in the current schema: umbracoDeployChecksum,id,umbracoDeployChecksum,entityType,umbracoDeployChecksum,entityGuid,umbracoDeployChecksum,entityPath,umbracoDeployChecksum,localChecksum,umbracoDeployChecksum,compositeChecksum,umbracoDeployDependency,sourceId,umbracoDeployDependency,targetId,umbracoDeployDependency,mode,cmsMacro,uniqueId,cmsMacroProperty,uniquePropertyId,umbracoRelationType,typeUniqueId,umbracoLock,id,umbracoLock,value,umbracoLock,name
The following constraints (Primary Keys, Foreign Keys and Indexes) were found in the database, but are not in the current schema: FKumbracoDeployDependencyumbracoDeployChecksumid1,FKumbracoDeployDependencyumbracoDeployChecksumid2,PKumbracoDeployChecksum,PKumbracoDeployDependency,PKumbracoLock,IXumbracoNodeTrashed,IXumbracoNodeParentId,IXumbracoNodePath,IXumbracoNodeUniqueID,IXumbracoNodeObjectType,IXcmsContentType,IXcmsContentTypeicon,IXcmsTemplatenodeId,IXcmsContent,IXcmsContentVersionContentId,IXcmsContentVersionVersionId,IXcmsDocument,IXcmsDocumentpublished,IXcmsDocumentnewest,IXcmsDataTypenodeId,IXcmsDictionaryid,IXumbracoLanguagelanguageISOCode,IXumbracoLog,IXcmsMacroUniqueId,IXcmsMacroPropertyAlias,IXcmsMacroPropertyUniquePropertyId,IXcmsMacroPropertyAlias,IXcmsMemberLoginName,IXcmsPropertyTypeGroupUniqueID,IXcmsPropertyTypeUniqueID,IXcmsPropertyData1,IXcmsPropertyData2,IXcmsPropertyData3,IXumbracoRelationTypeUniqueId,IXumbracoRelationTypename,IXumbracoRelationTypealias,IXumbracoRelationparentChildType,IXcmsTags,IXumbracoUseruserLogin,IXcmsTaskTypealias,IXumbracoUser2NodePermissionnodeId,IXcomputerName,IXumbracoServerisActive,IXumbracoAccessnodeId,IXumbracoAccessRule,IXumbracoMigration,IXumbracoRedirectUrl
The following indexes were found in the database, but are not in the current schema: IXumbracoDeployChecksum,IXumbracoNodePath,IXcmsMacroUniqueId,IXcmsMacroPropertyUniquePropertyId,IXcmsMemberLoginName,IXumbracoRelationTypeUniqueId,IXumbracoRelationTypename,IXumbracoRelationTypealias,IXumbracoRelationparentChildType,IXumbracoUser2NodePermissionnodeId
I've found similar issues with mentioned solutions, but unfortunately I've been unable to resolve mine. I have a feeling it can be resolved with some sql manipulation, but I'm not an expert in that arena. Any help will be much appreciated!
Comment author was deleted
Just ran into this today when going form 7.5.14 to 7.7.4, what we did was disable the index, then generated new ids for the duplicate one and then rebuild the index, are you in a position where you can run some t-sql on the database? I'll post the snippet here
Thanks for the quick reply, Tim. Yes, I have that access to the database. Thank you very much!
Comment author was deleted
AH sorry seems your install is complaining about a different index but it should be a similar fix https://stackoverflow.com/questions/44263266/umbraco-upgrading-from-7-4-3-to-7-6-1-cannot-insert-duplicate-key
is working on a reply...