Details
-
Type:
Improvement
-
Status: Done/Fixed
-
Priority:
Major
-
Resolution: Incomplete
-
Affects Version/s: 4.3.5
-
Fix Version/s: Unscheduled
-
Component/s: Core CiviCRM
-
Labels:None
-
Documentation Required?:None
-
Funding Source:Needs Funding
Description
We use MySQL's binary logging, so CiviCRM does not have access to create triggers. We won't be granting super access or using log_bin_trust_function_creators, that is too much for something web-accessible, for us.
If trigger support is not enabled / present the upgrade script can give the admin the sql needed to create the triggers so the DB admin can create them manually.
Attachments
Activity
> Installing/upgrading/removing a CMS extension (Drupal module/Joomla extension/WP plugin)
IMO CiviCRM checking whether a CMS extension implements ANY CiviCRM-related hooks before doing this would be a bonus here.
It's a bit of a drag that enabling or running DB update on unrelated modules can cause CiviCRM to spin its wheels dropping and recreating an identical set of triggers each time ...
http://issues.civicrm.org/jira/browse/CRM-13796 is pretty much this - seems I've watched the trigger dance at least twice now 
I've added some comments to CRM-13796. Agree that CRM-13194 and CRM-13796 are related (because both involve the trigger rebuild) but different (because 13194 seems motivated by security while 13796 seems motivated by performance). In both cases, it helps if we can correctly determine whether the list of triggers has actually changed (rather than assuming that it's changed – like we do now).
Agreed with Tim. This issue is that we simply don't want to grant elevated DB permissions, needed when using triggers on DB clusters, to something web-accessible.
There's a table in the "5.0 Saloon" for those who have thoughts on DB triggers. http://forum.civicrm.org/index.php/topic,31846.0.html
An important nuance: Triggers may be setup during installation or upgrade, but they can also be setup at runtime – e.g. when an admin enables or disables logging, the triggers must be updated. When using hook_civicrm_triggerInfo, a Civi extension (or Drupal module, etal) can define its own triggers – these need to be manipulated whenever the extension is installed/upgraded/removed.
Specifically, triggers are rebuilt on these occasions:
+ Upgrading CiviCRM
+ Installing/upgrading/removing a Civi extension
+ Installing/upgrading/removing a CMS extension (Drupal module/Joomla extension/WP plugin)
+ Visiting /civicrm/cacheclear?triggerRebuild=1 or /civicrm/menu/rebuild?triggerRebuild=1
+ Executing the "System.flush" API (e.g. "drush cvapi system.flush triggers=1")
A minimal solution might be something like:
1. Add a new page, "/civicrm/admin/triggers.sql", which simply prints out the SQL for dropping & creating all triggers.
2. In CRM_Core_DAO::triggerRebuild(), add a call like:
if (! isPermittedToManageTriggers())
{ $url = CRM_Utils_System::url('civicrm/admin/triggers.sql'); CRM_Core_Session::setStatus(ts('Please update the SQL trigger configuration by downloading and executing the <a href="%1">the SQL trigger definitions</a>.', array(1 => $url))); }This will unfortunately produce false-positives. If the admin enables a typical Drupal module (which doesn't use hook_civicrm_triggerInfo), then there's no real need to reset the triggers, but it would never-the-less display the alert. The false-positives seem acceptable if we think that only a handful of professional sysadmins will be using this feature. If the false-positives are a problem, then we'd have to do a bit more work (eg call "SHOW TRIGGERS LIKE 'civicrm_%'" and compare with the expected triggers).