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

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).

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.

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.

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.

cool, merged