Update Umbraco 8.16.0 to Umbraco 9.1.1

thomashdk commented 2 years ago

Which exact Umbraco version are you using? For example: 9.0.1 - don't just write v9

Umbraco 8.16.0 to Umbraco 9.1.1

Bug summary

When upgrading Umbraco from 8.16.0 to 9.1.1 fails on installations.

Specifics

I tried to update from 8.16.0 to 9.1.1 and on install it fails.

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 'cmsContentType' that match the referencing column list in the foreign key 'FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId'. Could not create constraint or index. See previous errors. Please check log file for additional information (can be found in '~/umbraco/Logs')

I have tried to update step by step:

8.16.0 > 9.0.0 Worked 9.0.0 > 9.0.1 Worked: 9.01 > 9.1.0 Failed with:

ERROR: The database configuration failed with the following message: There are no primary or candidate keys in the referenced table 'cmsContentType' that match the referencing column list in the foreign key 'FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId'.

From the log: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Umbraco.Cms.Core.Install.InstallException: 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 'cmsContentType' that match the referencing column list in the foreign key 'FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId'. Could not create constraint or index. See previous errors. Please check log file for additional information (can be found in '~/umbraco/Logs') at Umbraco.Cms.Infrastructure.Install.InstallSteps.DatabaseUpgradeStep.ExecuteAsync(Object model) --- End of inner exception stack trace --- at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters) at Umbraco.Cms.Web.BackOffice.Install.InstallApiController.ExecuteStepAsync(InstallSetupStep step, Object instruction) at Umbraco.Cms.Web.BackOffice.Install.InstallApiController.PostPerformInstall(InstallInstructions installModel)

Steps to reproduce

Update from 8.16.0 > 9.0.0, 9.0.0 > 9.0.1, 9.01 > 9.1.0

or update from 8.16.0 to 9.1.1 directly.

Expected result / actual result

Expected Ubmraco 9.1.1 to be installed.

thomashdk commented 2 years ago

Same issue from 8.16 to 9.1.2

thomashdk commented 2 years ago

When I connect my 8.16 database to my Umbraco 9.1.2 solution and getting the login screen to start the upgrade, the user password are not working, but need to reset it before it works. After that I get the error as above.

That gives this error: The input is not a valid Base-64 string as it contains a non-base 64 character, more than two padding characters, or an illegal character among the padding characters.

thomashdk commented 2 years ago

Also gettings this.

The database schema validation produced the following summary: "The following tables were found in the database, but are not in the current schema:
 SkyfishLogin,UCErrorLog,UFRecordDataBit,UFRecordDataDateTime,UFRecordDataInteger,UFRecordDataLongString,UFRecordDataString,UFRecordFields,UFRecords,UFUserFormSecurity,UFUserSecurity,LdapUsers,umbracoDeployDependency,umbracoDeploySignature,CabanaRedirects,_TransactionIndex_098b082c-5e66-4ac1-8764-54e6a19e957f,PageCounter,UserDashboard,SiteImprove_settings,SiteImproveUrlMap,FeedbackComments,MessageWallLikes,MessageWallPosts,NodeMediaLog,PageEditorComments,RestrictBackofficeTable,umbracoExternalLoginToken,umbracoLogViewerQuery,umbracoContentVersionCleanupPolicy

 The following columns were found in the database, but are not in the current schema:
 UFRecordDataBit,Key,UFRecordDataDateTime,Key,UFRecordDataInteger,Key,UFRecordDataLongString,Key,UFRecordFields,Key,UFRecordFields,FieldId,UFRecordDataString,Key,UFRecords,CurrentPage,UFRecords,Form,UFRecords,UniqueId,UFUserFormSecurity,Form,LdapUsers,UserId,_TransactionIndex_098b082c-5e66-4ac1-8764-54e6a19e957f,Id,SiteImproveUrlMap,id,SiteImprove_settings,id,SiteImproveUrlMap,PageId,FeedbackComments,PageId,FeedbackComments,Id,MessageWallLikes,Id,MessageWallLikes,RelatedPostId,MessageWallPosts,Id,MessageWallPosts,RelatedPostId,MessageWallPosts,FileId,NodeMediaLog,id,NodeMediaLog,userId,NodeMediaLog,nodeId,PageEditorComments,Id,PageEditorComments,PageId,RestrictBackofficeTable,AddedByUserId,RestrictBackofficeTable,Id,PageCounter,Id,PageCounter,PageId,UserDashboard,Id,UserDashboard,UserID,CabanaRedirects,Id,CabanaRedirects,Accessed,umbracoDeployDependency,mode,umbracoDeploySignature,nodeId,UFUserFormSecurity,SecurityType,UFUserFormSecurity,Id,UFRecords,UmbracoPageId,UFRecordDataString,Id,UFRecords,Id,UFRecordFields,Record,UFRecordDataInteger,Value,UFRecordDataLongString,Id,UFRecordDataDateTime,Id,UFRecordDataInteger,Id,UFRecordDataBit,Id,SkyfishLogin,Id,UCErrorLog,Id,UCErrorLog,Date,UFRecordDataDateTime,Value,UFRecords,Created,UFRecords,Updated,LdapUsers,LastUpdated,LdapUsers,Created,MessageWallPosts,Date,MessageWallPosts,ExpireDate,UFRecords,RecordData,UFRecordDataLongString,Value,UCErrorLog,Read,UFRecordDataBit,Value,UFUserFormSecurity,HasAccess,UFUserFormSecurity,AllowInEditor,UFUserSecurity,ManageDataSources,UFUserSecurity,ManagePreValueSources,UFUserSecurity,ManageWorkflows,UFUserSecurity,ManageForms,LdapUsers,IsEditor,LdapUsers,IsAdmin,umbracoDeployDependency,ordering,CabanaRedirects,WildCard,umbracoServer,isMaster,MessageWallPosts,IsGlobal,SiteImprove_settings,Installed,FeedbackComments,Feedback,PageCounter,Uniq,LdapUsers,TelephoneNumber,LdapUsers,MobileNumber,UCErrorLog,Thread,UCErrorLog,Level,UCErrorLog,Logger,FeedbackComments,Reference,FeedbackComments,Lang,FeedbackComments,ScreenWidth,MessageWallLikes,UserAlias,FeedbackComments,Date,FeedbackComments,Name,FeedbackComments,Email,FeedbackComments,Comment,SiteImprove_settings,Token,SiteImproveUrlMap,CurrentUrlPart,SiteImproveUrlMap,NewUrlPart,RestrictBackofficeTable,Date,RestrictBackofficeTable,Name,RestrictBackofficeTable,UserGroupAlias,RestrictBackofficeTable,PageAlias,RestrictBackofficeTable,Tabs,RestrictBackofficeTable,Fields,RestrictBackofficeTable,Components,PageEditorComments,Username,PageEditorComments,Comment,PageEditorComments,Date,MessageWallPosts,InterestAreas,MessageWallPosts,UserAlias,MessageWallPosts,Message,NodeMediaLog,nodeName,NodeMediaLog,type,NodeMediaLog,action,NodeMediaLog,userName,NodeMediaLog,date,UserDashboard,Links,PageCounter,Date,UCErrorLog,Message,UCErrorLog,Exception,UCErrorLog,ThreadId,UCErrorLog,SourceContext,UCErrorLog,ReadBy,SkyfishLogin,Username,SkyfishLogin,Password,SkyfishLogin,Secret,SkyfishLogin,Api,SkyfishLogin,Token,SkyfishLogin,ExpireDateToken,UFRecordFields,Alias,UFRecordFields,DataType,UFRecordDataString,Value,UFUserFormSecurity,User,UFRecords,State,UFRecords,IP,UFRecords,MemberKey,UFUserSecurity,User,LdapUsers,JobTitle,LdapUsers,Departments,LdapUsers,Interests,LdapUsers,ProfileImage,LdapUsers,PersonText,LdapUsers,PersonLink,LdapUsers,UserAlias,LdapUsers,FirstName,LdapUsers,LastName,LdapUsers,Email,LdapUsers,MainDepartment,umbracoDeploySignature,checksum,umbracoDeployDependency,sourceUdi,umbracoDeployDependency,targetUdi,umbracoDeploySignature,udi,CabanaRedirects,LastReference,CabanaRedirects,LastReferenceDate,CabanaRedirects,CreatedBy,CabanaRedirects,Date,CabanaRedirects,OldUrl,CabanaRedirects,NewUrl,umbracoContentVersion,preventCleanup,cmsMember,passwordConfig,cmsMember,securityStampToken,cmsMember,emailConfirmedDate,umbracoServer,isSchedulingPublisher,umbracoExternalLoginToken,id,umbracoExternalLoginToken,externalLoginId,umbracoExternalLoginToken,name,umbracoExternalLoginToken,value,umbracoExternalLoginToken,createDate,umbracoLogViewerQuery,id,umbracoLogViewerQuery,name,umbracoLogViewerQuery,query,umbracoContentVersionCleanupPolicy,contentTypeId,umbracoContentVersionCleanupPolicy,preventCleanup,umbracoContentVersionCleanupPolicy,keepAllVersionsNewerThanDays,umbracoContentVersionCleanupPolicy,keepLatestVersionPerDayForDays,umbracoContentVersionCleanupPolicy,updated

 The following constraints (Primary Keys, Foreign Keys and Indexes) were found in the database, but are not in the current schema:
 FK_UFRecordDataBit_UFRecordFields_Key,FK_UFRecordDataDateTime_UFRecordFields_Key,FK_UFRecordDataInteger_UFRecordFields_Key,FK_UFRecordDataLongString_UFRecordFields_Key,FK_UFRecordDataString_UFRecordFields_Key,FK_UFRecordFields_UFRecords_Record,FK_umbracoExternalLoginToken_umbracoExternalLogin_id,FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId,PK___Transac__3214EC07CC590AE3,PK__LdapUser__1788CC4C42AE67EF,PK__UCErrorL__3214EC0742CAAE70,PK_CabanaRedirects,PK_deployDependency,PK_deploySignature,PK_FeedbackComments,PK_id,PK_MessageWallLikes,PK_MessageWallPosts,PK_PageCounter,PK_PageEditorComments,PK_RestrictBackofficeTable,PK_SiteImprove_settings,PK_SiteImproveUrlMap,PK_SkyfishLogin,PK_UFRecordDataBit,PK_UFRecordDataDateTime,PK_UFRecordDataInteger,PK_UFRecordDataLongString,PK_UFRecordDataString,PK_UFRecordFields,PK_UFRecords,PK_UFUserFormSecurity,PK_UserDashboard,PK_umbracoExternalLoginToken,PK_umbracoLogViewerQuery

 The following indexes were found in the database, but are not in the current schema:
 UmbracoCloud_DateRead,IX_UFRecordDataBit_Key,IX_UFRecordDataDateTime_Key,IX_UFRecordDataInteger_Key,IX_UFRecordDataLongString_Key,IX_datastring_recordfield,IX_record_recordfield,UK_UFUserFormSecurity_User_Form,IX_cmsLanguageText_languageId,IX_umbracoExternalLogin_userId,IX_umbracoExternalLogin_LoginProvider,IX_umbracoExternalLogin_ProviderKey,IX_umbracoExternalLoginToken_Name,IX_LogViewerQuery_name

 The following unknown constraints (Primary Keys, Foreign Keys and Indexes) were found in the database, but are not in the current schema:
 UK_UFUserFormSecurity_User_Form

 "

thomashdk commented 2 years ago

Looks like running this

/****** Object:  Table [dbo].[umbracoContentVersionCleanupPolicy]    Script Date: 10-12-2021 11:00:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[umbracoContentVersionCleanupPolicy](
    [contentTypeId] [int] NOT NULL,
    [preventCleanup] [bit] NOT NULL,
    [keepAllVersionsNewerThanDays] [int] NULL,
    [keepLatestVersionPerDayForDays] [int] NULL,
    [updated] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[umbracoContentVersionCleanupPolicy]  WITH CHECK ADD  CONSTRAINT [FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId] FOREIGN KEY([contentTypeId])
REFERENCES [dbo].[cmsContentType] ([nodeId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[umbracoContentVersionCleanupPolicy] CHECK CONSTRAINT [FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId]
GO

And then reload the installation site again works.

p-m-j commented 2 years ago

Hey @thomashdk, I'm sorry to hear about your pain upgrading :(

I tried to reproduce by creating 8.16.0 site with starter template and then: Updating to 9.0.0 -> 9.0.1 -> 9.1.0 -> 9.1.1 -> 9.1.2

Everything worked just fine

Additionally I tried from 8.16.0 -> 9.1.2 which was also fine.

I had no issues with database migrations or user logins.

Are you able to provide any additional details / steps to reproduce?

thomashdk commented 2 years ago

Hmm sadly no. I have some custom table in the db? Not sure if that could have something to do with it

nul800sebastiaan commented 2 years ago

Hmmm, a mystery for now! I'll close this one as it looks like you're up and running now, but we might need to re-open if other people have this problem and can provide more info!

qamarkhan84 commented 2 years ago

Hi there , I am upgrading umbraco from version 8.17.1 to version 9.1.2. I am getting following error

Description: The process was terminated due to an unhandled exception. Exception Info: System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'umbracoContentVersionCleanupPolicy'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

qamarkhan84 commented 2 years ago

I found that root cause of this is that in my startup.cs before the line u.UseInstallerEndpoints(); I had some custom logic to register routing logic, which was blocking to migration start.

sumeyyeclockwork commented 2 years ago

I am upgrading umbraco 8.16 to 8.18 and getting same SQL error

sumeyyeclockwork commented 2 years ago

Looks like running this

/****** Object:  Table [dbo].[umbracoContentVersionCleanupPolicy]    Script Date: 10-12-2021 11:00:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[umbracoContentVersionCleanupPolicy](
  [contentTypeId] [int] NOT NULL,
  [preventCleanup] [bit] NOT NULL,
  [keepAllVersionsNewerThanDays] [int] NULL,
  [keepLatestVersionPerDayForDays] [int] NULL,
  [updated] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[umbracoContentVersionCleanupPolicy]  WITH CHECK ADD  CONSTRAINT [FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId] FOREIGN KEY([contentTypeId])
REFERENCES [dbo].[cmsContentType] ([nodeId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[umbracoContentVersionCleanupPolicy] CHECK CONSTRAINT [FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId]
GO

And then reload the installation site again works.

I run this command but the second part cause error because new table dese not have any key

abaderkar commented 2 years ago

Looks like running this

/****** Object:  Table [dbo].[umbracoContentVersionCleanupPolicy]    Script Date: 10-12-2021 11:00:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[umbracoContentVersionCleanupPolicy](
    [contentTypeId] [int] NOT NULL,
    [preventCleanup] [bit] NOT NULL,
    [keepAllVersionsNewerThanDays] [int] NULL,
    [keepLatestVersionPerDayForDays] [int] NULL,
    [updated] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[umbracoContentVersionCleanupPolicy]  WITH CHECK ADD  CONSTRAINT [FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId] FOREIGN KEY([contentTypeId])
REFERENCES [dbo].[cmsContentType] ([nodeId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[umbracoContentVersionCleanupPolicy] CHECK CONSTRAINT [FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId]
GO

And then reload the installation site again works.

I run this command but the second part cause error because new table dese not have any key

Hi, I have the same issue. I want upgrade from 8.17.2 to 8.18.0

p-m-j commented 2 years ago

@abaderkar just tried 8.17.2 -> 8.18, seemed to work fine, ~what edition of SQL Server are you using, potentially related to https://github.com/umbraco/Umbraco-CMS/issues/11964~

p-m-j commented 2 years ago

Can anyone impacted run the following query and post the result set?

select 
    T.name as table_name, 
    I.name as index_name, 
    AC.Name as column_name,
    I.is_unique,
    I.is_unique_constraint
from sys.tables as T inner join sys.indexes as I on T.[object_id] = I.[object_id]
   inner join sys.index_columns as IC on IC.[object_id] = I.[object_id] and IC.[index_id] = I.[index_id]
   inner join sys.all_columns as AC on IC.[object_id] = AC.[object_id] and IC.[column_id] = AC.[column_id]
   inner join sys.schemas as S on T.[schema_id] = S.[schema_id]
WHERE S.name = (SELECT SCHEMA_NAME()) 
and T.Name = 'cmsContentType'

akiselenko commented 2 years ago

Migration 8.17.0 -> 9.3.1 The same error: 'There are no primary or candidate keys in the referenced table 'cmsContentType' that match the referencing column list in the foreign key 'FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId'.

Can anyone impacted run the following query and post the result set?

select 
  T.name as table_name, 
  I.name as index_name, 
  AC.Name as column_name,
  I.is_unique,
  I.is_unique_constraint
from sys.tables as T inner join sys.indexes as I on T.[object_id] = I.[object_id]
   inner join sys.index_columns as IC on IC.[object_id] = I.[object_id] and IC.[index_id] = I.[index_id]
   inner join sys.all_columns as AC on IC.[object_id] = AC.[object_id] and IC.[column_id] = AC.[column_id]
   inner join sys.schemas as S on T.[schema_id] = S.[schema_id]
WHERE S.name = (SELECT SCHEMA_NAME()) 
and T.Name = 'cmsContentType'

cmsContentType IX_cmsContentType_icon icon 0 0 cmsContentType IX_cmsContentType nodeId 1 0 cmsContentType PK_cmsContentType pk 1 0

p-m-j commented 2 years ago

@akiselenko i'm pretty stumped, since SQL server 2005 a FK should be able to reference a column which has a unique index which cmsContentType.nodeId does in your database.

Obviously you're not alone in having this issue but I don't know how to explain it and I can't reproduce.

What do you get for the following queries

SELECT SERVERPROPERTY('ProductVersion');

SELECT name, compatibility_level 
FROM sys.databases
where name = '{{your database name}}'

Would you be able to script your database (schema only) and upload somewhere (GitHub gist etc) https://docs.microsoft.com/en-us/sql/ssms/tutorials/scripting-ssms?view=sql-server-ver15#script-a-database-by-using-the-generate-scripts-option

brano commented 2 years ago

I have the same issue when upgrading Umbraco version 8.16 to latest version 8.18.1.

Error from App_Data/Logs: Umbraco.Web.Install.InstallException: 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 'cmsContentType' that match the referencing column list in the foreign key 'FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId'. Could not create constraint or index. See previous errors.

Initially it was v7 PROD database copy which we have successfully migrated to v8.16 some time ago and all worked (after many upgrade steps and issues when going from v7 to v8).

brano commented 2 years ago

Nothing special in our [dbo].[cmsContentType] table I guess...

p-m-j commented 2 years ago

@brano Would you be able to script your database (schema only) and upload somewhere.

https://docs.microsoft.com/en-us/sql/ssms/tutorials/scripting-ssms?view=sql-server-ver15#script-a-database-by-using-the-generate-scripts-option

brano commented 2 years ago

https://gist.github.com/brano/c9df1bea80bbe50ff61d3106e2686d0d

@p-m-j thanks for your help!

p-m-j commented 2 years ago

@brano On line 1133 you have

ALTER INDEX [IX_cmsContentType] ON [dbo].[cmsContentType] DISABLE

We need that index to create the new foreign key, if you run the following can you then migrate?

ALTER INDEX [IX_cmsContentType] ON [dbo].[cmsContentType] REBUILD

Edit: hmm I also get those statements if I script out a database, but it still might be the answer

What is the result set for

select 
    T.name as table_name, 
    I.name as index_name, 
    AC.Name as column_name,
    I.is_unique,
    I.is_unique_constraint,
    I.is_disabled
from sys.tables as T 
inner join sys.indexes as I on T.[object_id] = I.[object_id]
inner join sys.index_columns as IC on IC.[object_id] = I.[object_id] and IC.[index_id] = I.[index_id]
inner join sys.all_columns as AC on IC.[object_id] = AC.[object_id] and IC.[column_id] = AC.[column_id]
inner join sys.schemas as S on T.[schema_id] = S.[schema_id]
where S.name = schema_name()
and I.is_disabled = 1

brano commented 2 years ago

Great it helped! Thanks man! Enabling index [IX_cmsContentType] ON [dbo].[cmsContentType] has fixed migration issue (from 8.16 to 8.18.1).

chrden commented 1 year ago

For anyone who experiences this issue, this worked for me.

I tried the upgrade from Umbraco 9.0.0 > 9.0.1 and received the original error. After running this script, the installation succeeded, and further upgrades also worked 🎉

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[umbracoContentVersionCleanupPolicy](
  [contentTypeId] [int] NOT NULL,
  [preventCleanup] [bit] NOT NULL,
  [keepAllVersionsNewerThanDays] [int] NULL,
  [keepLatestVersionPerDayForDays] [int] NULL,
  [updated] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[umbracoContentVersionCleanupPolicy]  WITH CHECK ADD  CONSTRAINT [FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId] FOREIGN KEY([contentTypeId])
REFERENCES [dbo].[cmsContentType] ([nodeId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[umbracoContentVersionCleanupPolicy] CHECK CONSTRAINT [FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId]
GO

Thanks @thomashdk 👍

mirkomaty commented 1 year ago

I had this error while updating from Umbraco 10.3.2 to 10.6.1. The problem occurs because the nodeId column of the cmsContentType table is not unique. Therefore the constraint couldn't be executed. You can come over it using these lines:

alter table cmsContentType add unique(nodeid)
GO
ALTER TABLE [umbracoContentVersionCleanupPolicy] ADD CONSTRAINT [FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId] FOREIGN KEY ([contentTypeId]) REFERENCES [cmsContentType]([nodeId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

lordy1981 commented 1 year ago

I had this error while updating from Umbraco 10.3.2 to 10.6.1. The problem occurs because the nodeId column of the cmsContentType table is not unique. Therefore the constraint couldn't be executed. You can come over it using these lines:
alter table cmsContentType add unique(nodeid)
GO
ALTER TABLE [umbracoContentVersionCleanupPolicy] ADD CONSTRAINT [FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId] FOREIGN KEY ([contentTypeId]) REFERENCES [cmsContentType]([nodeId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

@nul800sebastiaan can you confirm this is the recommended fix, or whether you will be releasing another version to resolve this? We are trying to update to 10.6.1 due to the vulnerability fix, but cannot get past this error.

ane-dwarf commented 10 months ago

I was facing this issue when upgrading from v10.0.0 to v11.3.1 (the migration from v8.18.7 => v9.3.1 => v10.0.0 ran perfectly fine) and what fixed it for me was to run the following scripts before starting migration from v8.18.7:

Enable the disabled indexes

DECLARE @AlterStatements TABLE (
    Statement NVARCHAR(MAX)
)

INSERT INTO @AlterStatements (Statement)
SELECT 'ALTER INDEX [' + I.name + '] ON [' + T.name + '] REBUILD'
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.type_desc = 'NONCLUSTERED'
AND I.name IS NOT NULL

DECLARE @Statement NVARCHAR(MAX)

DECLARE AlterCursor CURSOR FOR
SELECT Statement FROM @AlterStatements

OPEN AlterCursor
FETCH NEXT FROM AlterCursor INTO @Statement

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_executesql @Statement
    FETCH NEXT FROM AlterCursor INTO @Statement
END

CLOSE AlterCursor
DEALLOCATE AlterCursor

Re-creating umbracoContentVersionCleanupPolicy table

drop table [dbo].[umbracoContentVersionCleanupPolicy]

GO
--drop table umbracoContentVersionCleanupPolicy
CREATE TABLE [dbo].[umbracoContentVersionCleanupPolicy](
    [contentTypeId] [int] NOT NULL,
    [preventCleanup] [bit] NOT NULL,
    [keepAllVersionsNewerThanDays] [int] NULL,
    [keepLatestVersionPerDayForDays] [int] NULL,
    [updated] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[umbracoContentVersionCleanupPolicy]  WITH NOCHECK ADD  CONSTRAINT [FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId] CHECK  (([contentTypeId]<>''))
GO
ALTER TABLE [dbo].[umbracoContentVersionCleanupPolicy] CHECK CONSTRAINT [FK_umbracoContentVersionCleanupPolicy_cmsContentType_nodeId]
GO

umbraco / Umbraco-CMS