Answers.Body: <p>MySQL 5.7.5+ changed the way <a href="https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html">GROUP BY</a> behaved in order to be SQL99 <a href="https://en.wikipedia.org/wiki/SQL:1999">compliant</a> (where in previous versions it was not).</p>
  
  <p>There is a good overview of the changes <a href="http://rpbouman.blogspot.com/2014/09/mysql-575-group-by-respects-functional.html">here</a>.</p>
  
  <p>Unfortunately, this affects some core queries that Craft uses when fetching elements.</p>
  
  <p>The good news is that this has been fixed already in Craft 3 since we refactored the way that elements query grabs data and it no longer uses a GROUP BY.</p>
  
  <p>The bad news is that it's going to be pretty impractical to backport that fix into Craft 2.</p>
  
  <p>The workaround for Craft 2, if you're running MySQL 5.7.5+ is to edit your <a href="https://dev.mysql.com/doc/refman/5.1/en/option-files.html">my.cnf</a> file and remove the <code>ONLY_FULL_GROUP_BY</code> option from <a href="https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html">sql_mode</a>. That will change <code>GROUP BY</code> behavior back to its pre-MySQL 5.7.5 behavior.</p>
  
  <p><strong>Update</strong>:</p>
  
  <p>If, for whatever reason (primarily you're on shared hosting), you aren't able to change the <code>sql_mode</code> of your box at the environment level, as of the next Craft release (<a href="https://craftcms.com/changelog#build2945">post 2.6.2945</a>), there is a new <code>initSQLs</code> database config settings you can set in your <code>craft/config/db.php</code>.  It will accept an array of SQL statements to be executed immediately after the database connection has been established.  If you're running into this MySQL 5.7 error, you can do:</p>
  
  <pre><code>'initSQLs' =&gt; array("SET SESSION 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>Just keep in mind this method will add at least one extra SQL query to every request.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-04-28T03:24:48.703Z
Answers.Text: Another reference: https://mattstauffer.co/blog/how-to-disable-mysql-strict-mode-on-laravel-forge-ubuntu
Answers.UserDisplayName: Luke Holder
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-05-11T23:24:22.857Z
Answers.Text: Brad, you might be able to do a check for whether the server is running 5.7.5+ and add ANY_VALUE() to your columns if so? http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value
Answers.UserDisplayName: adrienne
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-05-12T00:47:29.863Z
Answers.Text: @adrienne It gets messy because the query is so dynamic. Element types can set any custom select and order by values, so those would have to be wrapped in ANY_VALUE depending on what the GROUP BY specifies.
Answers.UserDisplayName: Brad Bell
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-07-05T00:22:02.247Z
Answers.Text: "Impractical to backport" - Then don't. Best practice for Mysql 5.7 is to set the sqlmode in your DB drive on connect. Craft installations should not require setting this on the entire database instance in my.cnf, which is terrible practice. You should be implementing the one line change required to set this when you call the database connection.
  
  http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
Answers.UserDisplayName: Routhinator
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-19T09:40:01.093Z
Answers.Text: Thanks, the initSQLs allowed me to carry on working without having to mess about installing an older MySQL version or updating Craft to 3.x
Answers.UserDisplayName: Alexander Holsgrove
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-01-12T09:13:51.783Z
Answers.Text: This bit me today and feels important enough to me that it should be mentioned in the main Craft install docs somewhere, especially as MySQL 5.7.5+ usage is on the up (I hit this with a box provisioned via Laravel Forge)
Answers.UserDisplayName: Tom Davies
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-01-13T03:04:28.070Z
Answers.Text: Fair enough! Added a note to https://craftcms.com/docs/requirements
Answers.UserDisplayName: Brad Bell
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-03-30T22:34:26.060Z
Answers.Text: None of the solutions suggested are working for me. Is there any new issues as to why? My site is https://mentiondesign.co.uk/
Answers.UserDisplayName: mention79
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-22T18:02:09.800Z
Answers.Text: HEADS UP to Cloud SQL users - when you config the DB in the GCP console, the sql_mode flag is a drop-down menu, and only accepts one value. I found out from [this blog page](https://introvertedengineer.com/2017/03/23/mysql-sql_mode-troubles-google-cloud-sql/) that `TRADITIONAL` may work as an alias for the strict values you want, and none of the strict values you don't want. More on `TRADITIONAL` on the [MySQL 5.7 reference page](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_traditional).
Answers.UserDisplayName: ingernet
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-05-08T09:42:16.427Z
Answers.Text: @BradBell Just curious.. does the initSQLs solution still run an extra query even if we are running MySQL5.6?
Answers.UserDisplayName: biko
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-05-08T22:18:00.540Z
Answers.Text: @biko, if you're using `initSQLs`, it will execute whatever the value is on every request no matter the MySQL version.
Answers.UserDisplayName: Brad Bell
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-01-29T11:00:55.063Z
Answers.Text: This is the easiest solution to get it running if you work localy on MAMP
Answers.UserDisplayName: KSPR
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-10-26T17:19:45.677Z
Answers.LastActivityDate: 2016-09-29T22:39:53.823Z
Answers.LastEditDate: 2016-09-29T22:39:53.823Z
Answers.UserDisplayName: Brad Bell
Answers.Body: <p><strong>Updated</strong> <em>05/03/16</em>: I had a struggle getting this to work with <a href="https://laravel.com/docs/5.2/homestead" rel="noreferrer"><code>Homestead 5.2</code></a>. The MySQL error everyone else is seeing, <s>as well as <code>mcrypt</code> error, as <a href="https://laracasts.com/discuss/channels/general-discussion/homestead-3-and-mcrypt/replies/134924" rel="noreferrer">mycrypt is not installed by default</a> in <a href="https://atlas.hashicorp.com/laravel/boxes/homestead/versions/0.4.1" rel="noreferrer"><code>Vagrant box laravel/homestead 0.4.1</code></a></s> (Fixed in <a href="https://atlas.hashicorp.com/laravel/boxes/homestead/versions/0.4.2" rel="noreferrer">vagrant box laravel/homestead 0.4.2</a> — See <a href="https://craftcms.stackexchange.com/posts/13662/revisions">answer history</a> for <code>0.4.1</code> how-to).</p>
  
  <p><strong>So, steps to success:</strong></p>
  
  <ol>
  <li><code>vagrant ssh</code> into your box</li>
  <li><code>sudo vim /etc/mysql/my.cnf</code></li>
  <li>Scroll to the bottom of file and <kbd>A</kbd> to enter insert mode</li>
  <li>Copy and paste <code>[mysqld]
  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> to the bottom of the file</li>
  <li><kbd>esc</kbd> to exit input mode</li>
  <li><kbd>:wq</kbd> to save and close vim.</li>
  <li><code>sudo service mysql restart</code> to restart MySQL.</li>
  <li>Done!</li>
  </ol>
  
  <p><em><a href="https://medium.com/@cobbers/tweak-homestead-v0-3-3-to-work-with-craft-2-bfa66beb2460#.vb0iul4jq" rel="noreferrer">Original how-to article</a></em>.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-03-05T08:41:07.320Z
Answers.Text: Hi @Rob the latest version of Homestead v0.4.2 adds mcrypt back in.
Answers.UserDisplayName: Gareth
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-03-05T19:18:49.163Z
Answers.Text: @Gareth Great! So is it just the MySQL settings that need to be changed now?
Answers.UserDisplayName: Rob
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-03-05T23:42:14.533Z
Answers.Text: Yeah that's exactly right. I did need to destroy my homestead box and install a new one. For some reason the update didn't work for me. So you just need to do from 4 onwards in your points once you have v0.4.2.
Answers.UserDisplayName: Gareth
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-03-06T19:32:35.990Z
Answers.Text: @Gareth I updated and it seems to be working fine for me. But some of my previous updates seem to have carried over. It does seem to be a dark art getting the damn thing working sometimes! Thanks for the tip-off.
Answers.UserDisplayName: Rob
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-03-07T06:22:07.343Z
Answers.Text: yeah I'm still not sure all the messing you have to do is worth it. I'm going to stick with it for a while though and see how it goes.
Answers.UserDisplayName: Gareth
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-07-03T15:15:03.400Z
Answers.Text: Had this problem on digitalocean too. This fix works :)
Answers.UserDisplayName: darylknight
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-01-20T08:39:43.797Z
Answers.Text: This works for me! :)
Answers.UserDisplayName: vijay
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-02-11T01:51:19.163Z
Answers.LastActivityDate: 2016-03-05T20:27:25.133Z
Answers.LastEditDate: 2017-04-13T12:51:00.253Z
Answers.UserDisplayName: Rob
Answers.Body: <p>To fix this I have created a <code>.my.cnf</code> in my home-directory with the following content:</p>
  
  <pre><code>[mysqld]
  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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-05-04T13:30:26.497Z
Answers.Text: Only solution that worked on my mac. Thanks.
Answers.UserDisplayName: Gaurav Gandhi
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-11-20T13:17:45.983Z
Answers.LastActivityDate: 2015-11-20T13:17:45.983Z
Answers.UserDisplayName: Lars Bilke
Answers.Body: <p>For UBUNTU 16.04 LTS</p>
  
  <p>commands:</p>
  
  <pre><code>cd /etc/mysql
  
  sudo nano my.cnf
  </code></pre>
  
  <p>add these two lines:</p>
  
  <pre><code>[mysqld]
  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>save file and reset your computer.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-09-05T09:13:47.747Z
Answers.Text: This one works.
Answers.UserDisplayName: Dan Zuzevich
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-10T06:37:20.263Z
Answers.LastActivityDate: 2018-01-24T20:13:50.803Z
Answers.LastEditDate: 2018-01-24T20:13:50.803Z
Answers.UserDisplayName: Juan Gabriel
Answers.Body: <p>We have the same problem. MySQL version <em>(Ver 14.14 Distrib 5.7.9, for osx10.11 (x86_64) using  EditLine wrapper)</em></p>
  
  <p>Had problems <code>SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression</code> and by adding the code at my.cnf file resolved it for me:</p>
  
  <pre><code>[mysqld]
  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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-12-09T09:32:37.030Z
Answers.Text: This fixed my problem with Laravel forge on DO with PHP 7 and MySQL 5.7.9.
Answers.UserDisplayName: Johannes Lamers
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-11-24T02:50:31.423Z
Answers.LastActivityDate: 2015-11-26T22:22:01.317Z
Answers.LastEditDate: 2015-11-26T22:22:01.317Z
Answers.UserDisplayName: Joniel Roy Rosales
Answers.Body: <p>Here are some fairly specific instructions if you happen to be hosted on a server configured through ServerPilot.</p>
  
  <p><a href="https://serverpilot.io/community/articles/how-to-disable-strict-mode-in-mysql-5-7.html">https://serverpilot.io/community/articles/how-to-disable-strict-mode-in-mysql-5-7.html</a></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-08-16T22:04:16.560Z
Answers.LastActivityDate: 2016-08-16T22:04:16.560Z
Answers.UserDisplayName: Philip Jones
Answers.Body: <p>MySQL 5.7.5+ changed the way GROUP BY behaved in order to be SQL99 compliant (where in previous versions it was not). Either modify the existing mysql queries to be compatible to the new mysql version or make mysql behave as in previous versions , by executing the below queries on the mysql .</p>
  
  <p>SET sql_mode = '';   // current client </p>
  
  <p>SET GLOBAL sql_mode = '';   // for other clients like hibernate</p>
  
  <p>Source: <a href="http://www.proactiveclass.com/tutorials/java/hibernate/sql-error-group-by-incompatible-with-sql-mode-only-full-group-by" rel="nofollow"><a href="http://www.proactiveclass.com/tutorials/java/hibernate/sql-error-group-by-incompatible-with-sql-mode-only-full-group-by" rel="nofollow">http://www.proactiveclass.com/tutorials/java/hibernate/sql-error-group-by-incompatible-with-sql-mode-only-full-group-by</a></a></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-12-03T16:39:43.517Z
Answers.LastActivityDate: 2015-12-03T16:39:43.517Z
Answers.UserDisplayName: user4891166
Answers.Body: <p>The proper fixes have already been answered here; but I'd like to note that another fix is to use MariaDB instead of MySQL. It's a drop-in replacement for it, it's more performant for a number of metrics, and it's created by the original author of MySQL.</p>
  
  <p>Give it a go; you won't regret it.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-28T02:18:32.523Z
Answers.LastActivityDate: 2017-07-28T02:18:32.523Z
Answers.UserDisplayName: andrew.welch
Answers.Body: <p>I faced this problem when I upgraded MySQL to version >= 5.7.5 and I'v fixed it by following these steps:</p>
  
  <ol>
  <li>SSH into your server.</li>
  <li><p>Add <code>sql-mode=""</code> to your MySQL config:</p>
  
  <pre><code>echo -e '[mysqld]\nsql-mode=""' | tee --append /etc/mysql/conf.d/craft.cnf &gt; /dev/null
  </code></pre></li>
  <li><p>Restart MySQL service:</p>
  
  <pre><code>service mysql restart
  </code></pre></li>
  </ol>
  
  <hr>
  
  <p>Note: you might need to add <code>sudo</code> to those commands.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-12-13T15:26:23.700Z
Answers.LastActivityDate: 2016-01-13T05:19:22.827Z
Answers.LastEditDate: 2016-01-13T05:19:22.827Z
Answers.UserDisplayName: mohd4482
Answers.Body: <p>If you're using MAMP, it will constantly complain about existence of a "<em>global</em>" <strong>my.cnf</strong> file.<br>
  So, you need to update MySQL config template file inside your MAMP application.</p>
  
  <ol>
  <li>Start MAMP</li>
  <li>Go <em>File</em> > <em>Edit Template</em> > <em>MySQL (my.cnf)</em> > "<em>choose whatever version you're using</em>"</li>
  <li>Dump these two lines in the end of the opened file:</li>
  </ol>
  
  <blockquote>
    <p>[mysqld]
    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION</p>
  </blockquote>
  
  <p>Cheers!</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-12-17T14:00:15.643Z
Answers.LastActivityDate: 2018-12-17T14:00:15.643Z
Answers.UserDisplayName: syntax-punk
Answers.Body: <p>Following on from <a href="https://craftcms.stackexchange.com/a/13662/4937">Rob's answer</a>, for anyone running into this issue on Laravel Homestead, we can use Bash to script the change. That way, the edit to the <code>my.cnf</code> file is done automatically for any future VMs created using <code>vagrant up</code>.</p>
  
  <p>To do this, simply add the following to your <code>~/.homestead/after.sh</code> file:</p>
  
  <pre><code>STR="sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  if ! grep -Fxq "$STR" /etc/mysql/my.cnf
  then
      printf "\n# Craft 2 compatibility\n[mysqld]\n$STR" &gt;&gt; /etc/mysql/my.cnf
      service mysql restart
  fi
  </code></pre>
  
  <p>The above code searches <code>/etc/mysql/my.cnf</code> for the line <code>'sql_mode=...'</code>, and if it cannot be found, it is automatically appended, together with the preceding <code>[mysqld]</code> to the end of the file. The mysql service is then restarted to enable the change.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-03-26T04:43:52.677Z
Answers.LastActivityDate: 2016-03-26T04:43:52.677Z
Answers.LastEditDate: 2017-04-13T12:50:59.930Z
Answers.UserDisplayName: newtonne
Answers.Body: <p>Just in case somebody has problems with <code>'initSQLs'</code> on ddev: I had to remove 'NO_AUTO_CREATE_USER'.</p>
  <pre class="lang-php prettyprint-override"><code>&lt;?php
  /**
   * Database Configuration
   */
  
  return array(
      '*' =&gt; array(
          'server' =&gt; 'db',
          'user' =&gt; 'db',
          'password' =&gt; 'db',
          'database' =&gt; 'db',
          'initSQLs' =&gt; array(&quot;SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';&quot;)
      ),
  
  );
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-07-03T20:36:43.420Z
Answers.LastActivityDate: 2024-07-03T20:36:43.420Z
Answers.UserDisplayName: Max Strebel
Answers.Body: <p>open terminal :</p>
  
  <p>"sudo nano /etc/mysql/my.cnf"</p>
  
  <p>Scroll to the bottom of file and A to enter insert mode
  Copy and paste </p>
  
  <p>[mysqld]
  sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION </p>
  
  <p>save file then restart mysql service by the below command :</p>
  
  <p>"sudo service mysql restart"  to restart MySQL.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-07-28T11:07:58.400Z
Answers.LastActivityDate: 2016-07-28T11:07:58.400Z
Answers.UserDisplayName: Devdutt Sharma
Body: <p>I am getting the following error:</p>
  
  <blockquote>
    <p>Internal Server Error CDbCommand failed to execute the SQL statement:
    SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #18
    of SELECT list is not in GROUP BY clause and contains nonaggregated
    column 'creighton_mma_craft.structureelements.root' which is not
    functionally dependent on columns in GROUP BY clause; this is
    incompatible with sql_mode=only_full_group_by</p>
  </blockquote>
  
  <p>I have mysql 5.7.9 installed. Logging into the Control Panel is working fine, but the front end displays this message.</p>
  
  <p>During the installation, the screen was stuck at the very end with "Ooops…" message. </p>
  
  <p>I am new to terminal commands and mysql configurations so the more specific the better.</p>
  
  <p>Thanks.</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-10-24T16:31:06.997Z
Comments.Text: There are going to be 2 issues that need investigating here. 1) Whatever the underlying error message is you receiving during installation and 2) Whatever the template code is on the front-end that generated that SQL error. Can you do a fresh install to replicate #1, then zip up your `craft/storage/runtime/logs` folder and send it in to support@buildwithcraft.com?
Comments.UserDisplayName: Brad Bell
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-10-25T05:47:25.877Z
Comments.Text: Thanks for recommending to look into the logs. The clue was the sql_mode. Apparently as of MYSQL 5.7.5, the value is no longer set to empty. There are new default values separated by commas, and the culprit seems to be 'ONLY_FULL_GROUP_BY'. What I needed to do was create a 'my.cnf' file in "/etc" directory. There I created the new value with "sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES". Only thing is, I am not sure security-wise if that is the correct setting. There are other values that can be added. Maybe knows the best settings for security purposes?
Comments.UserDisplayName: user3818623
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-10-25T20:26:04.167Z
Comments.Text: Ahh... that's probably worth adding as an answer all by itself. :) It look like MySQL 5.7.5 changed the GROUP BY behavior, though (http://rpbouman.blogspot.com/2014/09/mysql-575-group-by-respects-functional.html) and (https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) and Craft needs to take that into account now, it definitely is a bug. I'll look into it.
Comments.UserDisplayName: Brad Bell
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-11-18T13:25:04.890Z
Comments.Text: Hmm.....so just to get this straight...so everyone installing a current mysql version....will get a fatal error on craft v2? With Craft3 not being around for quite some time....i would at least try to get that fix a bit more public into the installer or do backport it? Sounds a bit scary to me? no?
Comments.UserDisplayName: nexflo
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-11-18T19:20:51.593Z
Comments.Text: Hmm, I'm seeing this as well. Unfortunate as Vagrant Homestead box has just updated it's MySQL version. Is this unlikely to be resolved before v3 (judging from the answer below that seems to be the case)? Not keen on messing with settings, so guess I'll revert to previous box for now.
Comments.UserDisplayName: Rob
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-01-04T01:54:19.530Z
Comments.Text: I think it's not good to let everyone know what exactly you are working on. one might take the advance of a security flaw and take over your installation. i am talking about the database name which basically leads a way..
Comments.UserDisplayName: Manticore
Tags: <installation><mysql>
Title: Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by
ThreadText: # Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by
  
  (Asked by: username_0 on 2015-10-24)
  
  I am getting the following error:
  
  > Internal Server Error CDbCommand failed to execute the SQL statement:
  > SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #18
  > of SELECT list is not in GROUP BY clause and contains nonaggregated
  > column 'creighton\_mma\_craft.structureelements.root' which is not
  > functionally dependent on columns in GROUP BY clause; this is
  > incompatible with sql\_mode=only\_full\_group\_by
  
  I have mysql 5.7.9 installed. Logging into the Control Panel is working fine, but the front end displays this message.
  
  During the installation, the screen was stuck at the very end with "Ooops…" message.
  
  I am new to terminal commands and mysql configurations so the more specific the better.
  
  Thanks.
  
  **Tags:** installation, mysql
  
  > **Comments:**
  > **username_1**: There are going to be 2 issues that need investigating here. 1) Whatever the underlying error message is you receiving during installation and 2) Whatever the template code is on the front-end that generated that SQL error. Can you do a fresh install to replicate #1, then zip up your `craft/storage/runtime/logs` folder and send it in to support@buildwithcraft.com?  
  > **username_0**: Thanks for recommending to look into the logs. The clue was the sql_mode. Apparently as of MYSQL 5.7.5, the value is no longer set to empty. There are new default values separated by commas, and the culprit seems to be 'ONLY_FULL_GROUP_BY'. What I needed to do was create a 'my.cnf' file in "/etc" directory. There I created the new value with "sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES". Only thing is, I am not sure security-wise if that is the correct setting. There are other values that can be added. Maybe knows the best settings for security purposes?  
  > **username_1**: Ahh... that's probably worth adding as an answer all by itself. :) It look like MySQL 5.7.5 changed the GROUP BY behavior, though (http://rpbouman.blogspot.com/2014/09/mysql-575-group-by-respects-functional.html) and (https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) and Craft needs to take that into account now, it definitely is a bug. I'll look into it.  
  > **username_2**: Hmm.....so just to get this straight...so everyone installing a current mysql version....will get a fatal error on craft v2? With Craft3 not being around for quite some time....i would at least try to get that fix a bit more public into the installer or do backport it? Sounds a bit scary to me? no?  
  > **username_3**: Hmm, I'm seeing this as well. Unfortunate as Vagrant Homestead box has just updated it's MySQL version. Is this unlikely to be resolved before v3 (judging from the answer below that seems to be the case)? Not keen on messing with settings, so guess I'll revert to previous box for now.  
  > **username_4**: I think it's not good to let everyone know what exactly you are working on. one might take the advance of a security flaw and take over your installation. i am talking about the database name which basically leads a way..  
  
  ---
  
  ## 13 answers
  
  ### Answer by username_1 on 2015-10-26. Score: 99
  
  MySQL 5.7.5+ changed the way [GROUP BY](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) behaved in order to be SQL99 [compliant](https://en.wikipedia.org/wiki/SQL:1999) (where in previous versions it was not).
  
  There is a good overview of the changes [here](http://rpbouman.blogspot.com/2014/09/mysql-575-group-by-respects-functional.html).
  
  Unfortunately, this affects some core queries that Craft uses when fetching elements.
  
  The good news is that this has been fixed already in Craft 3 since we refactored the way that elements query grabs data and it no longer uses a GROUP BY.
  
  The bad news is that it's going to be pretty impractical to backport that fix into Craft 2.
  
  The workaround for Craft 2, if you're running MySQL 5.7.5+ is to edit your [my.cnf](https://dev.mysql.com/doc/refman/5.1/en/option-files.html) file and remove the `ONLY_FULL_GROUP_BY` option from [sql\_mode](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html). That will change `GROUP BY` behavior back to its pre-MySQL 5.7.5 behavior.
  
  **Update**:
  
  If, for whatever reason (primarily you're on shared hosting), you aren't able to change the `sql_mode` of your box at the environment level, as of the next Craft release ([post 2.6.2945](https://craftcms.com/changelog#build2945)), there is a new `initSQLs` database config settings you can set in your `craft/config/db.php`. It will accept an array of SQL statements to be executed immediately after the database connection has been established. If you're running into this MySQL 5.7 error, you can do:
  
  ```
  'initSQLs' => array("SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';")
  
  ```
  
  Just keep in mind this method will add at least one extra SQL query to every request.
  
  > **Comments:**
  > **username_9**: This bit me today and feels important enough to me that it should be mentioned in the main Craft install docs somewhere, especially as MySQL 5.7.5+ usage is on the up (I hit this with a box provisioned via Laravel Forge)  
  > **username_1**: Fair enough! Added a note to https://craftcms.com/docs/requirements  
  > **username_5**: Another reference: https://mattstauffer.co/blog/how-to-disable-mysql-strict-mode-on-laravel-forge-ubuntu  
  > **username_6**: username_1, you might be able to do a check for whether the server is running 5.7.5+ and add ANY_VALUE() to your columns if so? http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value  
  > **username_1**: @username_6 It gets messy because the query is so dynamic. Element types can set any custom select and order by values, so those would have to be wrapped in ANY_VALUE depending on what the GROUP BY specifies.  
  > **username_7**: "Impractical to backport" - Then don't. Best practice for Mysql 5.7 is to set the sqlmode in your DB drive on connect. Craft installations should not require setting this on the entire database instance in my.cnf, which is terrible practice. You should be implementing the one line change required to set this when you call the database connection.  http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html  
  > **username_8**: Thanks, the initSQLs allowed me to carry on working without having to mess about installing an older MySQL version or updating Craft to 3.x  
  > **username_10**: None of the solutions suggested are working for me. Is there any new issues as to why? My site is https://mentiondesign.co.uk/  
  > **username_11**: HEADS UP to Cloud SQL users - when you config the DB in the GCP console, the sql_mode flag is a drop-down menu, and only accepts one value. I found out from [this blog page](https://introvertedengineer.com/2017/03/23/mysql-sql_mode-troubles-google-cloud-sql/) that `TRADITIONAL` may work as an alias for the strict values you want, and none of the strict values you don't want. More on `TRADITIONAL` on the [MySQL 5.7 reference page](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_traditional).  
  > **username_12**: @username_1 Just curious.. does the initSQLs solution still run an extra query even if we are running MySQL5.6?  
  > **username_1**: @username_12, if you're using `initSQLs`, it will execute whatever the value is on every request no matter the MySQL version.  
  > **username_13**: This is the easiest solution to get it running if you work localy on MAMP  
  
  ---
  
  ### Answer by username_3 on 2016-02-11. Score: 41
  
  **Updated** *05/03/16*: I had a struggle getting this to work with [`Homestead 5.2`](https://laravel.com/docs/5.2/homestead). The MySQL error everyone else is seeing, ~~as well as `mcrypt` error, as [mycrypt is not installed by default](https://laracasts.com/discuss/channels/general-discussion/homestead-3-and-mcrypt/replies/134924) in [`Vagrant box laravel/homestead 0.4.1`](https://atlas.hashicorp.com/laravel/boxes/homestead/versions/0.4.1)~~ (Fixed in [vagrant box laravel/homestead 0.4.2](https://atlas.hashicorp.com/laravel/boxes/homestead/versions/0.4.2) — See [answer history](https://craftcms.stackexchange.com/posts/13662/revisions) for `0.4.1` how-to).
  
  **So, steps to success:**
  
  1. `vagrant ssh` into your box
  2. `sudo vim /etc/mysql/my.cnf`
  3. Scroll to the bottom of file and `A` to enter insert mode
  4. Copy and paste `[mysqld]
     sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION` to the bottom of the file
  5. `esc` to exit input mode
  6. `:wq` to save and close vim.
  7. `sudo service mysql restart` to restart MySQL.
  8. Done!
  
  *[Original how-to article](https://medium.com/@cobbers/tweak-homestead-v0-3-3-to-work-with-craft-2-bfa66beb2460#.vb0iul4jq)*.
  
  > **Comments:**
  > **username_14**: Hi @username_3 the latest version of Homestead v0.4.2 adds mcrypt back in.  
  > **username_3**: @username_14 Great! So is it just the MySQL settings that need to be changed now?  
  > **username_14**: Yeah that's exactly right. I did need to destroy my homestead box and install a new one. For some reason the update didn't work for me. So you just need to do from 4 onwards in your points once you have v0.4.2.  
  > **username_3**: @username_14 I updated and it seems to be working fine for me. But some of my previous updates seem to have carried over. It does seem to be a dark art getting the damn thing working sometimes! Thanks for the tip-off.  
  > **username_14**: yeah I'm still not sure all the messing you have to do is worth it. I'm going to stick with it for a while though and see how it goes.  
  > **username_15**: Had this problem on digitalocean too. This fix works :)  
  > **username_16**: This works for me! :)  
  
  ---
  
  ### Answer by username_17 on 2015-11-20. Score: 19
  
  To fix this I have created a `.my.cnf` in my home-directory with the following content:
  
  ```
  [mysqld]
  sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  
  ```
  
  > **Comments:**
  > **username_18**: Only solution that worked on my mac. Thanks.  
  
  ---
  
  ### Answer by username_19 on 2017-01-10. Score: 11
  
  For UBUNTU 16.04 LTS
  
  commands:
  
  ```
  cd /etc/mysql
  
  sudo nano my.cnf
  
  ```
  
  add these two lines:
  
  ```
  [mysqld]
  sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
  
  ```
  
  save file and reset your computer.
  
  > **Comments:**
  > **username_20**: This one works.  
  
  ---
  
  ### Answer by username_21 on 2015-11-24. Score: 8
  
  We have the same problem. MySQL version *(Ver 14.14 Distrib 5.7.9, for osx10.11 (x86\_64) using EditLine wrapper)*
  
  Had problems `SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression` and by adding the code at my.cnf file resolved it for me:
  
  ```
  [mysqld]
  sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  
  ```
  
  > **Comments:**
  > **username_22**: This fixed my problem with Laravel forge on DO with PHP 7 and MySQL 5.7.9.  
  
  ---
  
  ### Answer by username_23 on 2016-08-16. Score: 6
  
  Here are some fairly specific instructions if you happen to be hosted on a server configured through ServerPilot.
  
  <https://serverpilot.io/community/articles/how-to-disable-strict-mode-in-mysql-5-7.html>
  
  
  ---
  
  ### Answer by username_24 on 2015-12-03. Score: 4
  
  MySQL 5.7.5+ changed the way GROUP BY behaved in order to be SQL99 compliant (where in previous versions it was not). Either modify the existing mysql queries to be compatible to the new mysql version or make mysql behave as in previous versions , by executing the below queries on the mysql .
  
  SET sql\_mode = ''; // current client
  
  SET GLOBAL sql\_mode = ''; // for other clients like hibernate
  
  Source: <http://www.proactiveclass.com/tutorials/java/hibernate/sql-error-group-by-incompatible-with-sql-mode-only-full-group-by>
  
  
  ---
  
  ### Answer by username_25 on 2017-07-28. Score: 4
  
  The proper fixes have already been answered here; but I'd like to note that another fix is to use MariaDB instead of MySQL. It's a drop-in replacement for it, it's more performant for a number of metrics, and it's created by the original author of MySQL.
  
  Give it a go; you won't regret it.
  
  
  ---
  
  ### Answer by username_26 on 2015-12-13. Score: 3
  
  I faced this problem when I upgraded MySQL to version >= 5.7.5 and I'v fixed it by following these steps:
  
  1. SSH into your server.
  2. Add `sql-mode=""` to your MySQL config:
  
     ```
     echo -e '[mysqld]\nsql-mode=""' | tee --append /etc/mysql/conf.d/craft.cnf > /dev/null
  
     ```
  3. Restart MySQL service:
  
     ```
     service mysql restart
  
     ```
  
  ---
  
  Note: you might need to add `sudo` to those commands.
  
  
  ---
  
  ### Answer by username_27 on 2018-12-17. Score: 2
  
  If you're using MAMP, it will constantly complain about existence of a "*global*" **my.cnf** file.  
  So, you need to update MySQL config template file inside your MAMP application.
  
  1. Start MAMP
  2. Go *File* > *Edit Template* > *MySQL (my.cnf)* > "*choose whatever version you're using*"
  3. Dump these two lines in the end of the opened file:
  
  > [mysqld]
  > sql\_mode=STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION
  
  Cheers!
  
  
  ---
  
  ### Answer by username_28 on 2016-03-26. Score: 2
  
  Following on from [Rob's answer](https://craftcms.stackexchange.com/a/13662/4937), for anyone running into this issue on Laravel Homestead, we can use Bash to script the change. That way, the edit to the `my.cnf` file is done automatically for any future VMs created using `vagrant up`.
  
  To do this, simply add the following to your `~/.homestead/after.sh` file:
  
  ```
  STR="sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  if ! grep -Fxq "$STR" /etc/mysql/my.cnf
  then
      printf "\n# Craft 2 compatibility\n[mysqld]\n$STR" >> /etc/mysql/my.cnf
      service mysql restart
  fi
  
  ```
  
  The above code searches `/etc/mysql/my.cnf` for the line `'sql_mode=...'`, and if it cannot be found, it is automatically appended, together with the preceding `[mysqld]` to the end of the file. The mysql service is then restarted to enable the change.
  
  
  ---
  
  ### Answer by username_29 on 2024-07-03. Score: 2
  
  Just in case somebody has problems with `'initSQLs'` on ddev: I had to remove 'NO\_AUTO\_CREATE\_USER'.
  
  ```php
  <?php
  /**
   * Database Configuration
   */
  
  return array(
      '*' => array(
          'server' => 'db',
          'user' => 'db',
          'password' => 'db',
          'database' => 'db',
          'initSQLs' => array("SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';")
      ),
  
  );
  
  ```
  
  
  ---
  
  ### Answer by username_30 on 2016-07-28. Score: 1
  
  open terminal :
  
  "sudo nano /etc/mysql/my.cnf"
  
  Scroll to the bottom of file and A to enter insert mode
  Copy and paste
  
  [mysqld]
  sql\_mode=STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION
  
  save file then restart mysql service by the below command :
  
  "sudo service mysql restart" to restart MySQL.
  
  
  ---
  
  