Umbraco 13 to 17 upgrade - premigration error

Questions
1

Hi there, I’m trying to upgrade a project from 13.15.0 to 17.5.1 .

All the packages update and the project builds as exspected, but after the migration starts to run I’m hitting this error in the browser, with an Umbraco failed to boot message:

 An error occurred while running the premigration upgrade.
The database configuration failed with the following message: 
There are no primary or candidate keys in the referenced table 
'umbracoNode' that match the referencing column list in the 
foreign key 'FK_umbracoUserGroup2GranularPermission_umbracoNode_uniqueId'.
Could not create constraint or index. See previous errors.
Please check log file for additional information
 (can be found in 'LoggingSettings.Directory')

I’ve tried running this query, but I think this is for constraints being untrusted rather than not being created (Untrusted Database Constraints | CMS 17.latest (LTS) | Umbraco Documentation):

       OBJECT_NAME(fk.parent_object_id) AS TableName, fk.name AS ConstraintName
FROM sys.foreign_keys fk
INNER JOIN sys.schemas s ON fk.schema_id = s.schema_id
WHERE fk.is_not_trusted = 1
  AND (OBJECT_NAME(fk.parent_object_id) LIKE 'umbraco%' OR OBJECT_NAME(fk.parent_object_id) LIKE 'cms%')

UNION ALL

SELECT 'Check constraint', s.name,
       OBJECT_NAME(cc.parent_object_id), cc.name
FROM sys.check_constraints cc
INNER JOIN sys.schemas s ON cc.schema_id = s.schema_id
WHERE cc.is_not_trusted = 1
  AND (OBJECT_NAME(cc.parent_object_id) LIKE 'umbraco%' OR OBJECT_NAME(cc.parent_object_id) LIKE 'cms%');

Has anyone come across this before?

Thanks

2

Hi @si25

It sounds like the index on umbracoNode.uniqueId is missing.

Can you check if you have this index, and if not can you create it?

CREATE UNIQUE NONCLUSTERED INDEX [IX_umbracoNode_uniqueID] ON [umbracoNode] ([uniqueId] ASC);

If it is missing, it would be a bit worrying, although the chance of a duplicate GUID being created would be very slim.

You could create a blank v17 instance and compare the tables/keys/indexes to see if you are missing anything else. There may have been a failed migration somewhere along the lines…

Justin

3

Thanks @justin-nevitech appreciate it, that’s been a really useful starting point

Those keys are missing and creating an index DOES work, though I’m also a little unsure whether doing so manually will create any unexpected db issues in production…

Interestingly FX_umbracoUserGroup2GranularPermission is a v14 db addition, so testing v13 - v14 migration in case that gets it working :crossed_fingers:

4

Hi @si25

That was an index definition from a v17 project, so it should be the default one Umbraco has - why you are missing it I’ve no idea? It shouldn’t cause any problems.

You should be able to go from LTS to LTS, so you should only need to update using the v17 project - you shouldn’t need v14 as an interim step. If that’s not the case for you then by all means see if v14 works.

Let us know how you get on.

Justin