U4-7120 - SQL Fail upgrading om 7.2.4 to 7.3 due to index on master template column

Created by jonas carlson 21 Sep 2015, 12:45:02 Updated by Shannon Deminick 12 May 2016, 09:19:10

After many sorrow I finally get to the point of upgrading the DB.

Upgrading from 7.2.4 to 7.3 RC.

2015-09-21 14:40:42,821 [P22112/D3/T12] 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: The index 'IX_master' is dependent on column 'master'. ALTER TABLE DROP COLUMN master 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')

Comments

jonas carlson 25 Sep 2015, 12:02:04

Tried tha latest Nightly and the problem still occured.

Found this http://issues.umbraco.org/issue/U4-7059 but it still does not work.


jonas carlson 25 Sep 2015, 12:03:13

2015-09-25 13:58:42,300 [P15424/D4/T27] INFO Umbraco.Core.DatabaseContext - The database schema validation produced the following summary: The following tables were found in the database, but are not in the current schema: umbracoUserLogins,UFRecordFields,UFRecordDataString,UFRecordDataLongString,UFRecordDataInteger,UFRecordDataBit,UFRecordDataDateTime,UFUserSecurity,UFUserFormSecurity,UFRecords,CMSImportState,CMSImportScheduledTask,CMSImportRelation,CMSImportMediaRelation,CMSImportScheduledItems,CMSImportCustomRelation,UCUserSettings,umbracoAccess,umbracoAccessRule,umbracoCacheInstruction,umbracoExternalLogin,umbracoMigration

The following columns were found in the database, but are not in the current schema: umbracoUserLogins,contextID,umbracoUserLogins,userID,umbracoUserLogins,timeout,cmsTemplate,master,UFRecordFields,Key,UFRecordFields,FieldId,UFRecordFields,Record,UFRecordFields,Alias,UFRecordFields,DataType,UFRecordDataString,Id,UFRecordDataString,Key,UFRecordDataString,Value,UFRecordDataLongString,Id,UFRecordDataLongString,Key,UFRecordDataLongString,Value,cmsContentVersion,LanguageLocale,UFRecordDataInteger,Id,UFRecordDataInteger,Key,UFRecordDataInteger,Value,UFRecordDataBit,Id,UFRecordDataBit,Key,UFRecordDataBit,Value,UFRecordDataDateTime,Id,UFRecordDataDateTime,Key,UFRecordDataDateTime,Value,UFUserSecurity,User,UFUserSecurity,ManageDataSources,UFUserSecurity,ManagePreValueSources,UFUserSecurity,ManageWorkflows,UFUserSecurity,ManageForms,UFUserFormSecurity,Id,UFUserFormSecurity,User,UFUserFormSecurity,Form,UFUserFormSecurity,HasAccess,UFUserFormSecurity,AllowInEditor,UFUserFormSecurity,SecurityType,UFRecords,Id,UFRecords,Form,UFRecords,Created,UFRecords,Updated,UFRecords,CurrentPage,UFRecords,UmbracoPageId,UFRecords,IP,UFRecords,MemberKey,UFRecords,UniqueId,UFRecords,State,UFRecords,RecordData,CMSImportState,Id,CMSImportState,UniqueIdentifier,CMSImportState,Name,CMSImportState,ImportType,CMSImportState,ImportState,CMSImportState,Recursive,CMSImportState,Parent,CMSImportScheduledTask,ScheduleId,CMSImportScheduledTask,ScheduleGUID,CMSImportScheduledTask,ImportStateGUID,CMSImportScheduledTask,ScheduledTaskName,CMSImportScheduledTask,NotifyEmailAddress,CMSImportScheduledTask,ExecuteEvery,CMSImportScheduledTask,ExecuteDays,CMSImportScheduledTask,ExecuteHour,CMSImportScheduledTask,ExecuteMinute,CMSImportScheduledTask,ImportAsUser,CMSImportRelation,Id,CMSImportRelation,UmbracoID,CMSImportRelation,UmbracoParentId,CMSImportRelation,DataSourceKey,CMSImportMediaRelation,Id,CMSImportMediaRelation,UmbracoMediaId,CMSImportMediaRelation,SourceUrl,CMSImportScheduledItems,ScheduledItemId,CMSImportScheduledItems,ScheduleldTaskId,CMSImportScheduledItems,ScheduledOn,CMSImportScheduledItems,ExecutedOn,CMSImportScheduledItems,InProgress,CMSImportCustomRelation,CustomRelationId,CMSImportCustomRelation,UmbracoId,CMSImportCustomRelation,Alias,CMSImportCustomRelation,DataSourceKey,CMSImportCustomRelation,RelatedId,UCUserSettings,User,UCUserSettings,Key,UCUserSettings,Value,cmsPropertyType,helpText,cmsPropertyType,UniqueID,umbracoUser,securityStampToken,umbracoUser,failedLoginAttempts,umbracoUser,lastLockoutDate,umbracoUser,lastPasswordChangeDate,umbracoUser,lastLoginDate,umbracoAccess,id,umbracoAccess,nodeId,umbracoAccess,loginNodeId,umbracoAccess,noAccessNodeId,umbracoAccess,createDate,umbracoAccess,updateDate,umbracoAccessRule,id,umbracoAccessRule,accessId,umbracoAccessRule,ruleValue,umbracoAccessRule,ruleType,umbracoAccessRule,createDate,umbracoAccessRule,updateDate,umbracoCacheInstruction,id,umbracoCacheInstruction,utcStamp,umbracoCacheInstruction,jsonInstruction,umbracoCacheInstruction,originated,umbracoExternalLogin,id,umbracoExternalLogin,userId,umbracoExternalLogin,loginProvider,umbracoExternalLogin,providerKey,umbracoExternalLogin,createDate,umbracoMigration,id,umbracoMigration,name,umbracoMigration,createDate,umbracoMigration,version

The following constraints (Primary Keys, Foreign Keys and Indexes) were found in the database, but are not in the current schema: FK_cmsTemplate_cmsTemplate,FK_UFRecordDataBit_UFRecordFields_Key,FK_UFRecordDataDateTime_UFRecordFields_Key,FK_UFRecordDataInteger_UFRecordFields_Key,FK_UFRecordDataLongString_UFRecordFields_Key,FK_UFRecordDataString_UFRecordFields_Key,FK_cmsDictionary_cmsDictionary_id,FK_cmsLanguageText_umbracoLanguage_id,FK_umbracoAccess_umbracoNode_id,FK_umbracoAccess_umbracoNode_id1,FK_umbracoAccess_umbracoNode_id2,FK_umbracoAccessRule_umbracoAccess_id,PK_CMSImportCustomRelation,PK_CMSImportMediaRelation,PK_CMSImportRelation,PK_CMSImportScheduledItems,PK_CMSImportScheduledTask,PK_CMSImportState,PK_cmsPreviewXml,PK_UCUserSettings,PK_UFRecordDataBit,PK_UFRecordDataDateTime,PK_UFRecordDataInteger,PK_UFRecordDataLongString,PK_UFRecordDataString,PK_UFRecordFields,PK_UFRecords,PK_cmsContentPreviewXml,PK_umbracoAccess,PK_umbracoAccessRule,PK_umbracoCacheInstruction,PK_umbracoExternalLogin,PK_umbracoMigration,IX_umbracoNodeTrashed,IX_umbracoNodeParentId,IX_umbracoNodeUniqueID,IX_umbracoNodeObjectType,IX_cmsContentType,IX_cmsContentType_icon,IX_cmsTemplate_nodeId,IX_cmsContent,IX_cmsContentVersion_ContentId,IX_cmsContentVersion_VersionId,IX_cmsDocument,IX_cmsDocument_published,IX_cmsDocument_newest,IX_cmsDataType_nodeId,IX_cmsDictionary_id,IX_umbracoLanguage_languageISOCode,IX_umbracoLog,IX_cmsMacroPropertyAlias,IX_cmsMacroProperty_Alias,IX_cmsPropertyTypeUniqueID,IX_cmsPropertyData,IX_cmsPropertyData_1,IX_cmsPropertyData_2,IX_cmsPropertyData_3,IX_cmsTags,IX_umbracoUser_userLogin,IX_cmsTaskType_alias,IX_computerName,IX_umbracoServer_isActive,IX_umbracoAccess_nodeId,IX_umbracoAccessRule,IX_umbracoMigration

The following indexes were found in the database, but are not in the current schema: IX_childContentTypeId,IX_AllowedId,IX_Id,IX_ContentId,IX_contentTypeNodeId,IX_UniqueId,IX_cmsMacro_Alias,IX_macro,IX_Member,IX_contentTypeId,IX_dataTypeId,IX_nodeId,IX_master,IX_databit_recordfield,IX_datadatetime_recordfield,IX_datainteger_recordfield,IX_datalongstring_recordfield,IX_datastring_recordfield,Id,IX_umbracoNode_nodeObjectType_id_trashed_parentID_nodeUser_level_path_sortOrder_uniqueID_text_createDate,IX_userType,IX_user,IX_userId,IX_umbracoUserLogins_Index,IX_umbracoNodeObjectType,IX_cmsMacroPropertyAlias,IX_cmsPropertyTypeUniqueID,IX_cmsPropertyData,IX_cmsPropertyData_3,IX_cmsTaskType_alias,IX_umbracoServer_isActive,IX_umbracoAccess_nodeId,IX_umbracoAccessRule,IX_umbracoMigration

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


Shannon Deminick 25 Sep 2015, 12:30:01

The only way that I can attempt to fix this is if you give me your db since all other dbs work, it could be that you have some corrupted data in there that we need to look at. You can attach your zipped db here and set Visible To: "HQ"


Shannon Deminick 25 Sep 2015, 12:40:59

yup sure, whatever works best for you


Shannon Deminick 29 Sep 2015, 09:55:38

Thanks, fixed in latest commit which will be included in 7.3 release.


Bruce 06 Oct 2015, 15:41:53

Adding this info to this ticket as I get something similar on the 7.3 release but for a different table. The upgrade is from 7.2.1 -> 7.3. Upgrade fails with similar message to above but it complains about the CMSTemplate table and column master (cannot Alter Table etc). To fix this I had to manually remove the relationship "cmsTemplate_FK_cmsTemplate_cmsTemplate" from the CMSTemplate database table. Once that relationship was removed, upgrade works correctly so I assume the issue is that the upgrade script needs to drop the relationship before removing the (now redundant) master column. Hope this is of help to anyone who gets a similar issue


Johan Benjaminsson 12 May 2016, 09:11:10

Thanks Bruce! Helped me alot. I hade to remove cmsPropertyTypeGroup_FK_cmsPropertyTypeGroup_cmsPropertyTypeGroup_id too for it to work. I upgraded from 7.2 to 7.4.3.


Shannon Deminick 12 May 2016, 09:19:10

Hey guys, if you have steps to replicate and it's not the exact issue mentioned here then please open a new issue with steps to reproduce so we can get it fixed. thanks!


Priority: Major

Type: Bug

State: Fixed

Assignee: Shannon Deminick

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions:

Due in version: 7.3.0

Sprint:

Story Points:

Cycle: