Answers.Body: <p>An option is to disable the trigger, run your upgrade and re-create it at the end.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-03-06T08:36:09.520Z
Answers.LastActivityDate: 2018-03-06T08:36:09.520Z
Answers.UserDisplayName: Xavier
Body: <p>In our set-up we have custom fields associated to activities, the value of which is computed periodically by scheduled job. The job executes a query that reads something like:</p>
  
  <p><code>UPDATE civicrm_value_something sth
    JOIN (
      SELECT
        a.id, someexpression() as value
      FROM civicrm_activity a
      JOIN sometables
      WHERE somecondition
    ) r ON r.id = sth.entity_id
  SET sth.somefield = r.value;</code></p>
  
  <p>This was working fine until today when we migrated from 4.7.24 to 4.7.30, which introduces some new triggers on <code>civicrm_activity</code> table. The problem is that <a href="https://dev.mysql.com/doc/refman/5.7/en/stored-program-restrictions.html" rel="nofollow noreferrer">MySQL does not support executing triggers that update a table used (even just for read) in the statement that cause the trigger</a>. So the query fails with this error:</p>
  
  <blockquote>
    <p>[nativecode=1442 ** Can't update table 'civicrm_activity' in stored function/trigger 
    because it is already used by statement which invoked this stored function/trigger.]</p>
  </blockquote>
  
  <p>The only solution I can see is to split the query and store the result of the sub-query in a temporary table. This is not desirable because it is less convenient, less performant (if the subquery fits in memory) and less atomic.</p>
  
  <p>Has someone faced a similar problem and come up with a good solution? </p>
  
Tags: <activities><mysql>
Title: Can't use activity table in join query for update
ThreadText: # Can't use activity table in join query for update
  
  (Asked by: username_0 on 2018-03-05)
  
  In our set-up we have custom fields associated to activities, the value of which is computed periodically by scheduled job. The job executes a query that reads something like:
  
  `UPDATE civicrm_value_something sth
  JOIN (
  SELECT
  a.id, someexpression() as value
  FROM civicrm_activity a
  JOIN sometables
  WHERE somecondition
  ) r ON r.id = sth.entity_id
  SET sth.somefield = r.value;`
  
  This was working fine until today when we migrated from 4.7.24 to 4.7.30, which introduces some new triggers on `civicrm_activity` table. The problem is that [MySQL does not support executing triggers that update a table used (even just for read) in the statement that cause the trigger](https://dev.mysql.com/doc/refman/5.7/en/stored-program-restrictions.html). So the query fails with this error:
  
  > [nativecode=1442 \*\* Can't update table 'civicrm\_activity' in stored function/trigger
  > because it is already used by statement which invoked this stored function/trigger.]
  
  The only solution I can see is to split the query and store the result of the sub-query in a temporary table. This is not desirable because it is less convenient, less performant (if the subquery fits in memory) and less atomic.
  
  Has someone faced a similar problem and come up with a good solution?
  
  **Tags:** activities, mysql
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2018-03-06. Score: 1
  
  An option is to disable the trigger, run your upgrade and re-create it at the end.
  
  
  ---
  
  