Umbraco random 500 internal server error,You can't specify target table 'umbracoCacheInstruction' for update in FROM clause

Question

i am using Umbraco 7.3.7 with Mysql database, i am getting random 500 Internal Server Errors on various resources (refreshing may fix it for that resource, but then random other resources then error). As this is my personal development machine, I have set the root folder permissions to allow full access to everyone, so can't see how it is denied access (the fact that some resources work would also suggest it's not permissions).

things i have tired:

reinstalled iis express,webmatrix and umbraco.
full access to every user.
enable 32 bit applications true in DefaultAppPool.

Software configuration windows 10 pro 64bit umbraco 7.3.7 IIS express 8.0

Exception Details: MySql.Data.MySqlClient.MySqlException: You can't specify target table 'umbracoCacheInstruction' for update in FROM clause

Stack Trace: 

[MySqlException (0x80004005): You can't specify target table 'umbracoCacheInstruction' for update in FROM clause]
   MySql.Data.MySqlClient.MySqlStream.ReadPacket() +272
   MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) +68
   MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) +17
   MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) +110
   MySql.Data.MySqlClient.MySqlDataReader.NextResult() +783
   MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +1564
   MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() +118
   StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() +60
   Umbraco.Core.Persistence.<>c__DisplayClass1.<ExecuteNonQueryWithRetry>b__0() +55
   Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction(Func`1 func) +170
   Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy) +118
   Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy retryPolicy) +52
   Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command) +48
   Umbraco.Core.Persistence.Database.Execute(String sql, Object[] args) +180
   Umbraco.Core.Persistence.Database.Execute(Sql sql) +40
   Umbraco.Core.Sync.DatabaseServerMessenger.PruneOldInstructions() +423
   Umbraco.Core.Sync.DatabaseServerMessenger.Sync() +387
   Umbraco.Web.BatchedDatabaseServerMessenger.UmbracoModule_RouteAttempt(Object sender, RoutableAttemptEventArgs e) +32
   Umbraco.Web.UmbracoModule.OnRouteAttempt(RoutableAttemptEventArgs args) +22
   Umbraco.Web.UmbracoModule.ProcessRequest(HttpContextBase httpContext) +256
   Umbraco.Web.UmbracoModule.<Init>b__8(Object sender, EventArgs e) +80
   System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +141
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +69

Details from Event Viewer:

Log Name:      Application
Source:        ASP.NET 4.0.30319.0
Date:          2/10/2016 5:40:09 PM
Event ID:      1309
Task Category: Web Event
Level:         Warning
Keywords:      Classic
User:          N/A
Computer:      james
Description:
Event code: 3005 
Event message: An unhandled exception has occurred. 
Event time: 2/10/2016 5:40:09 PM 
Event time (UTC): 2/10/2016 12:10:09 PM 
Event ID: 98b3588189ee485baba087681a23ce1c 
Event sequence: 56 
Event occurrence: 6 
Event detail code: 0 

Application information: 
    Application domain: /LM/W3SVC/2/ROOT-1-130995795051477306 
    Trust level: Full 
    Application Virtual Path: / 
    Application Path: C:\Users\Administrator\Documents\UmbracoCms.7.3.7\ 
    Machine name: JAMES 

Process information: 
    Process ID: 9380 
    Process name: iisexpress.exe 
    Account name: JAMES\Administrator 

Exception information: 
    Exception type: MySqlException 
    Exception message: You can't specify target table 'umbracoCacheInstruction' 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.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.Database.Execute(Sql sql)
   at Umbraco.Core.Sync.DatabaseServerMessenger.PruneOldInstructions()
   at Umbraco.Core.Sync.DatabaseServerMessenger.Sync()
   at Umbraco.Web.BatchedDatabaseServerMessenger.UmbracoModule_RouteAttempt(Object sender, RoutableAttemptEventArgs e)
   at Umbraco.Web.UmbracoModule.OnRouteAttempt(RoutableAttemptEventArgs args)
   at Umbraco.Web.UmbracoModule.ProcessRequest(HttpContextBase httpContext)
   at Umbraco.Web.UmbracoModule.<Init>b__8(Object sender, EventArgs e)
   at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)



Request information: 
    Request URL: http://localhost:17871/umbraco/RestServices/ScheduledPublish/Index 
    Request path: /umbraco/RestServices/ScheduledPublish/Index 
    User host address: ::1 
    User:  
    Is authenticated: False 
    Authentication Type:  
    Thread account name: JAMES\Administrator 

Thread information: 
    Thread ID: 11 
    Thread account name: JAMES\Administrator 
    Is impersonating: False 
    Stack trace:    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.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.Database.Execute(Sql sql)
   at Umbraco.Core.Sync.DatabaseServerMessenger.PruneOldInstructions()
   at Umbraco.Core.Sync.DatabaseServerMessenger.Sync()
   at Umbraco.Web.BatchedDatabaseServerMessenger.UmbracoModule_RouteAttempt(Object sender, RoutableAttemptEventArgs e)
   at Umbraco.Web.UmbracoModule.OnRouteAttempt(RoutableAttemptEventArgs args)
   at Umbraco.Web.UmbracoModule.ProcessRequest(HttpContextBase httpContext)
   at Umbraco.Web.UmbracoModule.<Init>b__8(Object sender, EventArgs e)
   at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)


Custom event details: 

Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="ASP.NET 4.0.30319.0" />
    <EventID Qualifiers="32768">1309</EventID>
    <Level>3</Level>
    <Task>3</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2016-02-10T12:10:09.000000000Z" />
    <EventRecordID>37763</EventRecordID>
    <Channel>Application</Channel>
    <Computer>james</Computer>
    <Security />
  </System>
  <EventData>
    <Data>3005</Data>
    <Data>An unhandled exception has occurred.</Data>
    <Data>2/10/2016 5:40:09 PM</Data>
    <Data>2/10/2016 12:10:09 PM</Data>
    <Data>98b3588189ee485baba087681a23ce1c</Data>
    <Data>56</Data>
    <Data>6</Data>
    <Data>0</Data>
    <Data>/LM/W3SVC/2/ROOT-1-130995795051477306</Data>
    <Data>Full</Data>
    <Data>/</Data>
    <Data>C:\Users\Administrator\Documents\UmbracoCms.7.3.7\</Data>
    <Data>JAMES</Data>
    <Data>
    </Data>
    <Data>9380</Data>
    <Data>iisexpress.exe</Data>
    <Data>JAMES\Administrator</Data>
    <Data>MySqlException</Data>
    <Data>You can't specify target table 'umbracoCacheInstruction' for update in FROM clause
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32&amp; affectedRow, Int64&amp; insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32&amp; affectedRows, Int64&amp; 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.ExecuteNonQuery()
   at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery()
   at Umbraco.Core.Persistence.PetaPocoCommandExtensions.&lt;&gt;c__DisplayClass1.&lt;ExecuteNonQueryWithRetry&gt;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.Database.Execute(Sql sql)
   at Umbraco.Core.Sync.DatabaseServerMessenger.PruneOldInstructions()
   at Umbraco.Core.Sync.DatabaseServerMessenger.Sync()
   at Umbraco.Web.BatchedDatabaseServerMessenger.UmbracoModule_RouteAttempt(Object sender, RoutableAttemptEventArgs e)
   at Umbraco.Web.UmbracoModule.OnRouteAttempt(RoutableAttemptEventArgs args)
   at Umbraco.Web.UmbracoModule.ProcessRequest(HttpContextBase httpContext)
   at Umbraco.Web.UmbracoModule.&lt;Init&gt;b__8(Object sender, EventArgs e)
   at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&amp; completedSynchronously)

</Data>
    <Data>http://localhost:17871/umbraco/RestServices/ScheduledPublish/Index</Data>
    <Data>/umbraco/RestServices/ScheduledPublish/Index</Data>
    <Data>::1</Data>
    <Data>
    </Data>
    <Data>False</Data>
    <Data>
    </Data>
    <Data>JAMES\Administrator</Data>
    <Data>11</Data>
    <Data>JAMES\Administrator</Data>
    <Data>False</Data>
    <Data>   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32&amp; affectedRow, Int64&amp; insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32&amp; affectedRows, Int64&amp; 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.ExecuteNonQuery()
   at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery()
   at Umbraco.Core.Persistence.PetaPocoCommandExtensions.&lt;&gt;c__DisplayClass1.&lt;ExecuteNonQueryWithRetry&gt;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.Database.Execute(Sql sql)
   at Umbraco.Core.Sync.DatabaseServerMessenger.PruneOldInstructions()
   at Umbraco.Core.Sync.DatabaseServerMessenger.Sync()
   at Umbraco.Web.BatchedDatabaseServerMessenger.UmbracoModule_RouteAttempt(Object sender, RoutableAttemptEventArgs e)
   at Umbraco.Web.UmbracoModule.OnRouteAttempt(RoutableAttemptEventArgs args)
   at Umbraco.Web.UmbracoModule.ProcessRequest(HttpContextBase httpContext)
   at Umbraco.Web.UmbracoModule.&lt;Init&gt;b__8(Object sender, EventArgs e)
   at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&amp; completedSynchronously)
</Data>
  </EventData>
</Event>

Anybody else seen this issue? and know of any fixes?

Never used MySql with Umbraco myself, but this looks a bit like a bug in Umbraco, yeah? So consider creating an issue here (you have to login first): http://issues.umbraco.org/ — Jannik Anker, Feb 11 '16 at 09:50

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

This looks like a similar MySQL issue (MySQL #1093 - You can't specify target table 'giveaways' for update in FROM clause)

It looks like Umbraco / MySQL setups have a bug when trying to delete old records in a table based on a select based on a column in the very same table.

We are also using a MySQL / Umbraco setup for a client and am having a similar issue when trying to upgrade to latest Umbraco version:

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

Have posted SO question specific to upgrading in case the issues are not linked: — Steve, Dec 07 '16 at 16:34
http://stackoverflow.com/questions/41022667/umbraco-upgrade-issue-cant-specify-target-table-for-update-in-from-clause — Steve, Dec 07 '16 at 16:35

Umbraco random 500 internal server error,You can't specify target table 'umbracoCacheInstruction' for update in FROM clause

1 Answers1