4

In my installation I firstly recognized database errors like described in CiviCRM issue CRM-19109: Draft mailings are not saved. In a fresh local installation (Ubuntu 16.04LTS, php 5.6, mysql 5.7.13, Drupal 7.50) even more database errors came up.

Digging into the MySQL database showed error messages like:

SELECT  id, component_type
FROM    civicrm_mailing_component
WHERE   is_active = 1
AND     is_default = 1
GROUP BY component_type LIMIT 0, 25

MySQL said:

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database_civicrm7.civicrm_mailing_component.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I found that since MySQL 5.7.5 the default value for "sql_mode" is "ONLY_FULL_GROUP_BY" and this causes the problems with CiviCRM database and CiviCRM SQL queries.

In my local installation I added in /etc/mysql/mysql.conf.d/mysqld.cnf/mysqld.cnf

[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

… no more problems so far.

The issue is addressed here:

  • Thanks for your post but I'm not clear what your question is. Could you please edit the question to make this clear. It would also be helpful if you could say which CMS you are using (i.e. Drupal, WordPress or Joomla) - including the version. – William Mortada Jul 27 '16 at 11:27
  • Thank you for the comment. Its Drupal 7.50, I added it above. I am aware that this is not a question ... but since I spent so much time to hunt that odd behavior I simply wanted to share my findings and this forum seemed to me the most appropriate place. Hoping that search engines do the trick if someone has the same problem. – froschq Aug 1 '16 at 14:49
  • Ah, in that case it would be better to put it into a question and answer format. You can then answer your own question. This is how Stack Exchange works best - with questions and answers. – William Mortada Aug 1 '16 at 14:56
1

This issue was fixed in CiviCRM 4.7.10.

If you are using an earlier version of CiviCRM (e.g. 4.6), one option is to stick to an earlier version of MySQL (e.g. 5.6).

Alternatively, as stated in the question, you can fix this problem by adding a custom config file for MySQL. I used the following file:

[mysqld]
# Turn off 'ONLY_FULL_GROUP_BY' option as this breaks CiviCRM < 4.7.10
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Rather than using my settings, I suggest that you check your current SQL mode with the following query:

SELECT @@SQL_MODE; 

Copy the results of this query into the file above without ONLY_FULL_GROUP_BY.

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.