Upgrade from 7.7.11 to 7.8.X database missing keys FK_cmsMedia_cmsContent_nodeId
When I upgrade (Nuget) from version 7.7.11 to 7.8.# I get an Error during installation. The database is in an earlier stage moved via script but I haven't had any problems with that before.
The database failed to upgrade. ERROR: The database configuration failed with the following message: There are no primary or candidate keys in the referenced table 'cmsContent' that match the referencing column list in the foreign key 'FK_cmsMedia_cmsContent_nodeId'.
Could not create constraint or index.
I've managed to fix this for my site. The issue was one of the indexes was "Non-unique" whereas it needed to be unique. I updated this by scripting out the index for a site which had the index as "unique", ran the upgrade and it all worked.
I also managed to fix this on my site. Ben put me in the right direction. Except my database has no ‘Non-Clusterd’ indexes at all. Perhaps they got lost by a previous action of scripting the database.
What I (also) did was installing a clean version of 7.8.#.
Then (in SQL Server Management Studio) go to the Index of the ‘cmsContent’ table.
On the ‘IX_cmsContent (Unique, Non-Clusterd)’ index right-click ‘Script Index as -> CREATE To -> New Query Editor Window’.
Execute this SQL script on your site database (USE [yourSiteDatabase]).
After running the site (umbraco instalation) again, I don’t get any errors :-)
Note: the ‘cmsMedia’ table is created during the installation. You don’t need to create this by yourself.
USE [yourDatabasename]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_cmsContent] ON [dbo].[cmsContent]
(
[nodeId] ASC
)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) ON [PRIMARY]
GO
I ran into this issue when upgrading to v7.8.0. My fix was slightly different. Somehow there were two entries in the cmsContent table with the same nodeId, which corrupted the IX_cmsContent index. Ran the following query to find the duplicate:
select nodeId, COUNT(pk) from cmsContent cc
group by cc.nodeId
havinG COUNT(pk) > 1
Both entries referenced the same UmbracoNode, so I deleted the second entry. Then I rebuilt the index and ran the update and it went through fine.
Upgrade from 7.7.11 to 7.8.X database missing keys FK_cmsMedia_cmsContent_nodeId
When I upgrade (Nuget) from version 7.7.11 to 7.8.# I get an Error during installation. The database is in an earlier stage moved via script but I haven't had any problems with that before.
The database failed to upgrade. ERROR: The database configuration failed with the following message: There are no primary or candidate keys in the referenced table 'cmsContent' that match the referencing column list in the foreign key 'FK_cmsMedia_cmsContent_nodeId'. Could not create constraint or index.
I've also had this same issue but upgrading from 7.7.9 to 7.8.1
My tests so far have been:
Issue created for this here http://issues.umbraco.org/issue/U4-10983
I've managed to fix this for my site. The issue was one of the indexes was "Non-unique" whereas it needed to be unique. I updated this by scripting out the index for a site which had the index as "unique", ran the upgrade and it all worked.
Can you please be a little more specific about how you solved the problem? I'm not exactly sure what to do.
It worked btw! Thank you :-)
Hi Benjamin,
All info can be found on the issue http://issues.umbraco.org/issue/U4-10983
I basically deleted the existing index and then from a working site, scripted out the index and recreated it in my site.
Thanks
Ben
Hi, I ran this sql query manually:
CREATE TABLE [cmsMedia] ([nodeId] INTEGER NOT NULL, [versionId] UniqueIdentifier NOT NULL, [mediaPath] NVARCHAR(255) NULL)
After that installation ran without errors.
That worked for me.
/ Lars
I also managed to fix this on my site. Ben put me in the right direction. Except my database has no ‘Non-Clusterd’ indexes at all. Perhaps they got lost by a previous action of scripting the database.
What I (also) did was installing a clean version of 7.8.#. Then (in SQL Server Management Studio) go to the Index of the ‘cmsContent’ table. On the ‘IX_cmsContent (Unique, Non-Clusterd)’ index right-click ‘Script Index as -> CREATE To -> New Query Editor Window’.
Execute this SQL script on your site database (USE [yourSiteDatabase]).
After running the site (umbraco instalation) again, I don’t get any errors :-)
Note: the ‘cmsMedia’ table is created during the installation. You don’t need to create this by yourself.
Thank you Robbie!
I was getting the same error when upgrading 7.7.7 to 7.11.1 and that SQL script worked perfectly as described.
Cheers,
Russ.
I ran into this issue when upgrading to v7.8.0. My fix was slightly different. Somehow there were two entries in the cmsContent table with the same nodeId, which corrupted the IX_cmsContent index. Ran the following query to find the duplicate:
select nodeId, COUNT(pk) from cmsContent cc group by cc.nodeId havinG COUNT(pk) > 1
Both entries referenced the same UmbracoNode, so I deleted the second entry. Then I rebuilt the index and ran the update and it went through fine.
is working on a reply...