We have moved to GitHub Issues
Created by mscommunities 12 Oct 2015, 06:05:28 Updated by Claus Jensen 14 Apr 2016, 10:10:08
Is duplicated by: U4-7253
I get this error when upgrading from umbraco 7.2.8 to 7.3.0. It bombs when it tries to create a unique index on the uniqueID field for the umbracoNode table. If I query this table I see that I have close to 2000 duplicate entries. I still have a backup of our umbraco 4.11 database and that too has the same duplicate entries. Maybe the data got corrupted over the years since we have been upgrading it for 2-3 years. Any ideas on what I need to do to fix this issue? A bigger question is what other data integrity issues do we have. It would be nice if there was some sort of dbcc type check in umbraco that would validate the database.
2015-10-08 23:38:33,703 [P4920/D32/T32] INFO Umbraco.Core.Persistence.Migrations.MigrationRunner - Executing sql statement 17: CREATE UNIQUE NONCLUSTERED INDEX [IX_umbracoNode_uniqueID] ON [umbracoNode] ([uniqueID])
2015-10-08 23:38:33,897 [P4920/D32/T32] INFO Umbraco.Core.Persistence.UmbracoDatabase - 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.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() in c:\Code\github\SamSaffron\MiniProfiler\StackExchange.Profiling\Data\ProfiledDbCommand.cs:line 286
1 func) in c:\projects\umbraco-cms-xtjqd\src\Umbraco.Core\Persistence\FaultHandling\RetryPolicy.cs:line 172 at Umbraco.Core.Persistence.Database.Execute(String sql, Object args) in c:\projects\umbraco-cms-xtjqd\src\Umbraco.Core\Persistence\PetaPoco.cs:line 594 2015-10-08 23:38:33,900 [P4920/D32/T32] 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.umbracoNode' and the index name 'IX_umbracoNode_uniqueID'. The duplicate key value is (8fa52517-630d-44b9-ad12-00052c0101a4). The statement has been terminated. at System.Data.SqlClient.SqlConnection.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)
1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() in c:\Code\github\SamSaffron\MiniProfiler\StackExchange.Profiling\Data\ProfiledDbCommand.cs:line 286 at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass1.<ExecuteNonQueryWithRetry>b__0() in c:\projects\umbraco-cms-xtjqd\src\Umbraco.Core\Persistence\PetaPocoCommandExtensions.cs:line 56 at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) in c:\projects\umbraco-cms-xtjqd\src\Umbraco.Core\Persistence\FaultHandling\RetryPolicy.cs:line 172
at Umbraco.Core.Persistence.Database.Execute(String sql, Object args) in c:\projects\umbraco-cms-xtjqd\src\Umbraco.Core\Persistence\PetaPoco.cs:line 608
at Umbraco.Core.Persistence.Migrations.MigrationRunner.ExecuteMigrations(IMigrationContext context, Database database) in c:\projects\umbraco-cms-xtjqd\src\Umbraco.Core\Persistence\Migrations\MigrationRunner.cs:line 278
at Umbraco.Core.Persistence.Migrations.MigrationRunner.Execute(Database database, DatabaseProviders databaseProvider, Boolean isUpgrade) in c:\projects\umbraco-cms-xtjqd\src\Umbraco.Core\Persistence\Migrations\MigrationRunner.cs:line 126
at Umbraco.Core.DatabaseContext.UpgradeSchemaAndData(IMigrationEntryService migrationEntryService) in c:\projects\umbraco-cms-xtjqd\src\Umbraco.Core\DatabaseContext.cs:line 670
The only way for us to debug this is if you send us your database. You can attach it to this issue and set it to Visible to HQ Only. The upgrader actually already fixes several data integrity issues but some people's older databases contain some very strange issues.
Ok, will do. Is there a max attachment size? Last I checked our db backup was 1.5 GB
Yeah but you could post a drop box (or whatever) link
@mscommunities FYI, I can see your link to your partially upgraded database. Did you mean to protect your comment so only Umbraco HQ can see it?
Thanks, I missed that part. You should no longer see that link.
I have looked at both of your databases and they both contain corrupt data. For some reason (as @door3 mentions) there is multiple media items existing with the same uniqueID. This could have happened due to a partially failed upgrade earlier on, or maybe from an import of data gone wrong.
It is however not something that actually has anything to do with the upgrade of going to 7.2.8 to 7.3.0 - you only see the error here due to the fact that this upgrade actually applies an index with a constraint now longer allowing duplicates in this column.
The correct way of resolving the issue and allowing you to upgrade, would be to ensure that the database is in a non-corrupt state before starting the upgrade process. This should be done by ensuring that there are no duplicate ID's in the unique ID column of this table:
In @door3's case it is only 4 items having 3 duplicate entries each and the best solution would most likely be to figure out whether these items are actually all in use or if the 2 duplicates of each item could be manually removed to regain a non-corrupted database.
In @mscommunities case it is a bit worse since there's actually 1098 items with at least one duplicate (and some having up to 19 duplicates) - not really manageable by doing manual cleanup.
Since references for media items are done with the actual ID column and not the uniqueID, it should not be a problem reassigning a new GUID to all of the problematic rows unless you have specifically used this uniqueID in some custom code in your site (if that is the case - I can not vouch for this solution).
These uniqueID's are currently only used for matching up nodes on multiple environments when you are using Courier for deployments - so please note that if you DO use this site in a Courier setup you will either need to redeploy the whole database/site manually after upgrading, or use a custom script to ensure the generated GUIDs are generated the same way on both source and destination.
If you simply want to be able to upgrade and you ''do not use Courier'', this SQL script should suffice in generating new random GUIDs for the nodes where duplicates exist:
update umbracoNode set uniqueID = newid() from ( select id, uniqueID from umbracoNode where uniqueID in ( select uniqueID from umbracoNode group by uniqueID having count(*) > 1) ) n where umbracoNode.id = n.id
If you intend to use Courier - either do the manual redeployment after upgrading or ensure that the generation of GUIDs is based on a combination of properties from the node, that will ensure each item will get a unique ID, but that this unique ID is the same no matter which environment it is generated on.
Oh - and for the record, I just ran my script above on @mscommunities database updating 4xxx rows and successfully upgraded to 7.3.0 using NuGet afterwards.
Thank you so much for looking into this @Claus.Jensen. We do in fact use courier which may explain all the dups we have. We intend to continue using courier also. We have a staging website and a production website and usually push changes from staging to production (sometimes the other way around but not much). So you recommend running this cleanup script on our production server and then refreshing staging with production so they match up exactly? The other option you gave seems too risky for us to attempt.
Sorry for the delayed answer!
The duplicate uniqueID's actually have nothing to do with Courier. Courier is not responsible for generating these ID's - it only utilizes them when transferring. I can't really say what has caused duplicate uniqueID's to show up in your specific site, but I'm guessing it could be an import of external content gone wrong at some point maybe.
I would say your best option (if at all possible) is to decide which of your sites is most recent/updated/correct and then run the above SQL script on that database. This will ensure that all rows that have duplicate GUID's will have a new GUID generated. This is not really a risky thing to do at the current stage of Umbraco, since the Core internals do not actually use these GUID's yet (they use the integer ID column).
UniqueID's are however as mentioned used by Courier when transferring from one environment to another. This is the reason why, when you have "fixed" the GUID's in one site, you will need to replicate this site/database to all other environments to ensure the GUID for each content/media item is the same on all environments.
From there on, you should be able to use Courier to deploy content/media as intended without having to replicate the database again.
Ok, sounds good, thanks for all the details.
No problem - we're happy to help! Let us know if you have any further issues.
Backwards Compatible: True
Affected versions: 7.2.8
Due in version: