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?
