Answers.Body: <p>Your mysql database has only_full_group_by set. You want to unset it, but probably keep the rest of the sql_mode values. You can see the current value with <code>SELECT @@sql_mode</code>. You probably want to set it in the server's my.cnf file.</p>
  
  <p>So for example if the current value is
  ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION</p>
  
  <p>then you want to set it to
  STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION</p>
  
  <p>See <a href="https://dev.mysql.com/doc/refman/5.7/en/option-files.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/5.7/en/option-files.html</a> if you're not familiar with the my.cnf startup options file.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-05T04:22:42.777Z
Answers.Text: Thanks! The link Demerit put in a comment says this was fixed in 4.7.10 but I am seeing it in a later version. Is the issue with the mysql database configuration or should civicrm be modified? If the issue is with the database then can civicrm check and warn people that there is an issue?
Answers.UserDisplayName: Arthur Goldstein
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-05T17:16:22.723Z
Answers.Text: That is the same problem but a different code section, so that fix doesn't directly fix this one.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-06T18:48:37.553Z
Answers.Text: Should an issue be created for this? As mentioned, I am new so not sure if I should report this somewhere else. Thanks.
Answers.UserDisplayName: Arthur Goldstein
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-08T00:43:16.087Z
Answers.Text: Probably but not sure if it's just a documentation update needed that the mysql setting needs to be turned off or whether the entire code should be scanned for what are technically invalid SQL queries but where mysql has always allowed them in the past. I'm sure there are more such queries lying around since it has always been a common thing not just in civi but elsewhere to write mysql GROUP BY queries and not account for all the fields in the SELECT clause. Some discussion here https://stackoverflow.com/questions/1225144/why-does-mysql-allow-group-by-queries-without-aggregate-functions
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-03T23:23:59.893Z
Answers.LastActivityDate: 2017-12-03T23:23:59.893Z
Answers.UserDisplayName: Demerit
Body: <p>Can someone help me resolve this error? I have recently started using CiviCRM but the website has been up for years. Thanks for helping. Arthur</p>
  
  <p>Running CiviCRM 4.7.27 with Wordpress 4.8.4</p>
  
  <p>On the Contribution Reports/Contribution Details see the following error in the log when trying to view results.</p>
  
  <pre><code>[type] =&gt; DB_Error
  [user_info] =&gt; CREATE TEMPORARY TABLE civireport_contribution_detail_temp2  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, email_civireport.email as civicrm_email_email, phone_civireport.phone as civicrm_phone_phone, contribution_civireport.id as civicrm_contribution_contribution_id, contribution_civireport.financial_type_id as civicrm_contribution_financial_type_id, contribution_civireport.currency as civicrm_contribution_currency, contribution_civireport.receive_date as civicrm_contribution_receive_date, contribution_soft_civireport.amount as civicrm_contribution_total_amount_sum, 'Soft Credit' as civicrm_contribution_contribution_or_soft, address_civireport.country_id as civicrm_address_country_id  
    FROM  civireport_contribution_detail_temp1 temp1_civireport
    INNER JOIN civicrm_contribution contribution_civireport
      ON temp1_civireport.civicrm_contribution_contribution_id = contribution_civireport.id
    INNER JOIN civicrm_contribution_soft contribution_soft_civireport
      ON contribution_soft_civireport.contribution_id = contribution_civireport.id
    INNER JOIN civicrm_contact      contact_civireport
      ON contact_civireport.id = contribution_soft_civireport.contact_id
  
  
    LEFT JOIN civicrm_phone phone_civireport
    ON (contact_civireport.id =
    phone_civireport.contact_id) AND
    phone_civireport.is_primary = 1
  
               LEFT JOIN civicrm_address address_civireport
                         ON (contact_civireport.id =
                             address_civireport.contact_id) AND
                             address_civireport.is_primary = 1
  
          LEFT JOIN civicrm_email email_civireport
                 ON contact_civireport.id = email_civireport.contact_id AND
                    email_civireport.is_primary = 1
  </code></pre>
  
  <p>WHERE (1) AND ( contribution_civireport.contribution_status_id IN (1) )  GROUP BY contact_civireport.id, contribution_civireport.id, email_civireport.email, phone_civireport.phone, 'Contribution', address_civireport.country_id [nativecode=1055 ** Expression #9 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'civicrm.contribution_soft_civireport.amount' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by]</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-12-03T23:26:27.357Z
Comments.Text: See also https://civicrm.stackexchange.com/questions/13117/suscpicious-database-errors which is a different query but the same problem.
Comments.UserDisplayName: Demerit
Tags: <civireport>
Title: SQL Error in Contribution Details Report
ThreadText: # SQL Error in Contribution Details Report
  
  (Asked by: username_0 on 2017-12-03)
  
  Can someone help me resolve this error? I have recently started using CiviCRM but the website has been up for years. Thanks for helping. username_0
  
  Running CiviCRM 4.7.27 with Wordpress 4.8.4
  
  On the Contribution Reports/Contribution Details see the following error in the log when trying to view results.
  
  ```
  [type] => DB_Error
  [user_info] => CREATE TEMPORARY TABLE civireport_contribution_detail_temp2  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, email_civireport.email as civicrm_email_email, phone_civireport.phone as civicrm_phone_phone, contribution_civireport.id as civicrm_contribution_contribution_id, contribution_civireport.financial_type_id as civicrm_contribution_financial_type_id, contribution_civireport.currency as civicrm_contribution_currency, contribution_civireport.receive_date as civicrm_contribution_receive_date, contribution_soft_civireport.amount as civicrm_contribution_total_amount_sum, 'Soft Credit' as civicrm_contribution_contribution_or_soft, address_civireport.country_id as civicrm_address_country_id  
    FROM  civireport_contribution_detail_temp1 temp1_civireport
    INNER JOIN civicrm_contribution contribution_civireport
      ON temp1_civireport.civicrm_contribution_contribution_id = contribution_civireport.id
    INNER JOIN civicrm_contribution_soft contribution_soft_civireport
      ON contribution_soft_civireport.contribution_id = contribution_civireport.id
    INNER JOIN civicrm_contact      contact_civireport
      ON contact_civireport.id = contribution_soft_civireport.contact_id
  
    LEFT JOIN civicrm_phone phone_civireport
    ON (contact_civireport.id =
    phone_civireport.contact_id) AND
    phone_civireport.is_primary = 1
  
               LEFT JOIN civicrm_address address_civireport
                         ON (contact_civireport.id =
                             address_civireport.contact_id) AND
                             address_civireport.is_primary = 1
  
          LEFT JOIN civicrm_email email_civireport
                 ON contact_civireport.id = email_civireport.contact_id AND
                    email_civireport.is_primary = 1
  
  ```
  
  WHERE (1) AND ( contribution\_civireport.contribution\_status\_id IN (1) ) GROUP BY contact\_civireport.id, contribution\_civireport.id, email\_civireport.email, phone\_civireport.phone, 'Contribution', address\_civireport.country\_id [nativecode=1055 \*\* Expression #9 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'civicrm.contribution\_soft\_civireport.amount' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by]
  
  **Tags:** civireport
  
  > **Comments:**
  > **username_1**: See also https://civicrm.stackexchange.com/questions/13117/suscpicious-database-errors which is a different query but the same problem.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2017-12-03. Score: 1
  
  Your mysql database has only\_full\_group\_by set. You want to unset it, but probably keep the rest of the sql\_mode values. You can see the current value with `SELECT @@sql_mode`. You probably want to set it in the server's my.cnf file.
  
  So for example if the current value is
  ONLY\_FULL\_GROUP\_BY,STRICT\_TRANS\_TABLES,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION
  
  then you want to set it to
  STRICT\_TRANS\_TABLES,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION
  
  See <https://dev.mysql.com/doc/refman/5.7/en/option-files.html> if you're not familiar with the my.cnf startup options file.
  
  > **Comments:**
  > **username_0**: Thanks! The link username_1 put in a comment says this was fixed in 4.7.10 but I am seeing it in a later version. Is the issue with the mysql database configuration or should civicrm be modified? If the issue is with the database then can civicrm check and warn people that there is an issue?  
  > **username_1**: That is the same problem but a different code section, so that fix doesn't directly fix this one.  
  > **username_0**: Should an issue be created for this? As mentioned, I am new so not sure if I should report this somewhere else. Thanks.  
  > **username_1**: Probably but not sure if it's just a documentation update needed that the mysql setting needs to be turned off or whether the entire code should be scanned for what are technically invalid SQL queries but where mysql has always allowed them in the past. I'm sure there are more such queries lying around since it has always been a common thing not just in civi but elsewhere to write mysql GROUP BY queries and not account for all the fields in the SELECT clause. Some discussion here https://stackoverflow.com/questions/1225144/why-does-mysql-allow-group-by-queries-without-aggregate-functions  
  
  ---
  
  