1

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]

|improve this question
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.

|improve this answer
  • 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? – Arthur Goldstein Dec 5 '17 at 4:22
  • That is the same problem but a different code section, so that fix doesn't directly fix this one. – Demerit Dec 5 '17 at 17:16
  • Should an issue be created for this? As mentioned, I am new so not sure if I should report this somewhere else. Thanks. – Arthur Goldstein Dec 6 '17 at 18:48
  • 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 stackoverflow.com/questions/1225144/… – Demerit Dec 8 '17 at 0:43

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.