SQL Error in Contribution Details Report

Question

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

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]

Answer 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.