U4-10358 - Upgrade error from 7.1.8 to 7.6.5: The index 'IX_umbracoNodeUniqueID' is dependent on column 'uniqueID'

Created by Sebastiaan Janssen 25 Aug 2017, 08:25:07 Updated by Stephan 26 Aug 2017, 13:27:03

Relates to: U4-9993

Going from a 7.1.8 install and trying to upgrade directly to 7.6.5 we get the following error:

The index 'IX_umbracoNodeUniqueID' is dependent on column 'uniqueID'. ALTER TABLE ALTER COLUMN uniqueID failed because one or more objects access this column.

Also see U4-9993 where we thought we'd fixed this.

1 Attachments

Comments

Sebastiaan Janssen 25 Aug 2017, 15:41:28

Alright, there's two instructions in UpdateUniqueIdToHaveCorrectIndexType:

Alter.Table("umbracoNode").AlterColumn("uniqueID").AsGuid().NotNullable();

Create.Index("IX_umbracoNode_uniqueID").OnTable("umbracoNode").OnColumn("uniqueID").Unique();

They're in this order, but executed in reverse order. Which leads to this error since there's an index (second statement which was executed first) on the column uniqueID that we want to alter (first statement).


Sebastiaan Janssen 25 Aug 2017, 16:15:04

Duh.. it's because 7.2.0 upgrade adds the IX_umbracoNodeuniqueID index (note the missing underscore). We try to remove it in the 7.3.0 migration but since it's not been committed to the database yet, we can't find it, so it does not actually get dropped.

I am tempted to comment out the 7.2.0 migration (Umbraco.Core.Persistence.Migrations.Upgrades.TargetVersionSevenTwoZero.AddIndexToUmbracoNodeTable) since we know we're going to drop that index immediately again. In fact, we could just delete this whole class.

Unless there's a better solution @zpqrtbnk ? Tested with that specific migration commented out and it works.


Stephan 25 Aug 2017, 16:35:34

Duh - so is this the cause for the insane discussion about upgrade issues due to this index? If so, amazing you found it! Think... just delete the migration as it's redundant with a later migration indeed.


Sebastiaan Janssen 25 Aug 2017, 20:03:42

Cool, I also tried this in https://github.com/umbraco/Umbraco-CMS/blob/3de0b85ae3e1c307b58ad7c63cbf1e7446f486d8/src/Umbraco.Core/Persistence/Migrations/Upgrades/TargetVersionSevenThreeZero/UpdateUniqueIdToHaveCorrectIndexType.cs#L26 , which worked but isn't ideal:

var any = Context.Expressions.Any(exp => exp.Process(Context.Database).Contains("IX_umbracoNodeUniqueID")); if (indexes.Any(x => x.IndexName.InvariantEquals("IX_umbracoNodeUniqueID")) || any) Delete.Index("IX_umbracoNodeUniqueID").OnTable("umbracoNode");

Anyway, a PR to remove the 7.2 migration: https://github.com/umbraco/Umbraco-CMS/pull/2151 Easy peasy, then it all works as expected.


Stephan 26 Aug 2017, 13:26:55

cool, merged


Priority: Normal

Type: Bug

State: Fixed

Assignee:

Difficulty:

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions:

Due in version: 7.6.6

Sprint: Sprint 66

Story Points:

Cycle: