Answers.Body: <p>This particular error seems to be a recent regression in 4.7.23:
  <a href="https://issues.civicrm.org/jira/browse/CRM-21029" rel="nofollow noreferrer">https://issues.civicrm.org/jira/browse/CRM-21029</a></p>
  
  <p>You can wait until the release of 4.7.24 or apply the patch from <a href="https://github.com/civicrm/civicrm-core/pull/10819" rel="nofollow noreferrer">https://github.com/civicrm/civicrm-core/pull/10819</a> manually.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-16T10:28:49.217Z
Answers.Text: 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.
Answers.UserDisplayName: thoni56
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-16T09:42:46.067Z
Answers.LastActivityDate: 2017-08-16T09:42:46.067Z
Answers.UserDisplayName: Thomas Schüttler
Body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>This particular example is when running the standard Activity report.</p>
  
  <p>The errors are very often SQL syntax errors (1064) thrown in the midst of standard CiviCRM functions. I've turned on <strong>define('CIVICRM_DAO_DEBUG', 1);</strong> and <a href="https://gist.github.com/thoni56/4f68f98ecfd67332476a4593c4a7f10a" rel="nofollow noreferrer">there is a gist here</a> with the complete output from that error, backtrace and all.</p>
  
  <p>I think I've found the offending query with the syntax error:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>Looking at the backtrace, shows</p>
  
  <pre><code>#12 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Core/DAO.php(1315): CRM_Core_DAO-&gt;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-&gt;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-&gt;postProcess()
  #16 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Core/Form.php(543): CRM_Report_Form-&gt;preProcess()
  #17 /var/www/events/administrator/components/com_civicrm/civicrm/CRM/Core/QuickForm/Action/Display.php(92): CRM_Core_Form-&gt;buildForm()
  </code></pre>
  
  <p>Line #14 points to <code>CRM/Report/Form/Activity.php(877)</code> which in is in the context of</p>
  
  <pre><code>$this-&gt;_aclWhere = "";
  $this-&gt;buildPermissionClause();
  
  $sql = "{$this-&gt;_select}
    FROM civireport_activity_temp_target tar
    INNER JOIN civicrm_activity {$this-&gt;_aliases['civicrm_activity']} ON {$this-&gt;_aliases['civicrm_activity']}.id = tar.civicrm_activity_id
    INNER JOIN civicrm_activity_contact {$this-&gt;_aliases['civicrm_activity_contact']} ON {$this-&gt;_aliases['civicrm_activity_contact']}.activity_id = {$this-&gt;_aliases['civicrm_activity']}.id
    AND {$this-&gt;_aliases['civicrm_activity_contact']}.record_type_id = {$sourceID}
    LEFT JOIN civicrm_contact contact_civireport ON contact_civireport.id = {$this-&gt;_aliases['civicrm_activity_contact']}.contact_id 
    WHERE (1) AND {$this-&gt;_aclWhere} {$groupByFromSelect} {$this-&gt;_having} {$this-&gt;_orderBy} {$this-&gt;_limit}";
  
  $this-&gt;buildRows($sql, $rows);
  </code></pre>
  
  <p>This looks like <code>this-&gt;aclWhere</code> is expected to contain something that should appear between the <code>AND</code> and the <code>GROUP BY</code> causing the SQL syntax error.</p>
  
  <p>If I'm correct so far, then it looks like <code>buildPermissionClause()</code> fails. And it looks like this</p>
  
  <pre><code>public function buildPermissionClause() {
    $ret = array();
    foreach ($this-&gt;selectedTables() as $tableName) {
      $baoName = str_replace('_DAO_', '_BAO_', CRM_Core_DAO_AllCoreTables::getClassForTable($tableName));
      if ($baoName &amp;&amp; class_exists($baoName) &amp;&amp; !empty($this-&gt;_columns[$tableName]['alias'])) {
        $tableAlias = $this-&gt;_columns[$tableName]['alias'];
        $clauses = array_filter($baoName::getSelectWhereClause($tableAlias));
        foreach ($clauses as $field =&gt; $clause) {
          // Skip contact_id field if redundant
          if ($field != 'contact_id' || !in_array('civicrm_contact', $this-&gt;selectedTables())) {
            $ret["$tableName.$field"] = $clause;
          }
        }
      }
    }
    // Override output from buildACLClause
    $this-&gt;_aclFrom = NULL;
    $this-&gt;_aclWhere = implode(' AND ', $ret);
  }
  </code></pre>
  
  <p>And here I'm at a loss... Where do I go next?</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-08-15T19:56:42.830Z
Comments.Text: Could you please post the entirety of the error you're receiving?  Also, what version of CiviCRM are you using?
Comments.UserDisplayName: Jon G - Megaphone Tech
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-08-16T05:49:10.727Z
Comments.Text: Added a link to a gist of the complete output. We're on 4.7.23.
Comments.UserDisplayName: thoni56
Tags: <permissions><php><sql>
Title: Debugging SQL - possible database problem
ThreadText: # Debugging SQL - possible database problem
  
  (Asked by: username_0 on 2017-08-15)
  
  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](https://gist.github.com/username_0/4f68f98ecfd67332476a4593c4a7f10a) 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?
  
  **Tags:** permissions, php, sql
  
  > **Comments:**
  > **username_1**: Could you please post the entirety of the error you're receiving?  Also, what version of CiviCRM are you using?  
  > **username_0**: Added a link to a gist of the complete output. We're on 4.7.23.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2017-08-16. Score: 2 | Accepted answer
  
  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.
  
  > **Comments:**
  > **username_0**: 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.  
  
  ---
  
  