Help with admin panel errors. Fresh install. Mysql version issue? [duplicate]

Question

I get this on the admin panel: CDbException

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 'craftcms.structureelements.root' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by. The SQL statement executed was: SELECT elements.id, elements.type, elements.enabled, elements.archived, elements.dateCreated, elements.dateUpdated, .....

There's a long trail in the apache error log. here's a snippet. 2017/04/27 04:46:46 [trace] [system.db.CDbConnection] Opening DB connection in /var/www/craft/app/etc/behaviors/AppBehavior.php (871)

Env details: Ubuntu 1.04 (fresh digital ocean vps) php - PHP 7.0.15-0ubuntu0.16.04.4 (cli) ( NTS ) mysql - 5.7.17-0ubuntu0.16.04.2

Answer 1

You need to tweak your MySQL 5.7.5+ config for it to play nicely with Craft.

MySQL 5.7.5 changed the behaviour of the GROUP_BY statement, affecting some of the core Craft queries.

The preferred solution is to add the following to your /etc/mysql/conf.d/sql_mode.cnf 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

This Craft Cookbook recipe provides additional information about the problem, and the possible solutions.