3

I just updated from 4.6.3 to 4.6.5, on Joomla. I have a number of Smart Groups which worked fine before, but now inconsistently fail to load contacts or settings, giving a database error instead. The error seems related to building the cache, so I assume the inconsistency comes from whether or not it's refreshing the cache entry.

The database query that causes problems is:

SELECT id, cache_date, saved_search_id, children
FROM   civicrm_group
WHERE  id 0
  AND  ( saved_search_id != 0
   OR    saved_search_id IS NOT NULL
   OR    children IS NOT NULL )

Clearly "WHERE id 0" is invalid syntax. The problem seems to be with changes to the CRM/Contact/BAO/Query.php code. The function addGroupContactCache used to create a clause within the function, something like "WHERE ID IN (4, 7, 10)". Now it calls self::buildClause.

From some debugging I did, buildClause is usually called with "IN" as the $op value, in which case it generates a valid clause. But sometimes the $op value is "=", and it returns that "id 0" clause which breaks things.

The code changes between 4.6.3 and 4.6.5 are complicated enough that I'm not sure how to quick-fix it. Any suggestions to get our smart groups working again are very welcome!

  • I too am seeing odd things with smart groups in 4.6.5. I have a number of smart groups that are based upon membership. It has lost this info so it pulls everyone. Also see error if I clear caches and then go to manage groups gets syntax error the sql has AND where should have WHERE: CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache768 (SELECT 65 as group_id, contact_a.id as contact_id FROM CustomSearch_SYBNT_temp as contact_a AND contact_a.id NOT IN ( SELECT contact_id FROM civicrm_group_contact WHERE civicrm_group_contact.status = 'Removed' AND civicrm_group_contact.group_id = 65 ) ) – Paul-Tahoe Jul 28 '15 at 3:16
4

To standardize Get.api and Search form underlying where clause(s), buildClause() fn expects value(s) to be in array(operator => array(values)) format [I call it "Operator as Key" - OK format :p ]. Earlier in prior version most of the Get.api aren't working because the buildClause() was designed according to Search formValues format [array(values)], so there is conflict in how the values got retrieved and eventually result into Fatal Error/Empty Value for get.Apis. Whereas in some worst-case scenario the get.Api was only working proper with '=' operator as the buildClause didn't got the flexibility to work with any other operator other then '='. But recent changes ( 4.6 onward ) in buildClause expects values atleast for n-nary operators IN/NOT IN in the desired OK format. On the otherhand recent changes has ALSO ensured that the Search Form formvalues processed first into OK format before passing it to build-Clause.

Now back to the bug mentioned here, here's my fix https://github.com/civicrm/civicrm-core/pull/6320. In addition buildClause will also support previous format but will through a e-notice to notify the wrong value format.

| improve this answer | |
  • I tried the fix. It's still building the where clause without the WHERE. See my comment above. I'm guessing this is a different case. Should I file a different question/issue? – Paul-Tahoe Jul 28 '15 at 15:23
  • I was missing the steps to replicate the bug from UI, as you mentioned earlier, so the fix was an improvement. But if the bug still persist please file a issue with steps to replicate and if possible attach the screenshots of 46 demo site d46.demo.civicrm.org !! – Monish Deb Jul 28 '15 at 16:43
  • I'll file a separate issue. In the meantime, I get the error if I clear the civi cache and then go to the manage groups page. It goes off and creates the cache for that page. It churns for a while and then errors with syntax error. I'm guessing I won't be able to reproduce in a demo database. Some of the smart groups are based upon include exclude query which in the past has caused issues. – Paul-Tahoe Jul 28 '15 at 19:30
  • Thanks Monish, that seems to make the smart groups work again. (Though I haven't done full testing.) Unfortunately, when I try to view a contact record, it breaks with the error "Expected one Contact but found 0" – Norris Jul 29 '15 at 2:22
  • 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.