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
a.id, someexpression() as value
FROM civicrm_activity a
) 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. 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?