or browse the recipes

Making Craft Play Nicely With MySQL 5.7.5+

Problem

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.

Solution

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.

[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

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.

  1. You’ll probably need root permissions to do this. ↩︎

  2. The initSQLs configuration parameter was introduced in Craft 2.6.2945. ↩︎

Discussion

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