CiviCRM Community Forums (archive)

Hello! I'm testing an upgrade and have hit a show-stopper issue. The upgrade process itself seems to complete without issue. However while running through my test plan, I noticed that our Smart Group-based ACLs were no longer working.

The smart groups (segmenting the full database (around 170K contacts) by riding custom field) perform normally for fully privileged users, but when used by a access-controlled user (single riding), search only returns the contacts that have been manually added to the smart group--i.e. no results from the "saved search" portion of the smart group.

I tried the obvious next step of constructing a new ACL, with a newly minted smart-group target, and a new access control group. Here's the error I got when trying my first search with this test user:

Database Error Code: Unknown column 'civicrm_group_contact-ACL.group_id' in 'where clause', 1054

SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache-ACL` ON contact_a.id = `civicrm_group_contact_cache-ACL`.contact_id   WHERE  ( ( `civicrm_group_contact-ACL`.group_id IN (1023) AND `civicrm_group_contact-ACL`.status IN ("Added") ) OR `civicrm_group_contact_cache-ACL`.group_id IN (1023) )  AND (contact_a.is_deleted = 0)     [nativecode=1054 ** Unknown column 'civicrm_group_contact-ACL.group_id' in 'where clause']

Bactrace:

$backTrace = /var/www/<snip>/modules/civicrm/CRM/Core/Error.php, backtrace, 205 , handle,
/var/www/<snip>/modules/civicrm/packages/PEAR.php, call_user_func, 931
/var/www/<snip>/modules/civicrm/packages/DB.php, PEAR_Error, 968
/var/www/<snip>/modules/civicrm/packages/PEAR.php, DB_Error, 564
 /var/www/<snip>/modules/civicrm/packages/DB/common.php, raiseError, 1903
/var/www/<snip>/modules/civicrm/packages/DB/mysql.php, raiseError, 898
/var/www/<snip>/modules/civicrm/packages/DB/mysql.php, mysqlRaiseError, 327
/var/www/<snip>/modules/civicrm/packages/DB/common.php, simpleQuery, 1216
/var/www/<snip>/modules/civicrm/packages/DB/DataObject.php, query, 2424
/var/www/<snip>/modules/civicrm/packages/DB/DataObject.php, _query, 1610
/var/www/<snip>/modules/civicrm/CRM/Core/DAO.php, query, 152
/var/www/<snip>/modules/civicrm/CRM/Core/DAO.php, query, 879
/var/www/<snip>/modules/civicrm/CRM/Contact/BAO/Query.php, executeQuery, 3619
/var/www/<snip>/modules/civicrm/CRM/Contact/Selector.php, searchQuery, 869
/var/www/<snip>/modules/civicrm/CRM/Utils/PagerAToZ.php, alphabetQuery, 91
/var/www/<snip>/modules/civicrm/CRM/Utils/PagerAToZ.php, getDynamicCharacters, 117
/var/www/<snip>/modules/civicrm/CRM/Utils/PagerAToZ.php, createLinks, 61
/var/www/<snip>/modules/civicrm/CRM/Contact/Form/Search.php, getAToZBar, 832
/var/www/<snip>/modules/civicrm/CRM/Contact/Form/Search/Basic.php, postProcess, 233
/var/www/<snip>/modules/civicrm/CRM/Core/Form.php, postProcess, 250
/var/www/<snip>/modules/civicrm/CRM/Core/QuickForm/Action/Refresh.php, mainProcess, 79
/var/www/<snip>/modules/civicrm/packages/HTML/QuickForm/Controller.php, perform, 203
/var/www/<snip>/modules/civicrm/packages/HTML/QuickForm/Page.php, handle, 103
/var/www/<snip>/modules/civicrm/CRM/Core/Controller.php, handle, 284
/var/www/<snip>/modules/civicrm/CRM/Core/Invoke.php, run, 223
/var/www/<snip>/modules/civicrm/drupal/civicrm.module, invoke, 347 , civicrm_invoke,
/var/www/drupal-6/includes/menu.inc, call_user_func_array, 349
/var/www/drupal-6/index.php, menu_execute_active_handler, 17

I'd love some guidance on where to look for a solution! I presume that this key functionality is working on a fresh install, so it must be something with my schema post-upgrade? ...

It does!

I looked into this a bit more and it appears that it's the FROM clause that's missing a JOIN to civicrm_group_contact-ACL (or the WHERE is referencing it incorrectly)

Thx for the patch. I added that patch and it did JOIN in some more tables civicrm_address, civicrm_email, etc... but not civicrm_group_contact (aliased to civicrm_group_contact-ACL)

Database Error Code: Unknown column 'civicrm_group_contact-ACL.group_id' in 'where clause', 1054

SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id  LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)  LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)  LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1)  LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id   LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache-ACL` ON contact_a.id = `civicrm_group_contact_cache-ACL`.contact_id   LEFT JOIN civicrm_option_group option_group_gender ON (option_group_gender.name = 'gender') LEFT JOIN civicrm_option_value gender ON (contact_a.gender_id = gender.value AND option_group_gender.id = gender.option_group_id)  LEFT JOIN civicrm_option_group option_group_prefix ON (option_group_prefix.name = 'individual_prefix') LEFT JOIN civicrm_option_value individual_prefix ON (contact_a.prefix_id = individual_prefix.value AND option_group_prefix.id = individual_prefix.option_group_id )  LEFT JOIN civicrm_option_group option_group_suffix ON (option_group_suffix.name = 'individual_suffix') LEFT JOIN civicrm_option_value individual_suffix ON (contact_a.suffix_id = individual_suffix.value AND option_group_suffix.id = individual_suffix.option_group_id )  WHERE  ( ( `civicrm_group_contact-ACL`.group_id IN (1023) AND `civicrm_group_contact-ACL`.status IN ("Added") ) OR `civicrm_group_contact_cache-ACL`.group_id IN (1023) )  AND (contact_a.is_deleted = 0)     [nativecode=1054 ** Unknown column 'civicrm_group_contact-ACL.group_id' in 'where clause']

I notice that the smart groups have different values for "where_tables" between the two versions:

Smart group from 3.3.2:
civicrm_group.where_tables = a:2:{s:15:"civicrm_contact";i:1;s:27:"`civicrm_group_contact-569`";s:118:" LEFT JOIN civicrm_group_contact `civicrm_group_contact-569` ON contact_a.id = `civicrm_group_contact-569`.contact_id ";}
Smart Group from 3.4.7:
civicrm_group.where_tables =  a:2:{s:15:"civicrm_contact";i:1;s:34:"`civicrm_group_contact_cache_1023`";s:138:" LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_1023` ON contact_a.id = `civicrm_group_contact_cache_1023`.contact_id ";}

Matthew Clarke:

Can you apply the patch from this issue:

http://issues.civicrm.org/jira/browse/CRM-9162

(you can get the patch by clicking on the Fisheye tab in the lower section of the issue)

lobo

Thank you indeed! This patch does appear to fix the problem!