Answers.Body: <p>This issue <a href="https://issues.civicrm.org/jira/browse/CRM-18439?jql=text%20~%20%22only_full_group_by%22" rel="nofollow noreferrer">was fixed</a> in CiviCRM 4.7.10.</p>
  
  <p>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).</p>
  
  <p>Alternatively, as stated in the question, you can fix this problem by adding a custom config file for MySQL.  I used the following file:</p>
  
  <pre><code>[mysqld]
  # Turn off 'ONLY_FULL_GROUP_BY' option as this breaks CiviCRM &lt; 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"
  </code></pre>
  
  <p>Rather than using my settings, I suggest that you check your current SQL mode with the following query:</p>
  
  <pre><code>SELECT @@SQL_MODE; 
  </code></pre>
  
  <p>Copy the results of this query into the file above without <code>ONLY_FULL_GROUP_BY</code>.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-11-10T16:20:57.017Z
Answers.LastActivityDate: 2016-11-10T16:20:57.017Z
Answers.UserDisplayName: William Mortada
Body: <p>In my installation I firstly recognized database errors like described in CiviCRM issue <a href="https://issues.civicrm.org/jira/browse/CRM-19109" rel="nofollow noreferrer">CRM-19109: Draft mailings are not saved</a>. In a fresh local installation (Ubuntu 16.04LTS, php 5.6, mysql 5.7.13, Drupal 7.50) even more database errors came up.</p>
  
  <p>Digging into the MySQL database showed error messages like:</p>
  
  <pre><code>SELECT  id, component_type
  FROM    civicrm_mailing_component
  WHERE   is_active = 1
  AND     is_default = 1
  GROUP BY component_type LIMIT 0, 25
  </code></pre>
  
  <p>MySQL said:</p>
  
  <blockquote>
    <p><strong><em>#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</em></strong></p>
  </blockquote>
  
  <p>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.</p>
  
  <p>In my local installation I added in <code>/etc/mysql/mysql.conf.d/mysqld.cnf/mysqld.cnf</code></p>
  
  <pre><code>[mysqld]
  sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
  </code></pre>
  
  <p>… no more problems so far. </p>
  
  <p>The issue is addressed here:</p>
  
  <ul>
  <li><a href="https://issues.civicrm.org/jira/browse/CRM-18439" rel="nofollow noreferrer">CRM-18439: support MySQL 5.7 sql_mode=only_full_group_by</a></li>
  </ul>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-07-27T11:27:09.187Z
Comments.Text: 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.
Comments.UserDisplayName: William Mortada
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-08-01T14:49:29.190Z
Comments.Text: 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.
Comments.UserDisplayName: froschq
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-08-01T14:56:00.863Z
Comments.Text: 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.
Comments.UserDisplayName: William Mortada
Tags: <error><installation><mysql>
Title: Suscpicious Database Errors
ThreadText: # Suscpicious Database Errors
  
  (Asked by: username_0 on 2016-07-27)
  
  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
  
  **Tags:** error, installation, mysql
  
  > **Comments:**
  > **username_1**: 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.  
  > **username_0**: 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.  
  > **username_1**: 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.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2016-11-10. Score: 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.
  
  
  ---
  
  