We have moved to GitHub Issues
Created by Gionata Aladino Canova 20 Nov 2013, 10:46:45 Updated by Tom Fulton 25 Jun 2015, 05:34:49
Is duplicated by: U4-3798
Relates to: U4-3783
Relates to: U4-3784
Hi, I tried to upgrade from 4.11.3 to 7RC and update database failed with error:
The database configuration failed with the following message: 'DF_cmsMacroProperty_macroPropertyHidden' non è un vincolo. Impossibile eliminare il vincolo. Vedere gli errori precedenti. Please check log file for additional information (can be found in '/App_Data/Logs/UmbracoTraceLog.txt')
This is last part of log:
in Umbraco.Core.DatabaseContext.CreateDatabaseSchemaAndDataOrUpgrade() 2013-11-20 11:32:33,876  INFO Umbraco.Core.DatabaseContext - [Thread 33] The database schema validation produced the following summary: The following tables were found in the database, but are not in the current schema: umbracoApp,cmsMacroPropertyType,cmsTab,umbracoAppTree,cmsPropertyTypeGroup,cmsContentType2ContentType,umbracoServer
The following columns were found in the database, but are not in the current schema: umbracoApp,sortOrder,umbracoApp,appAlias,umbracoApp,appIcon,umbracoApp,appName,umbracoApp,appInitWithTreeAlias,cmsContentType,masterContentType,cmsMacroPropertyType,id,cmsMacroPropertyType,macroPropertyTypeAlias,cmsMacroPropertyType,macroPropertyTypeRenderAssembly,cmsMacroPropertyType,macroPropertyTypeRenderType,cmsMacroPropertyType,macroPropertyTypeBaseType,cmsTab,id,cmsTab,contenttypeNodeId,cmsTab,text,cmsTab,sortorder,umbracoUser,userDefaultPermissions,umbracoUser,defaultToLiveEditing,cmsPropertyType,tabId,cmsMacroProperty,macroPropertyHidden,cmsMacroProperty,macroPropertyType,umbracoAppTree,treeSilent,umbracoAppTree,treeInitialize,umbracoAppTree,treeSortOrder,umbracoAppTree,appAlias,umbracoAppTree,treeAlias,umbracoAppTree,treeTitle,umbracoAppTree,treeIconClosed,umbracoAppTree,treeIconOpen,umbracoAppTree,treeHandlerAssembly,umbracoAppTree,treeHandlerType,umbracoAppTree,action,cmsDataType,controlId,cmsTags,parentId
The following unknown constraints (Primary Keys, Foreign Keys and Indexes) were found in the database, but are not in the current schema: cmsTags_cmsTagRelationship,umbracoNode_cmsTagRelationship
I've am also getting this issue. I am starting with v4.7.2 and upgrading to 6.1.6 followed by and upgrade to v7RC which results in the same error here. I've done a bit of investigation and it seems that the constraint was renamed as of v18.104.22.168. A clean install from here will result in the new constraint name. However upgrading from a v4.* to v6* does not result in this constraint being renamed. I've raised this in the [forum|http://our.umbraco.org/forum/getting-started/installing-umbraco/46251-Database-error-during-upgrade-to-v7] and someone has suggested a SQL fix however this should be addressed in the upgrade.
The SQL upgrade scripts from 4.11.x -> 6.x either don't exist or were poorly written and not tested enough. I went from 6.1.6 to 7.0 attempting to fix foreign key names manually then eventually hit this road block...
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_cmsTagRelationship_cmsContent'. Could not create constraint. See previous errors. Please check log file for additional information (can be found in '/App_Data/Logs/UmbracoTraceLog.txt')
There is a thread running in our.umbraco related to this and a few sql scripts have been suggested for getting around these upgrade woes. I don't think this is anywhere near a solution but it may help lead the way. http://our.umbraco.org/forum/getting-started/installing-umbraco/46251-Database-error-during-upgrade-to-v7
Still not solved even if raised in RC. The new DB migration scripts are more strict than the previous for 4.7 to 6, and now a lot of people will have problems for a very simple thing to fix. Such a wonderful piece of software unusable for stupid db scripts!!!
@Martin and @Andy: I ended up taking a new 6.1.6 database, exporting its schema, then importing the data from my upgraded-to-6.1.6 database into that. On the MySQL side I've had a couple issues to address with field positions within the table that changed and a switch from bit to tinyint fields that were causing problems with the export-import, but this worked and got me to a position where I could do the upgrade to 7.0.0.
Hi Brian Judging by the comments in the item I linked (U4-3784). It's pretty clear the schema has been allowed to run ragged on anything but a fresh install. In view of the fact Umbraco continued to function after each upgrade, I was completely unaware schema changes weren't being handled to sync with the schema in a fresh install! Should I be surprised...NO! But that doesn't annoy me any less!
Brian, I will follow your advice at some point and go through the painstaking process of matching data with a brand new schema. As i'd be quite surprised if this is given any kind of priority. Thanks for the advice.
Same issue here, upgrading from 6.1.6 to 7.0.0 (downloaded from http://our.umbraco.org/contribute/releases/700 today), using SQL Server 2008 R2. Hope this issue stays live; have spent a couple days trying to upgrade to 7 with various errors popping up here and there. Thought I was so close with this last attempt, then this error.
@Eric: Things have gotten so out of whack with the previous versions being upgraded along the way I don't think you're going to get a clean upgrade. The best bet seems to be to create a blank new 6.1.6 database, import your data into it, then upgrade that database to 7.
@Brian, not to sound dumb, but if you had time to slightly expand on your last suggestion, I've never tried that approach and not quite clear the exact steps I'd have to take to create the new db, import, then upgrade. I appreciate your responses thus far for sure.
@Eric: There are a couple steps involved:
If you're running MySQL, there is a new bug in 7.0.1 at the end of the installation process where it tries to clear the XML cache tables. I have a pull request in to fix it.
Good idea Brian Powell, but I am unable to do that. I have data in my old db that violates some of the new foreign keys. For examples putting data into the new table 'cmsPropertyData' that now has a foregin key referencing the table 'cmsPropertyType' will fail, as there are rows in 'cmsPropertyData' with propertytypeid's that do not exist as id in 'cmsPropertyType'
@Allan: It sounds like your tables may have some orphaned data that need cleaned up in that case. You can trace things back, but I'm guessing the properties with invalid propertytypeid values aren't valid anyway.
Ok, so here is what I did:
Ok, so here is what I did:
Running the upgrade again causes the database step to halt at 90% with the message: The database configuration failed with the following message: Invalid column name 'controlId'.
I still think (especially in view of recent comments & suggestions) that there should be an attempt to re-align databases by the core team...It's clearly not simple to upgrade databases.
@Martin: In other posts, the core team has indicated they eventually plan to look at schema validation/cleanup tools but that is not a high priority right now. It's definitely not a show-stopper.
@Allan: If the database got to 100% and you got the error afterwards, it is probably a case something failed in one of the cleanup tasks. It would be helpful if you can post the tracelog section from near the error to help narrow where the problem occurred. You may just be able to update the version number in web.config and move on since the database schema itself has already been updated.
Once you've started a database upgrade, you can't re-run the upgrade on that same database. The database will not be in a form consistent with what was expected from the old version number listed in web.config.
I understand it's a major upgrade, and only ask the Umbraco team to put a more clear statement on the v7 page about the complications. I would likely have waited longer to upgrade, had I not seen text like "database upgrades and changes will happen automatically during install", etc. Even for our site which has no custom properties or back-end code, it gives db errors during the upgrade.
It's such a fab-looking upgrade (have used it on some new test sites with no problems) that we're really looking forward to pulling our 6.1.6 site into it. Thanks, Brian, for detailing the workaround steps; we're not quite ready to attempt it but useful info.
Thank you very much for taking the time to look at this problem. Here is the trace log around the time of the upgrade and following error. I hope it makes sense to someone and can provide some info. Keep in mind I did the 6 steps I described above and it might not have been the correct way to do this.
Ok, so not a showstopper for new installs, but definitely for up-graders, well ones who don't want to manually hack away at their databases!
Why do I constantly get the feeling that far too much emphasis with Umbraco is put on "new projects"? Hey it's fine, use v7 for your latest and greatest client win, but don't expect an easy upgrade path once you've settled on it! Oh and keep your fingers crossed your code doesn't get dropped too!
I've had to work so hard to keep our installs and codebase usable while and it's actually important to note that Umbraco 4.0.4 which for many will be a distant memory....actually suffers from backoffice UI issues in more recent versions of Chrome and Internet Explorer! So upgrading is important!
@Allan: Judging by the error in your stack trace, I'd verify you have a controlId column (that exact name) in your cmsDataType table. It seems to be squawking that it can't find this column.
That column is there: controlId (uniqueidentifier, not null)
@Allan: I'm not sure in that case.
I have been struggling with this and I think the "Invalid column name 'controlId'." message is due to the upgrade being started but crashing out. Before I start the upgrade from 6.1.6 I have the controlId column but once it has failed if I try and start the upgrade again the column is gone so I always get this message.
@Allan @Sean have you managed to fix "Invalid column name 'controlId'" issue?
I am currently upgrading from 6.2.1 to 7.1.4 and I got the same error upgrading production environment (all ok in dev environments).
The strange thing is that I get "Invalid column name 'controlId'" error after I do all manual pre-release steps (basically uninstalling not compatible packages), if I go to install script right after copying files - I get:
Umbraco.Web.Install.InstallException: The database failed to upgrade. ERROR: The database configuration failed with the following message: Exception has been thrown by the target of an invocation.
Can't figure out what the problem is, any help would be appreciated.
Same error as the OP when attempting 6.1.6 -> 7.1.6
hi - this is still an issue on 7.1.9. Any plans to fix this in 7.2?
In 7.2.0 we first check if this constraint exists before dropping it. You can try adding it first in 7.1.9 and lower:
ALTER TABLE [dbo].[cmsMacroProperty] ADD CONSTRAINT [DF_cmsMacroProperty_macroPropertyHidden] DEFAULT ('0') FOR [macroPropertyHidden]
Commit that fixes this is here: https://github.com/umbraco/Umbraco-CMS/commit/7b99efb061f0aad74cd8b74e77168dde6ee7827c (around line 42)
thanks, Sebastiaan. I've upgraded to 720 and this is no longer an issue for me. thanks!
Hi I'm trying to upgrade from 6.2.4 to 7.2 and have exactly the same message "Invalid column name 'controlId'" issue? . None of the fixes above work
Hi @thomen, I recently ran into the same
controlId error. You should be able to get more detail from the UmbracoTraceLog.
In my case, it was because of [https://github.com/umbraco/Umbraco-CMS/blob/dev-v7/src/Umbraco.Core/Persistence/Migrations/Upgrades/TargetVersionSeven/DropControlIdColumn.cs this migration] which tries to drop the
cmsDataType.controlId column. The reason was that I had already tried to run the upgrader once and it failed due to another issue. After fixing that issue and running again, I ran into this controlId error — because this migration had already run on my first attempt, thus there was no column to drop. So to fix, I started the upgrade over with a fresh DB backup of the v4 site, making sure to fix the original issue before starting the upgrade.
Hope that helps!
Backwards Compatible: True
Affected versions: 7.0.0, 7.0.3, 7.1.1, 7.1.9
Due in version: