CiviCRM Community Forums (archive)

After upgrading to 4.3.2 this weekend I am getting a SQL error anytime I try to click on the group tab for a contact; individual, household, organization.

The specific error is a syntaxt error in the SQL query when its trying to insert data into the group_contact_cache table.  Here is the specific code that raises the error.  I tried looking the query over and the problem seems to be at the second UNION.  There is a closing paren right before the second union that actually closes the INSERT. I think it is supposed to encompass the UNION but I am not sure exactly as there is a lot going on in that query. 

If anyone has a work around for not that would suffice.  I cant replicated the problem on the demo site since its still running 4.3.1.  Perhaps 4.3.2 is not quite stable yet?  Anyway here is the full unmodified query that generates the error. I even left specific identifiers in the mix.

INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) ( select contact_id_a as contact_id from ( /*This set gets all the households that have contributions by members plus household greater than the having clause statement*/ select household as householdID, sum(total_amount) from ( SELECT contribs.contact_id as contactID, total_amount, receive_date, contribs.id as contribID, contact_id_b as household FROM `civicrm4.2`.`civicrm_contribution` as contribs inner join `civicrm4.2`.`civicrm_relationship` as individuals on contact_id = individuals.contact_id_a and individuals.relationship_type_id=7 where receive_date >= '20121119000000' and receive_date <= '20121121235959' union SELECT households.id as contactID, total_amount, receive_date, contribs.id as contribID, households.id as household FROM `civicrm4.2`.`civicrm_contact` as households inner join `civicrm4.2`.`civicrm_contribution` as contribs on households.id = contact_id where receive_date >= '20121119000000' and receive_date <= '20121121235959' and contact_type='household' ) as temptable group by household having sum(total_amount) > 100 ) as aggregateHouseholds left outer join `civicrm4.2`.`civicrm_relationship` on householdID = contact_id_b and relationship_type_id=7 where not isnull(contact_id_a) /*combine the individuals with the households that we found above*/ union ( select household as contact_id from ( SELECT contribs.contact_id as contactID, total_amount, receive_date, contribs.id as contribID, contact_id_b as household FROM `civicrm4.2`.`civicrm_contribution` as contribs inner join `civicrm4.2`.`civicrm_relationship` as individuals on contact_id = individuals.contact_id_a and individuals.relationship_type_id=7 where receive_date >= '20121119000000' and receive_date <= '20121121235959' union SELECT households.id as contactID, total_amount, receive_date, contribs.id as contribID, households.id as household FROM `civicrm4.2`.`civicrm_contact` as households inner join `civicrm4.2`.`civicrm_contribution` as contribs on households.id = contact_id where receive_date >= '20121119000000' and receive_date <= '20121121235959' and contact_type='household' ) as temptable group by household having sum(total_amount) > 100 ) 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 = 27 ) );

I made it a little further on the error here.  I found that I can remark the execute and just return NULL the cache insert does not happen but the groups are at least displayed correctly without any errors.  The line I remarked is in /CRM/Contact/BAO/groupContactCache.php around line number 402.
 You can see there is even a comment left in the code saying FIXME so perhaps this piece is not complete yet.  Anyway removing the query makes it work again for now.

 $insertSql = "INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) ($selectSql);";
      $processed = TRUE; // FIXME
      //$result = CRM_Core_DAO::executeQuery($insertSql);
++   $result = NULL;