CiviCRM Community Forums (archive)

I've done a trial upgrade from 4.0.7 to 4.2.6.  I've temporarily removed all customisations.

I have a smart group (or two) which aren't working as I expect.  I find the group in Manage Groups then click on Contacts.  It lists 12 contacts.  If I click on Edit Smart Group Search Criteria I immediately see the expected result of 551 contacts.  If I do a Update Smart Group etc the count remains at 12.

If I re-create the group from scratch, the same problem arises.

For a contact in the 12 the group is listed as "Added (by Admin)".
For one of the non-listed contacts that should be in the group, this wasn't present.

Any ideas?

Thanks in advance

I set up a similar search in the demo site and it works OK.  Chris

can you create another smart group with the same parameters new in 4.2.6 and see if that works

if i had to guess, something changed in the search protocol and it is not backward compatible and hence the errors. but this is just a random guess

lobo

I've found that civicrm_group_contact_cache is not being updated correctly in CRM\Contact\BAO\GroupContactCache.php around line 344-346.  (This code has changed from v4.0.7.)

      $insertSql = "INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) ($selectSql);";
      $processed = TRUE; // FIXME
      $result = CRM_Core_DAO::executeQuery($insertSql);
The $selectSql is OK (see below) ie it returns the desired number of records (540).  The INSERT only inserts 12 because of a UI_contact_group unique index constraint (contact_id, group_id), with IGNORE meaning that such problems aren't reported.  There aren't such duplicates when I run the SELECT by itself.

This is weird.  If I limit the select to one record I get
group_id    contact_id
20    26841
If I then put the INSERT (no IGNORE) on the front, then I get
Duplicate entry '27037-20' for key 'UI_contact_group'
ie the contact_id is different.

This is the normal select clause:
SELECT 20 as group_id, contact_a.id as contact_id FROM civicrm_contact contact_a
LEFT JOIN civicrm_membership ON civicrm_membership.contact_id = contact_a.id LEFT JOIN
civicrm_contribution_recur ccr ON ( civicrm_membership.contribution_recur_id = ccr.id )
WHERE ( contact_a.contact_type IN ('Organization') AND civicrm_membership.membership_type_id = 7 AND
civicrm_membership.status_id IN ('1','2','3') AND civicrm_membership.is_test = 0 ) AND (contact_a.is_deleted = 0)
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 = 20 )
I'm running 5.1.49-1ubuntu8.1

Any help most appreciated as I'm stumped at the moment.  Thanks Chris

chris:

1. can u share the DB

2. ping me on irc and we can help debug / trace the problem

thanx

lobo

If I spin through the SELECT it works now.  I changed the preceding definition of $sqlB so that the second column is always called id.

    $sqlB = "
SELECT $groupID as group_id, contact_id as id
FROM   civicrm_group_contact
WHERE  civicrm_group_contact.status = 'Added'
  AND  civicrm_group_contact.group_id = $groupID ";

...

      $dao = CRM_Core_DAO::executeQuery($insertSql);
      while ($dao->fetch()) {
        $gid = $dao->group_id;
        $contactid = $dao->id;
        $insertSql = "INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) VALUES($gid,$contactid);";
        $result = CRM_Core_DAO::executeQuery($insertSql);
      }
      //$insertSql = "INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) ($selectSql);";
      $processed = TRUE; // FIXME
      //$result = CRM_Core_DAO::executeQuery($insertSql);
To me it feels like a MySql bug that I need to do this but in a quick look I could not see such issues reported.