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


An option is to disable the trigger, run your upgrade and re-create it at the end.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.