Eileen has uploaded a new change for review. ( https://gerrit.wikimedia.org/r/377385 )
Change subject: CRM-20722: 'Lock wait timeout exceeded' error triggered on smart group cache rebuild ...................................................................... CRM-20722: 'Lock wait timeout exceeded' error triggered on smart group cache rebuild This patch is merged upstream & I am submitting it & https://github.com/civicrm/civicrm-core/pull/10943 to our codebase. Doing this a) syncs us with upstream in the area we are working on b) adds some code consolidation making it easier for us to tinker with the impact of cache tables. Note that this change switches from one sql call to many when clearing groups from the group_contact_cache table. I'm thinking this is probably not a problem for us as we have not that many groups (& hopefully we can further reduce them). Also the apparent COMMIT is a bit of a a myth - CRM_Core_Transaction does weird complex transaction nesting stuff... Change-Id: I3d6505b04af1134884f660d71316afb2490221d1 --- M CRM/Contact/BAO/GroupContactCache.php 1 file changed, 45 insertions(+), 41 deletions(-) git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/crm/civicrm refs/changes/85/377385/1 diff --git a/CRM/Contact/BAO/GroupContactCache.php b/CRM/Contact/BAO/GroupContactCache.php index e0d041a..8875275 100644 --- a/CRM/Contact/BAO/GroupContactCache.php +++ b/CRM/Contact/BAO/GroupContactCache.php @@ -300,12 +300,12 @@ * @todo remove last call to this function from outside the class then make function protected, * enforce groupID as an array & remove non group handling. * - * @param int $groupID + * @param int $groupIDs * the groupID to delete cache entries, NULL for all groups. * @param bool $onceOnly * run the function exactly once for all groups. */ - public static function remove($groupID = NULL, $onceOnly = TRUE) { + public static function remove($groupIDs = NULL, $onceOnly = TRUE) { static $invoked = FALSE; // typically this needs to happy only once per instance @@ -316,21 +316,21 @@ if ( $onceOnly && $invoked && - $groupID == NULL + $groupIDs == NULL ) { return; } - if ($groupID == NULL) { + if ($groupIDs == NULL) { $invoked = TRUE; } - elseif (is_array($groupID)) { - foreach ($groupID as $gid) { + elseif (is_array($groupIDs)) { + foreach ($groupIDs as $gid) { unset(self::$_alreadyLoaded[$gid]); } } - elseif ($groupID && array_key_exists($groupID, self::$_alreadyLoaded)) { - unset(self::$_alreadyLoaded[$groupID]); + elseif ($groupIDs && array_key_exists($groupIDs, self::$_alreadyLoaded)) { + unset(self::$_alreadyLoaded[$groupIDs]); } $refresh = NULL; @@ -340,7 +340,7 @@ 2 => array(self::getRefreshDateTime(), 'String'), ); - if (!isset($groupID)) { + if (!isset($groupIDs)) { if ($smartGroupCacheTimeout == 0) { $query = " DELETE FROM civicrm_group_contact_cache @@ -352,7 +352,6 @@ "; } else { - $query = " DELETE gc FROM civicrm_group_contact_cache gc @@ -372,44 +371,49 @@ AND refresh_date IS NULL "; } - } - elseif (is_array($groupID)) { - $groupIDs = implode(', ', $groupID); - $query = " -DELETE g -FROM civicrm_group_contact_cache g -WHERE g.group_id IN ( $groupIDs ) -"; - $update = " -UPDATE civicrm_group g -SET cache_date = null, - refresh_date = null -WHERE id IN ( $groupIDs ) -"; + + CRM_Core_DAO::executeQuery($query, $params); + if ($refresh) { + CRM_Core_DAO::executeQuery($refresh, $params); + } + // also update the cache_date for these groups + CRM_Core_DAO::executeQuery($update, $params); } else { - $query = " -DELETE g -FROM civicrm_group_contact_cache g -WHERE g.group_id = %1 -"; - $update = " -UPDATE civicrm_group g -SET cache_date = null, - refresh_date = null -WHERE id = %1 -"; - $params = array(1 => array($groupID, 'Integer')); + foreach ((array) $groupIDs as $groupID) { + self::clearGroupContactCache($groupID); + } } + } + + /** + * Function to clear group contact cache and reset the corresponding + * group's cache and refresh date + * + * @param int $groupID + * + */ + public static function clearGroupContactCache($groupID) { + $transaction = new CRM_Core_Transaction(); + $query = " + DELETE g + FROM civicrm_group_contact_cache g + WHERE g.group_id = %1 "; + + $update = " + UPDATE civicrm_group g + SET cache_date = null, refresh_date = null + WHERE id = %1 "; + + $params = array( + 1 => array($groupID, 'Integer'), + ); CRM_Core_DAO::executeQuery($query, $params); - - if ($refresh) { - CRM_Core_DAO::executeQuery($refresh, $params); - } - // also update the cache_date for these groups CRM_Core_DAO::executeQuery($update, $params); + + $transaction->commit(); } /** -- To view, visit https://gerrit.wikimedia.org/r/377385 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I3d6505b04af1134884f660d71316afb2490221d1 Gerrit-PatchSet: 1 Gerrit-Project: wikimedia/fundraising/crm/civicrm Gerrit-Branch: master Gerrit-Owner: Eileen <emcnaugh...@wikimedia.org> _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
