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')
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%');
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…
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
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.
In case anyone else comes across this issue, @justin-nevitech was on the money with creating an index manually
Using this full index definition has worked for me (usual caveats about backing up and testing any database changes thoroughly):
CREATE UNIQUE NONCLUSTERED INDEX [IX_umbracoNode_UniqueId] ON [dbo].[umbracoNode]
(
[uniqueId] ASC
)
INCLUDE([parentId],[level],[path],[sortOrder],[trashed],[nodeUser],[text],[createDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
Sounds as if the index was introduced in v8 for new installs, but no migration was ever added, so if a database has been upgraded since v7 this could be why its missing.
hmm that’s interesting, thanks Mike. There’s not much of a paper trail before I took the project over in v8, but certainly looks like a migrations been missed here
Running this query isn’t returning any records, which (hopefully ) suggests that there hasn’t been any data integrity issues:
SELECT uniqueId, COUNT(*) FROM umbracoNode GROUP BY uniqueId HAVING COUNT(*) > 1;
If you are worried about data integrity in the DB.. you could perhaps do a clean uSync Export, replace your DB with a vanilla DB from a 17 blank install and then reimport from uSync?
But might not be as simple as all that, depending on the requirements to maintain audittrails, members, uForm entries etc…
And if it isn’t currently broken… then do you need to fix it!
If you need to maintain audit trails watch out for the automatic version cleansing in the later versions of umbraco… though I’m sure you’re already aware
