MySQL 5.7 on Mac OS X 10.11 El Capitan

So for reasons too extensive to get into in this post, I decided to make the jump on my MacBook Pro from OS X 10.8 (Mountain Lion) to the latest OS X which at the time was 10.11 El Capitan. Prior to making this leap, I checked the compatibility of all my mission critical applications. Naturally, however, the upgrade came with many challenges when it came to setting up the local web application development framework. Apache was fairly straightforward, as was PHP 5.6. MySQL was another story. The upgrade went smoothly, but it crushed the life out of a few legacy applications due to the ONLY_FULL_GROUP_BY mode.

I realize that the real solution is to fix my code, but much of it will likely be abandoned before too long, so it's not worth the effort. Fortunately, I discovered the way to permanently switch off this mode. It's simple enough to use the sql_mode command inside a mysql shell, but it took a bit of hunting to make this permanent. The solution was as follows. First a summary of the steps:

1. Determine the current sql_mode. Copy this down for later.
2. Determine the location(s) that the mysql daemon searches for its configuration files.
3. Edit one of these files and add the sql_mode configuration minus the ONLY_FULL_GROUP_BY
4. Restart the mysql daemon

Now in greater detail, with my particulars:

$ mysql -u  -p -e "select @@sql_mode"

Substitute your specific mysql user, and enter the password. The output will look something like:

+------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------+


By the way, there should be no spaces in the output above when you copy it into the config file later on.
You'll be editing that line and saving it into your mysqld config file. Next:

$ which mysqld

Using the full path the the mysql daemon from the previous step:

$ /usr/local/mysql/bin/mysqld --verbose --help | grep -A 1 "Default options"

The output of this command will indicate where mysqld will search for config files. If the above generates a permission error, you may need to use the "sudo" command. In my case, none of these files existed, so I created one in the first search location.

$ sudo vi /etc/my.cnf

Add the edited line from the first step into the [mysqld] section, or create a new [mysqld] section:

[mysqld]
# May be other stuff here
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Again, this was the sql_mode based on MY particulars. Yours might differ. Save the file, then do the following two commands to reload the mysql daemon:

$ sudo launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
$ sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

That did it for me!