Database upgrade fail on 7.1.2 to 7.4 manual upgrade
Having to do a manual upgrade of a 7.1.2 site (that wasn't installed with Nuget!) to 7.4. Bin,umbraco & umbracoclient directories overwritten and all config/*.config files and web.config checked for diffs. Builds ok and runs up to the upgrade screen with the drum. Shortly after hitting "continue" the upgrade fails with the following message in the dialogue:-
The database failed to upgrade. ERROR: The database configuration failed with the following message: 'FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup_id' is not a constraint.
Could not drop constraint. See previous errors. Please check log file for additional information (can be found in '/App_Data/Logs/UmbracoTraceLog.txt')
The final log entry is:-
2016-02-13 11:53:37,237 [P356/D4/T7] ERROR Umbraco.Web.Install.Controllers.InstallApiController - Installation step DatabaseUpgrade failed.
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Umbraco.Web.Install.InstallException: The database failed to upgrade. ERROR: The database configuration failed with the following message: 'FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup_id' is not a constraint.
Could not drop constraint. See previous errors.
Please check log file for additional information (can be found in '/App_Data/Logs/UmbracoTraceLog.txt')
at Umbraco.Web.Install.InstallSteps.DatabaseUpgradeStep.Execute(Object model)
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at Umbraco.Web.Install.Controllers.InstallApiController.ExecuteStep(InstallSetupStep step, JToken instruction)
2016-02-13 11:53:37,237 [P356/D4/T7] INFO Umbraco.Web.Install.Controllers.InstallApiController - Step completed (took 3351ms)
2016-02-13 11:53:37,237 [P356/D4/T7] ERROR Umbraco.Web.Install.Controllers.InstallApiController - An error occurred during installation step DatabaseUpgrade
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Umbraco.Web.Install.InstallException: The database failed to upgrade. ERROR: The database configuration failed with the following message: 'FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup_id' is not a constraint.
Could not drop constraint. See previous errors.
Please check log file for additional information (can be found in '/App_Data/Logs/UmbracoTraceLog.txt')
at Umbraco.Web.Install.InstallSteps.DatabaseUpgradeStep.Execute(Object model)
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at Umbraco.Web.Install.Controllers.InstallApiController.ExecuteStep(InstallSetupStep step, JToken instruction)
at Umbraco.Web.Install.Controllers.InstallApiController.PostPerformInstall(InstallInstructions installModel)
This is after other log entries saying various indices and constraints were found in the DB but not in the correct schema.
We should be able to add the constraint using some SQL though. From what it says it is trying to delete a constraint that doesn't exist. Why does it try to do that rather than just skipping it?
To fix this, run the following SQL statement on your database to add this containt (so that Umbraco con delete it again):
ALTER TABLE [cmsPropertyTypeGroup] ADD CONSTRAINT [FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup_id] FOREIGN KEY ([parentGroupId]) REFERENCES [cmsPropertyTypeGroup]([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;
This has only been tested on SQL CE, but I'm doing the same on SQL Server later.
Hi Asbjørn, thanks for this. Ran the site again after running your SQL statement and it failed again, this time with:-
The database failed to upgrade. ERROR: The database configuration failed with the following message: The object 'FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup' is dependent on column 'parentGroupId'.
ALTER TABLE DROP COLUMN parentGroupId failed because one or more objects access this column. Please check log file for additional information (can be found in '/App_Data/Logs/UmbracoTraceLog.txt')
Are you running on SQL Server or SQL CE? Can you do a Script as CREATE on the table cmsPropertyTypeGroup, so we can see what constraints are in there?
In SQL Server Management Studio or SQL CE Toolbox, right click the table and select Script as > CREATE and then paste the resulting script here (don't run it).
UPDATE: I have reproduced your issue and I'm working on a solution. I'll post once I have figured it out.
This is what I get after having run your Add Constraint script:-
USE [myDB]
GO
/****** Object: Table [dbo].[cmsPropertyTypeGroup] Script Date: 13/02/2016 14:35:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cmsPropertyTypeGroup](
[id] [int] IDENTITY(38,1) NOT NULL,
[contenttypeNodeId] [int] NOT NULL,
[text] [nvarchar](255) NOT NULL,
[sortorder] [int] NOT NULL,
[parentGroupId] [int] NULL,
CONSTRAINT [cmsTab_PK_cmsTab] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[cmsPropertyTypeGroup] WITH CHECK ADD CONSTRAINT [FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup] FOREIGN KEY([parentGroupId])
REFERENCES [dbo].[cmsPropertyTypeGroup] ([id])
GO
ALTER TABLE [dbo].[cmsPropertyTypeGroup] CHECK CONSTRAINT [FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup]
GO
ALTER TABLE [dbo].[cmsPropertyTypeGroup] WITH CHECK ADD CONSTRAINT [FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup_id] FOREIGN KEY([parentGroupId])
REFERENCES [dbo].[cmsPropertyTypeGroup] ([id])
GO
ALTER TABLE [dbo].[cmsPropertyTypeGroup] CHECK CONSTRAINT [FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup_id]
GO
Thanks. I think the problem is that we have two identical constraints with different names: FKcmsPropertyTypeGroupcmsPropertyTypeGroup and FKcmsPropertyTypeGroupcmsPropertyTypeGroup_id (the latter being the name Umbraco expects).
I'm currently working on my own DB, will write back once I have a working solution.
Trying to upgrade 7.2.4 to 7.4.3 I have an error that looks similar to this one:
The database failed to upgrade. ERROR: The database configuration failed with the following message: Cannot insert duplicate key row in object 'dbo.cmsPropertyTypeGroup' with unique index 'IXcmsPropertyTypeGroupUniqueID'. The duplicate key value is (5ef49d6c-a7c9-da9d-9bee-234dce220b1a).
The statement has been terminated. Please check log file for additional information (can be found in '/AppData/Logs/UmbracoTraceLog.txt')
Database upgrade fail on 7.1.2 to 7.4 manual upgrade
Having to do a manual upgrade of a 7.1.2 site (that wasn't installed with Nuget!) to 7.4. Bin,umbraco & umbracoclient directories overwritten and all config/*.config files and web.config checked for diffs. Builds ok and runs up to the upgrade screen with the drum. Shortly after hitting "continue" the upgrade fails with the following message in the dialogue:-
The final log entry is:-
This is after other log entries saying various indices and constraints were found in the DB but not in the correct schema.
Any advice would be appreciated.
Craig
I'm getting this as well upgrading from 7.3.5 to 7.4.0. I reported an issue here: http://issues.umbraco.org/issue/U4-7954
We should be able to add the constraint using some SQL though. From what it says it is trying to delete a constraint that doesn't exist. Why does it try to do that rather than just skipping it?
To fix this, run the following SQL statement on your database to add this containt (so that Umbraco con delete it again):
This has only been tested on SQL CE, but I'm doing the same on SQL Server later.
Hi Asbjørn, thanks for this. Ran the site again after running your SQL statement and it failed again, this time with:-
Are you running on SQL Server or SQL CE? Can you do a Script as CREATE on the table cmsPropertyTypeGroup, so we can see what constraints are in there?
In SQL Server Management Studio or SQL CE Toolbox, right click the table and select Script as > CREATE and then paste the resulting script here (don't run it).
UPDATE: I have reproduced your issue and I'm working on a solution. I'll post once I have figured it out.
This is what I get after having run your Add Constraint script:-
Thanks. I think the problem is that we have two identical constraints with different names: FKcmsPropertyTypeGroupcmsPropertyTypeGroup and FKcmsPropertyTypeGroupcmsPropertyTypeGroup_id (the latter being the name Umbraco expects).
I'm currently working on my own DB, will write back once I have a working solution.
Yes, deleting the other constraint did the trick for me. Try running this SQL to do it:
Fantastic! The site now works :)
Many thanks
Craig
Hi,
Trying to upgrade 7.2.4 to 7.4.3 I have an error that looks similar to this one:
The database failed to upgrade. ERROR: The database configuration failed with the following message: Cannot insert duplicate key row in object 'dbo.cmsPropertyTypeGroup' with unique index 'IXcmsPropertyTypeGroupUniqueID'. The duplicate key value is (5ef49d6c-a7c9-da9d-9bee-234dce220b1a). The statement has been terminated. Please check log file for additional information (can be found in '/AppData/Logs/UmbracoTraceLog.txt')
Any idea?
I'm getting the same error as AvihayBit while attempting to manually upgrade from 7.3.8 to 7.4.3.
Anyone know how to deal with this?
I solve it by run this query before update
is working on a reply...