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.cnfYou 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_SUBSTITUTIONSave the file and then restart mysql. With homebrew, I just run…
brew services restart mysqlAnd 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.cnfPaste:
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONCTRL + X, y, Enter
brew services restart mysqlRetry craft install.
