Smart group refresh locks issue on 4.7.8rc

Feedback on testing the rc is

I've done some more testing on test-box with the following results:

1. Running the drush command again one minute after you've run it a first time will exit without issue (and without effect) if the first one isn't finished. This is a good result in that one can be confident that running this every minute will not cause any issues itself.
2. It was fairly easy to generate Lock Wait errors (note: NOT deadlock errors) by looking up contacts in a group which is being refreshed by the drush command (and taking a long time). This caused front-end DB Error: unknown error to appear on test-box, where I was the only user.

This second observation is so concerning that I cannot put this into production as it stands. We don't experience that on production right now.

If the cache is cleared via a TRUNCATE and then a user navigates to the Manage Groups screen, it appears to automatically create all smart groups in the cache (which takes a long time, this screen times out when this happens).

I have received 2 backtraces on this - one is by virtue of going to manage groups. When this page is loaded & the contact_cache table is empty fillIfEmpty is called - which is waaayyyy too heavy handed!

The other is going to view a specific group. The first thing worth noting here is that since we have already identified population of the refresh_date to be unreliable it will try to refresh the group regardless of whether the cache is expired. See below the backtrace for a patch that would redress that part.

{{DELETE g
FROM civicrm_group_contact_cache g
WHERE g.group_id IN ( 176 )
[nativecode=1205 ** Lock wait timeout exceeded; try restarting transaction]
[type] => DB_Error
[user_info] =>
DELETE g
FROM civicrm_group_contact_cache g
WHERE g.group_id IN ( 176 )
[nativecode=1205 ** Lock wait timeout exceeded; try restarting transaction]
[to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="
DELETE g
FROM civicrm_group_contact_cache g
WHERE g.group_id IN ( 176 )
[nativecode=1205 ** Lock wait timeout exceeded; try restarting transaction]"]
)

May 26 09:25:55 [info] $backTrace = #0 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Core/Error.php(235): CRM_Core_
Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/packages/DB.php(976): PEAR_Error->PEAR_Error("DB Error: unknown error",
-1, 16, (Array:2), "\nDELETE g\nFROM civicrm_group_contact_cache g\nWHERE g.group_...")
#4 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-1, 16, (Array:2), "\nDELETE
g\nFROM civicrm_group_contact_cache g\nWHERE g.group_...")
#5 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -1, NULL, NULL, "\
nDELETE g\nFROM civicrm_group_contact_cache g\nWHERE g.group_...", "DB_Error", TRUE)
#6 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/packages/DB/mysql.php(895): DB_common->raiseError(-1, NULL, NULL, NULL,
"1205 ** Lock wait timeout exceeded; try restarting transaction")
#7 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/packages/DB/mysql.php(328): DB_mysql->mysqlRaiseError()
#8 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("\nDELETE g\nFR
OM civicrm_group_contact_cache g\nWHERE g.group_...")
#9 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/packages/DB/DataObject.php(2442): DB_common->query("\nDELETE g\nFRO
M civicrm_group_contact_cache g\nWHERE g.group_...")
#10 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/packages/DB/DataObject.php(1634): DB_DataObject->_query("\nDELETE
g\nFROM civicrm_group_contact_cache g\nWHERE g.group_...")
#11 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Core/DAO.php(324): DB_DataObject->query("\nDELETE g\nFROM
civicrm_group_contact_cache g\nWHERE g.group_...")
#12 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Core/DAO.php(1197): CRM_Core_DAO->query("\nDELETE g\nFROM
civicrm_group_contact_cache g\nWHERE g.group_...", TRUE)
#13 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Contact/BAO/GroupContactCache.php(420): CRM_Core_DAO::executeQuery
("\nDELETE g\nFROM civicrm_group_contact_cache g\nWHERE g.group_...", (Array:2))
#14 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Contact/BAO/GroupContactCache.php(686): CRM_Contact_BAO_GroupConta
ctCache::remove((Array:1))
#15 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Contact/BAO/Query.php(3035): CRM_Contact_BAO_GroupContactCache::lo
ad(Object(CRM_Core_DAO))
#16 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Contact/BAO/Query.php(2998): CRM_Contact_BAO_Query->addGroupContac
tCache((Array:1))
#17 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Contact/BAO/Query.php(2959): CRM_Contact_BAO_Query->savedSearch((A
rray:5))
#18 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Contact/BAO/Query.php(1749): CRM_Contact_BAO_Query->group((Array:5
))
#19 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Contact/BAO/Query.php(1967): CRM_Contact_BAO_Query->whereClauseSin
gle((Array:5))
#20 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Contact/BAO/Query.php(495): CRM_Contact_BAO_Query->whereClause()
#21 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Contact/BAO/Query.php(459): CRM_Contact_BAO_Query->initialize()
#22 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Contact/Selector.php(224): CRM_Contact_BAO_Query->__construct((Arr
ay:1), NULL, NULL, FALSE, FALSE, 1, FALSE, TRUE, FALSE, NULL, 1)
#23 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Contact/Form/Search.php(846): CRM_Contact_Selector->__construct(NU
LL, (Array:4), (Array:1), NULL, 256, FALSE, TRUE, "smog", (Array:11))
#24 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Contact/Form/Search/Basic.php(212): CRM_Contact_Form_Search->postP
rocess()
#25 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Contact/Form/Search.php(723): CRM_Contact_Form_Search_Basic->postP
rocess()
#26 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Contact/Form/Search/Basic.php(155): CRM_Contact_Form_Search->prePr
ocess()
#27 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Core/Form.php(455): CRM_Contact_Form_Search_Basic->preProcess()
#28 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Core/QuickForm/Action/Display.php(94): CRM_Core_Form->buildForm()
#29 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action
_Display->perform(Object(CRM_Contact_Form_Search_Basic), "display")
#30 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->hand
le(Object(CRM_Contact_Form_Search_Basic), "display")
#31 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Core/Controller.php(353): HTML_QuickForm_Page->handle("display")
#32 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Core/Invoke.php(312): CRM_Core_Controller->run((Array:3), (Array:0
))
#33 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Core/Invoke.php(86): CRM_Core_Invoke::runItem((Array:13))
#34 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/CRM/Core/Invoke.php(54): CRM_Core_Invoke::_invoke((Array:3))
#35 /var/local/www/mysite/htdocs/profiles/contact/modules/contrib/civicrm/drupal/civicrm.module(489): CRM_Core_Invoke::invoke((Array:3))
#36 [internal function](): civicrm_invoke("group", "search")
#37 /var/local/www/mysite/htdocs/includes/menu.inc(527): call_user_func_array("civicrm_invoke", (Array:2))
#38 /var/local/www/mysite/htdocs/index.php(21): menu_execute_active_handler()
#39

}}

+++ b/CRM/Contact/BAO/GroupContactCache.php
@@ -92,7 +92,7 @@ AND g.is_active = 1
AND (
g.cache_date IS NULL
OR cache_date <= $smartGroupCacheTimeoutDateTime

• OR NOW() >= g.refresh_date
  + OR (g.refresh_date IS NOT NULL AND NOW() >= g.refresh_date)