MySQL 5.7.12 / CraftCMS install fix

I recently upgraded to MySQL 5.7.12 before embarking on a new website build using CraftCMS (which, by the way, is a great bit of kit). I’ve never had a problem installing CraftCMS before, but the install kept failing afer installing version 5.7.12 of MySQL.

Why?

It turns out the the behaviour of ‘GROUP BY’ has been changed in this newer version of MySQL (to become SQL:1999 compliant). This causes some of the database queries that the install scripts use to fail.

The workaround

Two options: 1) downgrade MySQL to an earlier version or 2) make an edit to my.cnf

Edit my.cnf

I use Homebrew on my Mac meaning that when MySQL installed there is a default my.cnf file stored at:

/usr/local/opt/mysql/support-files/my-default.cnf

You need to copy this file to…

/usr/local/etc/

…and then rename it to my.cnf

The quick way to do this is to type…

cp $(brew --prefix mysql)/support-files/my-default.cnf /usr/local/etc/my.cnf

…into your terminal and hit enter.

Once this is done, you then need to edit the newly created my.cnf file by adding the following…

[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 the file and then restart mysql. With homebrew, I just run…

brew services restart mysql

And voila, CraftCMS installation will now work.

The guys at Craft say that the up and coming Craft3 already has a fix included within it so this problem will eventually be non-existent. But as Craft 3 is not yet production-ready, those of you that want to use Craft 2.6* and MySQL 5.7.12 (or later) can use the above work around.

TL;DR?

In terminal run:

cp $(brew --prefix mysql)/support-files/my-default.cnf /usr/local/etc/my.cnf

nano /usr/local/etc/my.cnf

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

CTRL + X, y, Enter

brew services restart mysql

Retry craft install.