Custom Field Group logs missing - help!!!

[joanne]

We were losing the logging on address, email and phone tables only AFAIK, but I may not have checked any custom_value tables.

Thanks to Dave J. for tracking down what may be the cause.  Now I just have to remember to rebuild the triggers if I tamper with our custom fields.

[DaveFF]

I've just experimented with editing custom fields on my local installation.

• Dump and inspect the output of SHOW TRIGGERS. Observed that a whole bunch of custom field tables, including civicrm_value_contact_reference_9, and a few core tables (address, email, im, phone, website) have the timestamp update in their triggers, but not the logging.
• Ran /civicrm/menu/rebuild&reset=1&triggerRebuild=1.
• Dump and inspect the output of SHOW TRIGGERS. Observed that all core and custom tables, including civicrm_value_contact_reference_9, have logging triggers.
• Added a new custom field to the group held in civicrm_value_contact_reference_9.
• Dump and inspect the output of SHOW TRIGGERS. Observed that a whole bunch of custom field tables (but not civicrm_value_contact_reference_9), and a few core tables (address, email, im, phone, website) have lost the logging in their triggers, but still have the timestamp update.

Also done a quick grep in 4.4.6 core for calls to dropTriggers. The only times I can see dropTriggers without being rebuilt/created soon afterwards, are in a couple of places under CRM/Upgrade, and in CRM_Core_DAO::dropTriggers itself calling CRM_Logging_Schema::dropTriggers.

[DaveFF]

I've found the bug, and am working towards a PR. To filter out the problem is a less-than-one-line change to CRM_Core_DAO::triggerRebuild. To fix the problem at source involves changing CRM_Contact_BAO_Contact::triggerInfo.

When a custom field is added/edited, triggers are rebuilt on that table. triggerRebuild requests triggers for that table to be generated, by CRM_Logging_Schema (logging triggers), CRM_Core_I18n_Schema, CRM_Contact_BAO_Contact (contact timestamp update triggers, possibly CRM-10554) and hook_civicrm_triggerInfo.

CRM_Contact_BAO_Contact was regenerating timestamp update triggers for all Contact-related core and custom tables, even when only one table was requested. When these were applied they replaced the existing triggers on said Contact-related tables with timestamp update triggers only, as the logging triggers were (correctly) not being regenerated for tables that weren't asked for in the call to triggerRebuild.

The tiny change to CRM_Core_DAO, which is enough in practice, passes the tableName parameter through to createTriggers. This, if non-NULL, causes createTriggers to filter out regenerated triggers for tables other than the requested one, so the only table that gets its triggers replaced is the requested one (and they are complete).

Fixing the problem at source involves making CRM_Contact_BAO_Contact not regenerate triggers it wasn't asked for. I've got a fix for that but still need to test it.

Many thanks to Dave J at Circle Interactive. His input was instrumental in finding out that the problem was related to missing triggers.

[DaveFF]

Pull request here.

[DaveFF]

And here's a PR against 4.4.

We have only tested on Drupal with 4.4.6, as that's what we're using. Details of that are above- basically re-did the show triggers, trigger rebuild, show triggers, add a new custom field, show triggers. Both the CRM_Core_DAO change and the CRM_Contact_BAO_Contact change gave good results there compared to before the fix.

There shouldn't be anything CMS-specific in the patch, but if anyone's willing to test on Joomla or WordPress your feedback would be welcome.

[mariolita]

I will add that when Do Not Contact Until calls dropTriggers and triggerRebuild it passes the name of its own table- ie. shouldn't be dropping all triggers. It does this because of something similar to CRM-13587 (the patch for that does the same thing).