0

This is possibly a question with much too deep technical aspects (SQL, PHP and Civi-innards...) for this forum, but I don't know where else to start, so here goes.

I am experiencing mysterious problems with my CiviCRM installation. Various SQL errors are thrown now and then. I had to restore from a backup and I am suspecting that the schema, or some data, was not restored completely correct.

We're currently on 4.7.23 but I've experienced this, and tried to it, since a few upgrades. It is a big problem but it does not seem to affect the features we use most or the front end. But I'm afraid it will get worse if I don't get to the bottom of this. I have a development server where I can easily restore a copy of our live server so there I can try anything.

This particular example is when running the standard Activity report.

The errors are very often SQL syntax errors (1064) thrown in the midst of standard CiviCRM functions. I've turned on define('CIVICRM_DAO_DEBUG', 1); and there is a gist here with the complete output from that error, backtrace and all.

I think I've found the offending query with the syntax error:

SELECT SQL_CALC_FOUND_ROWS GROUP_CONCAT(civicrm_contact_contact_source SEPARATOR ';') as civicrm_contact_contact_source,
    GROUP_CONCAT(civicrm_contact_contact_assignee SEPARATOR ';') as civicrm_contact_contact_assignee,
    GROUP_CONCAT(civicrm_contact_contact_target SEPARATOR ';') as civicrm_contact_contact_target,
    GROUP_CONCAT(civicrm_contact_contact_source_id SEPARATOR ';') as civicrm_contact_contact_source_id,
    GROUP_CONCAT(civicrm_contact_contact_assignee_id SEPARATOR ';') as civicrm_contact_contact_assignee_id, 
    GROUP_CONCAT(civicrm_contact_contact_target_id SEPARATOR ';') as civicrm_contact_contact_target_id, civicrm_activity_id, civicrm_activity_source_record_id, civicrm_activity_activity_type_id, civicrm_activity_activity_subject, civicrm_activity_activity_date_time, civicrm_activity_status_id
  FROM civireport_activity_temp_target tar
  INNER JOIN civicrm_activity activity_civireport ON activity_civireport.id = tar.civicrm_activity_id
  INNER JOIN civicrm_activity_contact activity_contact_civireport ON activity_contact_civireport.activity_id = activity_civireport.id
    AND activity_contact_civireport.record_type_id = 2
  LEFT JOIN civicrm_contact contact_civireport ON contact_civireport.id = activity_contact_civireport.contact_id
  WHERE (1) AND GROUP BY civicrm_activity_id, civicrm_activity_source_record_id, civicrm_activity_activity_type_id, civicrm_activity_activity_subject, civicrm_activity_activity_date_time, civicrm_activity_status_id ORDER BY civicrm_activity_activity_date_time ASC LIMIT 0, 50

The SQL error is pointing to the 'GROUP BY', and looking at it now I kind of think it looks incorrect; "WHERE (1) AND GROUP BY...", but I'm no SQL expert.

Looking at the backtrace, shows

#12 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Core/DAO.php(1315): CRM_Core_DAO->query("SELECT SQL_CALC_FOUND_ROWS GROUP_CONCAT(civicrm_contact_contact_source SEPARA...", TRUE)
#13 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Report/Form.php(2753): CRM_Core_DAO::executeQuery("SELECT SQL_CALC_FOUND_ROWS GROUP_CONCAT(civicrm_contact_contact_source SEPARA...")
#14 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Report/Form/Activity.php(877): CRM_Report_Form->buildRows("SELECT SQL_CALC_FOUND_ROWS GROUP_CONCAT(civicrm_contact_contact_source SEPARA...", (Array:0))
#15 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Report/Form.php(840): CRM_Report_Form_Activity->postProcess()
#16 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Core/Form.php(543): CRM_Report_Form->preProcess()
#17 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Core/QuickForm/Action/Display.php(92): CRM_Core_Form->buildForm()

Line #14 points to CRM/Report/Form/Activity.php(877) which in is in the context of

$this->_aclWhere = "";
$this->buildPermissionClause();

$sql = "{$this->_select}
  FROM civireport_activity_temp_target tar
  INNER JOIN civicrm_activity {$this->_aliases['civicrm_activity']} ON {$this->_aliases['civicrm_activity']}.id = tar.civicrm_activity_id
  INNER JOIN civicrm_activity_contact {$this->_aliases['civicrm_activity_contact']} ON {$this->_aliases['civicrm_activity_contact']}.activity_id = {$this->_aliases['civicrm_activity']}.id
  AND {$this->_aliases['civicrm_activity_contact']}.record_type_id = {$sourceID}
  LEFT JOIN civicrm_contact contact_civireport ON contact_civireport.id = {$this->_aliases['civicrm_activity_contact']}.contact_id 
  WHERE (1) AND {$this->_aclWhere} {$groupByFromSelect} {$this->_having} {$this->_orderBy} {$this->_limit}";

$this->buildRows($sql, $rows);

This looks like this->aclWhere is expected to contain something that should appear between the AND and the GROUP BY causing the SQL syntax error.

If I'm correct so far, then it looks like buildPermissionClause() fails. And it looks like this

public function buildPermissionClause() {
  $ret = array();
  foreach ($this->selectedTables() as $tableName) {
    $baoName = str_replace('_DAO_', '_BAO_', CRM_Core_DAO_AllCoreTables::getClassForTable($tableName));
    if ($baoName && class_exists($baoName) && !empty($this->_columns[$tableName]['alias'])) {
      $tableAlias = $this->_columns[$tableName]['alias'];
      $clauses = array_filter($baoName::getSelectWhereClause($tableAlias));
      foreach ($clauses as $field => $clause) {
        // Skip contact_id field if redundant
        if ($field != 'contact_id' || !in_array('civicrm_contact', $this->selectedTables())) {
          $ret["$tableName.$field"] = $clause;
        }
      }
    }
  }
  // Override output from buildACLClause
  $this->_aclFrom = NULL;
  $this->_aclWhere = implode(' AND ', $ret);
}

And here I'm at a loss... Where do I go next?

  • Could you please post the entirety of the error you're receiving? Also, what version of CiviCRM are you using? – Jon G - Megaphone Tech Aug 15 '17 at 19:56
  • Added a link to a gist of the complete output. We're on 4.7.23. – thoni56 Aug 16 '17 at 5:49
2

This particular error seems to be a recent regression in 4.7.23: https://issues.civicrm.org/jira/browse/CRM-21029

You can wait until the release of 4.7.24 or apply the patch from https://github.com/civicrm/civicrm-core/pull/10819 manually.

  • Thanks, the patch worked perfectly! Should have checked this of course, but I seem to have some other issues that I thought this was another example of. – thoni56 Aug 16 '17 at 10:28

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.