Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by 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.
MySQL 5.7.5+ changed the way GROUP BY behaved in order to be SQL99 compliant (where in previous versions it was not).

There is a good overview of the changes here.

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 file and remove the ONLY_FULL_GROUP_BY option from sql_mode. 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), 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' =&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';")


Just keep in mind this method will add at least one extra SQL query to every request.