U4-3616 - Problem upgrading from 4.11.3 to 7.0RC

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 [34] 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 constraints (Primary Keys, Foreign Keys and Indexes) were found in the database, but are not in the current schema: FK_cmsContent_umbracoNode,FK_cmsContentType_umbracoNode,FK_cmsContentVersion_cmsContent,FK_cmsContentXml_cmsContent,FK_cmsDataType_umbracoNode,FK_cmsDataTypePreValues_cmsDataType,FK_cmsDocument_cmsContent,FK_cmsDocument_cmsTemplate,FK_cmsDocument_umbracoNode,FK_cmsDocumentType_cmsContentType,FK_cmsDocumentType_cmsTemplate,FK_cmsDocumentType_umbracoNode,FK_cmsLanguageText_cmsDictionary,FK_cmsMacroProperty_cmsMacro,FK_cmsMember_cmsContent,FK_cmsMember_umbracoNode,FK_cmsMember2MemberGroup_cmsMember,FK_cmsMember2MemberGroup_umbracoNode,FK_cmsMemberType_cmsContentType,FK_cmsMemberType_umbracoNode,FK_cmsPreviewXml_cmsContent,FK_cmsPreviewXml_cmsContentVersion,FK_cmsPropertyData_cmsPropertyType,FK_cmsPropertyData_umbracoNode,FK_cmsPropertyType_cmsContentType,FK_cmsPropertyType_cmsDataType,FK_cmsPropertyType_cmsTab,FK_cmsStylesheet_umbracoNode,FK_cmsStylesheetProperty_umbracoNode,FK_cmsTab_cmsContentType,FK_cmsTask_cmsTaskType,FK_cmsTask_umbracoNode,FK_umbracoDomains_umbracoNode,FK_umbracoMacroProperty_umbracoMacroPropertyType,FK_umbracoNode_umbracoNode,FK_umbracoRelation_umbracoRelationType,FK_umbracoUser2app_umbracoUser,FK_umbracoUser2NodeNotify_umbracoNode,FK_umbracoUser2NodeNotify_umbracoUser,FK_umbracoUser2NodePermission_umbracoNode,FK_umbracoUser2NodePermission_umbracoUser,FK_user_userType,PK__cmsContentVersio__398D8EEE,PK_cmsContentPreviewXml,PK_cmsTab,PK_domains,PK_language,PK_macro,PK_macroProperty,PK_macroPropertyType,PK_templates,PK_umbracoApp,PK_umbracoAppTree,PK_userType,IX_cmsContentVersion,IX_cmsDataType,IX_cmsDictionary,IX_cmsTaskType,IX_cmsTemplate,IX_umbracoLanguage,IX_umbracoUser

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

1 Attachments

Download umbracotracelog.txt

Comments

Andy Rose 25 Nov 2013, 09:37:05

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 v6.0.0.0. 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.


Martin Griffiths 26 Nov 2013, 09:21:50

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')


Andy Rose 28 Nov 2013, 11:43:31

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


Sergio Mello-Grand 01 Dec 2013, 20:33:08

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!!!


Brian Powell 04 Dec 2013, 13:24:21

@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.


Martin Griffiths 04 Dec 2013, 13:34:36

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!

Its SLACK!

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.


Eric Schrepel 16 Dec 2013, 02:11:45

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.


Brian Powell 16 Dec 2013, 03:38:04

@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.


Eric Schrepel 16 Dec 2013, 19:13:20

@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.


Brian Powell 16 Dec 2013, 19:19:31

@Eric: There are a couple steps involved:

  1. Upgrade your current Umbraco instance to 6.1.6. The upgrade path through 6 should generally work OK, but sometimes the upgrade scripts missed keys or indexes along the way. This is where the upgrades to 7 tend to fail.
  2. Create a new blank Umbraco instance of 6.1.6 in a new database. The new database will have all of the keys, indexes, and field types that the Umbraco 7 expects.
  3. Delete all of the data in the new Umbraco 6.1.6 install so you have only blank tables.
  4. Export the data from your upgraded-to-6.1.6 instance and then import that data into the new-6.1.6 instance.
  5. Run the 7.0.1 upgrade on your new-6.1.6 database. Everything should come along.

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.


Allan Lorentzen 17 Dec 2013, 15:14:43

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'


Brian Powell 17 Dec 2013, 16:04:17

@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.


Allan Lorentzen 18 Dec 2013, 10:17:16

Ok, so here is what I did:

  1. Upgrade current Umbraco instance to 6.1.6
  2. Created a new blank Umbraco 6.1.6 instance with a new database.
  3. In my current instance database: DELETE FROM cmsPropertyData WHERE propertytypeid NOT IN ( SELECT pt.id FROM cmsPropertyType )
  4. In my current instance database: DELETE FROM cmsMember2MemberGroup WHERE Member NOT IN ( SELECT nodeId FROM cmsMember )
  5. Used a tool 'RedGate - SqlCompare' to change the structure of my current database to look exactly like the new 6.1.6 database.
  6. Ran the upgrade to 7.0.1 and the database step went all the way to 100% this time. But next step gave me this error: [InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.String'.] System.Data.SqlClient.SqlBuffer.get_String() +6639569 petapoco_factory_1(IDataReader ) +1157


Allan Lorentzen 18 Dec 2013, 10:45:15

Ok, so here is what I did:

  1. Upgrade current Umbraco instance to 6.1.6
  2. Created a new blank Umbraco 6.1.6 instance with a new database.
  3. In my current instance database: DELETE FROM cmsPropertyData WHERE propertytypeid NOT IN ( SELECT pt.id FROM cmsPropertyType )
  4. In my current instance database: DELETE FROM cmsMember2MemberGroup WHERE Member NOT IN ( SELECT nodeId FROM cmsMember )
  5. Used a tool 'RedGate - SqlCompare' to change the structure of my current database to look exactly like the new 6.1.6 database.
  6. Ran the upgrade to 7.0.1 and the database step went all the way to 100% this time. But next step gave me this error: [InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.String'.] System.Data.SqlClient.SqlBuffer.get_String() +6639569 petapoco_factory_1(IDataReader ) +1157

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'.


Martin Griffiths 18 Dec 2013, 10:56:24

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.


Brian Powell 18 Dec 2013, 17:47:56

@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.


Brian Powell 18 Dec 2013, 17:50:16

@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.


Eric Schrepel 18 Dec 2013, 18:13:22

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.


Allan Lorentzen 19 Dec 2013, 09:36:23

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.


Martin Griffiths 19 Dec 2013, 09:41:34

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!

  • I should add to this, I also want to be able to offer our customers new features and significant bug fixes within the API's and backoffice. More reasons to upgrade.


Brian Powell 07 Jan 2014, 23:16:29

@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.


Allan Lorentzen 14 Jan 2014, 12:51:01

That column is there: controlId (uniqueidentifier, not null)


Brian Powell 17 Jan 2014, 19:39:22

@Allan: I'm not sure in that case.


Sean Wheeler 26 Feb 2014, 17:14:58

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.


Pavel Gurecki 04 Jul 2014, 07:10:43

@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.


Tom Fulton 28 Sep 2014, 21:18:33

Same error as the OP when attempting 6.1.6 -> 7.1.6


andrew shearer 08 Dec 2014, 03:13:27

hi - this is still an issue on 7.1.9. Any plans to fix this in 7.2?


Sebastiaan Janssen 08 Dec 2014, 05:38:24

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)


andrew shearer 08 Dec 2014, 20:38:16

thanks, Sebastiaan. I've upgraded to 720 and this is no longer an issue for me. thanks!


thomen 25 Jun 2015, 05:09:39

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


Tom Fulton 25 Jun 2015, 05:34:49

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!


Priority: Normal

Type: Bug

State: Closed

Assignee:

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions: 7.0.0, 7.0.3, 7.1.1, 7.1.9

Due in version:

Sprint:

Story Points:

Cycle: