You’ve installed Craft on a server running MySQL 5.7.5+, and have encountered the dreaded
GROUP BY incompatible with sql_mode=only_full_group_by error.
MySQL 5.7.5 changed the behaviour of the
GROUP_BY statement, affecting some of the core Craft queries.
There are two possible solutions to this problem.
Option 1: Update your MySQL configuration
Rather than directly editing your MySQL configuration file, you should create a separate “override” file, which will be automatically loaded by MySQL.
The following steps assume your server is running Ubuntu 16.04, but the principle holds for all Linux-based servers:
SSH into your server, and create a new file at
/etc/mysql/conf.d/sql_mode.cnf, containing the following configuration settings1.
Restart the MySQL server, by running
sudo service mysql restart.
Option 2: Use the
initSQLs configuration parameter
You can set the
sql_mode on the fly by setting the
initSQLs configuration parameter2 in your
craft/config/db.php file, as follows:
[ "initSQLs => [ "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';" ], ],
It’s worth noting that this solution will add at least one extra database query to every request. As such, you should use option one, wherever possible.
The above solutions are discussed in more detail in this Stack Overflow thread.
ServerPilot also has an article detailing how to install Craft CMS, which covers the MySQL 5.7.5 issue.
Submitted by Stephen Lewis on 7th February, 2017