U4-7222 - SQL problem upgrading from 7.2.8 to 7.3.0 - adding uniqueID index

Created by mscommunities 12 Oct 2015, 06:05:28 Updated by Claus Jensen 14 Apr 2016, 10:10:08

Is duplicated by: U4-7253

Posted on umbraco forum: https://our.umbraco.org/forum/using/migrating-from-v5-to-v4-and-beyond/72051-umbraco-730-upgrade-failed-adding-uniqueid-index

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, 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 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.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 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) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 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 ClientConnectionId:7b052691-bed0-4055-966d-772079751dd0 Error Number:1505,State:1,Class:16

Comments

Shannon Deminick 12 Oct 2015, 08:38:46

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.


mscommunities 12 Oct 2015, 16:08:30

Ok, will do. Is there a max attachment size? Last I checked our db backup was 1.5 GB


Shannon Deminick 12 Oct 2015, 16:23:25

Yeah but you could post a drop box (or whatever) link


Nicholas Westby 15 Oct 2015, 19:13:36

@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?


mscommunities 15 Oct 2015, 23:31:37

Thanks, I missed that part. You should no longer see that link.


Claus Jensen 26 Oct 2015, 10:42:23

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.


mscommunities 28 Oct 2015, 23:00:19

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.


Claus Jensen 04 Nov 2015, 09:18:55

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.


mscommunities 04 Nov 2015, 09:25:15

Ok, sounds good, thanks for all the details.


Claus Jensen 04 Nov 2015, 09:28:56

No problem - we're happy to help! Let us know if you have any further issues.


Priority: Normal

Type: Bug

State: Closed

Assignee:

Difficulty: Normal

Category: Installation

Backwards Compatible: True

Fix Submitted:

Affected versions: 7.2.8

Due in version:

Sprint:

Story Points:

Cycle: