Sign in Register
Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Steve 8 posts 77 karma points
    Dec 07, 2016 @ 16:19
    Steve
    0

    Issue upgrading Umbraco from 7.2.4 to latest version

    We are having the following error when trying to upgrade our Umbraco installation (Server Core 2012 R2 / MySQL Database):

    2016-12-07 15:49:25,532 [P5184/D5/T42] 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: An error occurred running a schema migration but the changes could not be rolled back.

    Error: You can't specify target table 'cmsTemplate' for update in FROM clause. In some cases, it may be required that the database be restored to it's original state before running this upgrade process again.

    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 System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters) at Umbraco.Web.Install.Controllers.InstallApiController.ExecuteStep(InstallSetupStep step, JToken instruction)

    Any ideas how to resolve?

    Many thanks,

    Steve

    Copy Link
  • Steve 8 posts 77 karma points
    Dec 07, 2016 @ 17:01
    Steve
    0

    Have also added this to Stack Overflow:

    http://stackoverflow.com/questions/41022667/umbraco-upgrade-issue-cant-specify-target-table-for-update-in-from-clause

    Copy Link
  • Domenic 42 posts 128 karma points
    Dec 08, 2016 @ 20:50
    Domenic
    0

    Hi Steve,

    The issue seems related to MySQL. Any chance you can port to MS SQL and retry? What does UmbracoTraceLog say?

    Copy Link
  • Steve 8 posts 77 karma points
    Dec 14, 2016 @ 13:38
    Steve
    0

    Hi Domenic,

    Thanks for the reply! Unfortunately we do not have a MS SQL server available to port to currently.

    Also unsure how successful copying the MySQL database to MS SQL would be - are you able to recommend the best tool / method to use?

    Here is the UmbracoTraceLog (Looks like as you said the query used to update is incompatible with MySQL?):

    2016-12-07 15:47:18,531 [P5184/D5/T53] INFO  Umbraco.Core.CoreBootManager - Umbraco 7.5.6 application starting on DESKTOP
 2016-12-07 15:47:18,550 [P5184/D5/T53] INFO  Umbraco.Core.PluginManager - Determining hash of code files on disk
 2016-12-07 15:47:18,584 [P5184/D5/T53] INFO  Umbraco.Core.PluginManager - Hash determined (took 34ms)
 2016-12-07 15:47:18,596 [P5184/D5/T53] INFO  Umbraco.Core.MainDom - Acquiring MainDom...
 2016-12-07 15:47:18,596 [P5184/D5/T53] INFO  Umbraco.Core.MainDom - Acquired MainDom.
 2016-12-07 15:47:18,600 [P5184/D5/T53] INFO  Umbraco.Core.PluginManager - Starting resolution types of umbraco.interfaces.IApplicationStartupHandler
 2016-12-07 15:47:18,607 [P5184/D5/T53] INFO  Umbraco.Core.PluginManager - Completed resolution of types of umbraco.interfaces.IApplicationStartupHandler, found 0 (took 6ms)
 2016-12-07 15:47:18,623 [P5184/D5/T53] INFO  Umbraco.Core.PluginManager - Starting resolution types of Umbraco.Core.PropertyEditors.IPropertyEditorValueConverter
 2016-12-07 15:47:18,623 [P5184/D5/T53] INFO  Umbraco.Core.PluginManager - Completed resolution of types of Umbraco.Core.PropertyEditors.IPropertyEditorValueConverter, found 0 (took 0ms)
 2016-12-07 15:47:18,623 [P5184/D5/T53] INFO  Umbraco.Core.PluginManager - Starting resolution types of Umbraco.Core.PropertyEditors.IPropertyValueConverter
 2016-12-07 15:47:18,625 [P5184/D5/T53] INFO  Umbraco.Core.PluginManager - Completed resolution of types of Umbraco.Core.PropertyEditors.IPropertyValueConverter, found 0 (took 1ms)
 2016-12-07 15:47:18,629 [P5184/D5/T53] INFO  Umbraco.Core.PluginManager - Starting resolution types of Umbraco.Web.Mvc.SurfaceController
 2016-12-07 15:47:18,631 [P5184/D5/T53] INFO  Umbraco.Core.PluginManager - Completed resolution of types of Umbraco.Web.Mvc.SurfaceController, found 0 (took 1ms)
 2016-12-07 15:47:18,631 [P5184/D5/T53] INFO  Umbraco.Core.PluginManager - Starting resolution types of Umbraco.Web.WebApi.UmbracoApiController
 2016-12-07 15:47:18,636 [P5184/D5/T53] INFO  Umbraco.Core.PluginManager - Completed resolution of types of Umbraco.Web.WebApi.UmbracoApiController, found 0 (took 4ms)
 2016-12-07 15:47:18,650 [P5184/D5/T53] INFO  Umbraco.Core.PluginManager - Starting resolution types of Umbraco.Core.Media.IThumbnailProvider
 2016-12-07 15:47:18,651 [P5184/D5/T53] INFO  Umbraco.Core.PluginManager - Completed resolution of types of Umbraco.Core.Media.IThumbnailProvider, found 0 (took 0ms)
 2016-12-07 15:47:18,651 [P5184/D5/T53] INFO  Umbraco.Core.PluginManager - Starting resolution types of Umbraco.Core.Media.IImageUrlProvider
 2016-12-07 15:47:18,651 [P5184/D5/T53] INFO  Umbraco.Core.PluginManager - Completed resolution of types of Umbraco.Core.Media.IImageUrlProvider, found 0 (took 0ms)
 2016-12-07 15:47:19,348 [P5184/D5/T53] INFO  Umbraco.Core.CoreBootManager - Umbraco application startup complete (took 869ms)
 2016-12-07 15:47:19,641 [P5184/D5/T51] INFO  Umbraco.Core.Sync.ApplicationUrlHelper - ApplicationUrl: http://localhost:10920/umbraco (UmbracoModule request)
 2016-12-07 15:47:20,477 [P5184/D5/T51] INFO  Umbraco.Web.Install.Controllers.InstallApiController - Executing installation step: Permissions
 2016-12-07 15:47:20,522 [P5184/D5/T51] INFO  Umbraco.Web.Install.Controllers.InstallApiController - Step completed (took 45ms)
 2016-12-07 15:47:20,618 [P5184/D5/T50] INFO  Umbraco.Web.Install.Controllers.InstallApiController - Executing installation step: Upgrade
 2016-12-07 15:47:20,619 [P5184/D5/T50] INFO  Umbraco.Web.Install.Controllers.InstallApiController - Step completed (took 0ms)
 2016-12-07 15:47:20,681 [P5184/D5/T51] INFO  Umbraco.Web.Install.Controllers.InstallApiController - Executing installation step: DatabaseInstall
 2016-12-07 15:47:20,682 [P5184/D5/T51] INFO  Umbraco.Core.DatabaseContext - Database configuration status: Started
 2016-12-07 15:47:20,683 [P5184/D5/T51] INFO  Umbraco.Core.DatabaseContext - Database requires upgrade
 2016-12-07 15:47:20,683 [P5184/D5/T51] INFO  Umbraco.Web.Install.Controllers.InstallApiController - Step completed (took 2ms)
 2016-12-07 15:47:20,727 [P5184/D5/T48] INFO  Umbraco.Web.Install.Controllers.InstallApiController - Executing installation step: DatabaseUpgrade
 2016-12-07 15:47:20,727 [P5184/D5/T48] INFO  Umbraco.Web.Install.InstallSteps.DatabaseUpgradeStep - Running 'Upgrade' service
 2016-12-07 15:47:20,729 [P5184/D5/T48] INFO  Umbraco.Core.DatabaseContext - Database upgrade started
 2016-12-07 15:47:20,731 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Initializing database migrations
 2016-12-07 15:47:20,733 [P5184/D5/T48] INFO  Umbraco.Core.PluginManager - Starting resolution types of Umbraco.Core.Persistence.Migrations.IMigration
 2016-12-07 15:47:20,799 [P5184/D5/T48] INFO  Umbraco.Core.PluginManager - Completed resolution of types of Umbraco.Core.Persistence.Migrations.IMigration, found 0 (took 65ms)
 2016-12-07 15:47:20,886 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'AddRelationTypeForDocumentOnDelete' to context
 2016-12-07 15:47:20,894 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'CreateCacheInstructionTable' to context
 2016-12-07 15:47:21,127 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'MigrateAndRemoveTemplateMasterColumn' to context
 2016-12-07 15:47:21,590 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'MigrateStylesheetDataToFile' to context
 2016-12-07 15:47:21,597 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'RemoveStylesheetDataAndTables' to context
 2016-12-07 15:47:21,604 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'RemoveLanguageLocaleColumn' to context
 2016-12-07 15:47:21,613 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'UpdateUniqueIdToHaveCorrectIndexType' to context
 2016-12-07 15:47:21,620 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'AddPublicAccessTables' to context
 2016-12-07 15:47:21,632 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'MovePublicAccessXmlDataToDb' to context
 2016-12-07 15:47:21,641 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'RemoveHelpTextColumn' to context
 2016-12-07 15:47:21,645 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'AddExternalLoginsTable' to context
 2016-12-07 15:47:21,654 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'AddUserColumns' to context
 2016-12-07 15:47:21,659 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'AddMigrationTable' to context
 2016-12-07 15:47:21,734 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'AddUniqueIdPropertyTypeColumn' to context
 2016-12-07 15:47:21,789 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'AddForeignKeysForLanguageAndDictionaryTables' to context
 2016-12-07 15:47:21,799 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'RemoveUmbracoLoginsTable' to context
 2016-12-07 15:47:21,816 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'AddServerRegistrationColumnsAndLock' to context
 2016-12-07 15:47:21,925 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'CleanUpCorruptedPublishedFlags' to context
 2016-12-07 15:47:21,936 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'UpdateUserLanguagesToIsoCode' to context
 2016-12-07 15:47:21,936 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'EnsureMigrationsTableIdentityIsCorrect' to context
 2016-12-07 15:47:21,946 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'AddDataDecimalColumn' to context
 2016-12-07 15:47:21,955 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'AddUniqueIdPropertyTypeGroupColumn' to context
 2016-12-07 15:47:21,971 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'EnsureContentTypeUniqueIdsAreConsistent' to context
 2016-12-07 15:47:21,980 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'FixListViewMediaSortOrder' to context
 2016-12-07 15:47:21,997 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'RemoveParentIdPropertyTypeGroupColumn' to context
 2016-12-07 15:47:22,002 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'AddUmbracoDeployTables' to context
 2016-12-07 15:47:22,003 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'RemoveStylesheetDataAndTablesAgain' to context
 2016-12-07 15:47:22,210 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'UpdateUniqueIndexOnCmsPropertyData' to context
 2016-12-07 15:47:22,210 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'AddRedirectUrlTable' to context
 2016-12-07 15:47:22,210 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Added UPGRADE migration 'UpdateAllowedMediaTypesAtRoot' to context
 2016-12-07 15:47:22,218 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Executing sql statement 1: INSERT INTO `umbracoRelationType` (`dual`,`parentObjectType`,`childObjectType`,`name`,`alias`) VALUES (0,'c66ba18e-eaf3-4cff-8a22-41b16d66a972','c66ba18e-eaf3-4cff-8a22-41b16d66a972','Relate Parent Document On Delete','relateParentDocumentOnDelete');

 2016-12-07 15:47:22,237 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Executing sql statement 2: CREATE TABLE `umbracoCacheInstruction` (`id` int(11) NOT NULL AUTO_INCREMENT,
`utcStamp` TIMESTAMP NOT NULL,
`jsonInstruction` LONGTEXT NOT NULL,
`originated` NVARCHAR(500) NOT NULL,
PRIMARY KEY NONCLUSTERED (`id`))

 2016-12-07 15:47:22,406 [P5184/D5/T48] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Executing sql statement 3: UPDATE cmsTemplate SET master = NULL WHERE `master` IS NOT NULL AND `master` NOT IN (SELECT nodeId FROM (SELECT * FROM cmsTemplate a) b)

 2016-12-07 15:47:22,410 [P5184/D5/T48] ERROR Umbraco.Core.Persistence.UmbracoDatabase - Database exception occurred
MySql.Data.MySqlClient.MySqlException (0x80004005): You can't specify target table 'cmsTemplate' for update in FROM clause
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery()
   at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass1.<ExecuteNonQueryWithRetry>b__0()
   at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
   at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy)
   at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy retryPolicy)
   at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command)
   at Umbraco.Core.Persistence.Database.Execute(String sql, Object[] args)
 2016-12-07 15:47:22,413 [P5184/D5/T48] ERROR Umbraco.Core.DatabaseContext - Database configuration failed
Umbraco.Core.Persistence.Migrations.DataLossException: An error occurred running a schema migration but the changes could not be rolled back. Error: You can't specify target table 'cmsTemplate' for update in FROM clause. In some cases, it may be required that the database be restored to it's original state before running this upgrade process again. ---> MySql.Data.MySqlClient.MySqlException: You can't specify target table 'cmsTemplate' for update in FROM clause
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery()
   at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass1.<ExecuteNonQueryWithRetry>b__0()
   at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
   at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy)
   at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy retryPolicy)
   at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command)
   at Umbraco.Core.Persistence.Database.Execute(String sql, Object[] args)
   at Umbraco.Core.Persistence.Migrations.MigrationRunner.ExecuteMigrations(IMigrationContext context, Database database)
   at Umbraco.Core.Persistence.Migrations.MigrationRunner.Execute(Database database, DatabaseProviders databaseProvider, Boolean isUpgrade)
   --- End of inner exception stack trace ---
   at Umbraco.Core.Persistence.Migrations.MigrationRunner.Execute(Database database, DatabaseProviders databaseProvider, Boolean isUpgrade)
   at Umbraco.Core.Persistence.Migrations.MigrationRunner.Execute(Database database, Boolean isUpgrade)
   at Umbraco.Core.DatabaseContext.UpgradeSchemaAndData(IMigrationEntryService migrationEntryService)
 2016-12-07 15:47:22,416 [P5184/D5/T48] 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:
cmsstylesheet,cmsstylesheetproperty,umbracouserlogins,umbracoAccess,umbracoAccessRule,umbracoCacheInstruction,umbracoExternalLogin,umbracoMigration,umbracoDeployChecksum,umbracoDeployDependency,umbracoRedirectUrl

The following columns were found in the database, but are not in the current schema:
cmscontentversion,LanguageLocale,cmspropertytype,helpText,cmspropertytypegroup,parentGroupId,cmsstylesheet,nodeId,cmsstylesheet,filename,cmsstylesheet,content,cmsstylesheetproperty,nodeId,cmsstylesheetproperty,stylesheetPropertyEditor,cmsstylesheetproperty,stylesheetPropertyAlias,cmsstylesheetproperty,stylesheetPropertyValue,cmstemplate,master,umbracouserlogins,contextID,umbracouserlogins,userID,umbracouserlogins,timeout,cmsPropertyTypeGroup,uniqueID,cmsPropertyType,UniqueID,cmsPropertyData,dataDecimal,umbracoUser,securityStampToken,umbracoUser,failedLoginAttempts,umbracoUser,lastLockoutDate,umbracoUser,lastPasswordChangeDate,umbracoUser,lastLoginDate,umbracoServer,isMaster,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,umbracoDeployChecksum,id,umbracoDeployChecksum,entityType,umbracoDeployChecksum,entityGuid,umbracoDeployChecksum,entityPath,umbracoDeployChecksum,localChecksum,umbracoDeployChecksum,compositeChecksum,umbracoDeployDependency,sourceId,umbracoDeployDependency,targetId,umbracoDeployDependency,mode,umbracoRedirectUrl,id,umbracoRedirectUrl,contentKey,umbracoRedirectUrl,createDateUtc,umbracoRedirectUrl,url,umbracoRedirectUrl,urlHash

The following indexes were found in the database, but are not in the current schema:
IX_cmsPropertyData,IX_umbracoUserLogins_Index,IX_cmsPropertyTypeGroupUniqueID,IX_cmsPropertyTypeUniqueID,IX_umbracoAccess_nodeId,IX_umbracoAccessRule,IX_umbracoMigration,IX_umbracoDeployChecksum,IX_umbracoRedirectUrl

Please note that the constraints could not be validated because the current dataprovider is MySql.

 2016-12-07 15:47:22,417 [P5184/D5/T48] 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: An error occurred running a schema migration but the changes could not be rolled back. Error: You can't specify target table 'cmsTemplate' for update in FROM clause. In some cases, it may be required that the database be restored to it's original state before running this upgrade process again.
 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 System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
   at Umbraco.Web.Install.Controllers.InstallApiController.ExecuteStep(InstallSetupStep step, JToken instruction)
 2016-12-07 15:47:22,418 [P5184/D5/T48] INFO  Umbraco.Web.Install.Controllers.InstallApiController - Step completed (took 1690ms)
 2016-12-07 15:47:22,418 [P5184/D5/T48] 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: An error occurred running a schema migration but the changes could not be rolled back. Error: You can't specify target table 'cmsTemplate' for update in FROM clause. In some cases, it may be required that the database be restored to it's original state before running this upgrade process again.
 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 System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
   at Umbraco.Web.Install.Controllers.InstallApiController.ExecuteStep(InstallSetupStep step, JToken instruction)
   at Umbraco.Web.Install.Controllers.InstallApiController.PostPerformInstall(InstallInstructions installModel)
    Copy Link
  • Steve 8 posts 77 karma points
    Dec 14, 2016 @ 13:47
    Steve
    0

    It looks like the issue is due to the following MySQL statement:

    UPDATE cmsTemplate SET master = NULL WHERE 'master' IS NOT NULL AND 'master' NOT IN (SELECT nodeId FROM (SELECT * FROM cmsTemplate a) b)

    Any ideas on how to make the above query compatible with MySQL?

    Copy Link
  • Steve 8 posts 77 karma points
    Dec 14, 2016 @ 15:31
    Steve
    0

    Downloading the Umbraco source now from GitHub - going to try debugging in Visual Studio:

    • Umbraco.Core.Persistence.Migrations.MigrationRunner
    • Umbraco.Core.Persistence.UmbracoDatabase

    Hopefully will be able to determine the queries that have issues and then attempt to convert them into MySQL format..

    Is there a specific version of Visual Studio that works best with this release of Umbraco? Planning to use VS 2015!

    All the best,

    Steve

    Copy Link
  • Domenic 42 posts 128 karma points
    Jan 03, 2017 @ 16:38
    Domenic
    0

    I use VS2015 Community and it works a charm. Please let me know if you are able to figure this out.

    Regards,
    Dom

    Copy Link
  • Steve 8 posts 77 karma points
    Jan 04, 2017 @ 12:49
    Steve
    0

    Thanks Domenic!

    Have now managed to build Umbraco from source using VS2015 Community (had a few issues doing this from within a locked down networked environment!).

    Working through the debugging process now trying to figure out how the SQL queries for the migrations are generated.

    After I have some idea I will attempt to change the following query:

    UPDATE cmsTemplate SET master = NULL WHERE `master` IS NOT NULL AND `master` NOT IN (SELECT nodeId FROM (SELECT * FROM cmsTemplate a) b)

    Into something that does not produce the error:

    "You can't specify target table 'cmsTemplate' for update in FROM clause"

    The issue seems to be related to the MySQL limitation (http://dev.mysql.com/doc/refman/5.6/en/update.html):

    "You cannot update a table and select from the same table in a subquery."

    Not an SQL guru so would be great if someone could confirm how the query could be rewritten!

    Many Thanks

    Copy Link
  • Steve 8 posts 77 karma points
    Jan 04, 2017 @ 14:02
    Steve
    0

    The issue is due to the following migration:

    https://github.com/umbraco/Umbraco-CMS/blob/dev-v7/src/Umbraco.Core/Persistence/Migrations/Upgrades/TargetVersionSevenThreeZero/MigrateAndRemoveTemplateMasterColumn.cs

    Have posted a SO question for the SQL query re-write: http://stackoverflow.com/questions/41464376/mysql-you-cant-specify-target-table-x-for-update-in-from-clause

    Thanks

    Copy Link
  • Steve 8 posts 77 karma points
    Jan 04, 2017 @ 15:04
    Steve
    0

    Hi All,

    Does this seem like a reasonable solution to the MySQL code?

    UPDATE cmsTemplate c1 LEFT JOIN cmsTemplate c2
ON c1.`master` = c2.nodeId
SET c1.`master` = NULL
WHERE c2.nodeId IS NULL;

    Thanks

    Copy Link

  • This forum is in read-only mode while we transition to the new forum.

    You can continue this topic on the new forum by tapping the "Continue discussion" link below.

Please Sign in or register to post replies