Suscpicious Database Errors

Question

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:

• CRM-18439: support MySQL 5.7 sql_mode=only_full_group_by

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