I'm a big fan of the Mac package management tool called Homebrew. But I noticed one little quirk about how it installs MySQL, and I thought I'd blog about to so I can look up how to fix it in the future!
(This may actually be an issue with Homebrew itself, and I may look into what it takes to getting it fixed for realsy, but for now, this blog post for future-Cameron to read and remind him how to work around this.)
So for some reason, MySQL doesn't symlink a my.cnf in /usr/local/etc/ like it (at least seems) to for other similar tools. And not only that, but for some strange reason (and this is the part I can't figure out), it doesn't allow you to override options in /etc/my.cnf, despite mysqld --help --verbose telling you:
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
I assure you: I am nothing if not complexerated.
Why does all this matter anyway?
In my projects, 99% of the time, I've never had to mess with MySQL settings. It's only recently that I updated MySQL via Homebrew that I started having issues. The culprit? MySQL's strict mode. If enabled (which most hosts I've used and Mac's default MySQL install seem not to have it enabled by default), it will fail on certain queries that would normally work. It seems that Homebrew's default MySQL config has it enabled.
For example, in situations where you have a datetime field, you can give it an empty string and it will enter an empty date and time (0000-00-00 00:00:00). But if you are in strict mode, this will throw an error and the query will fail.
This is super frustrating if you are using a third party tool that doesn't respect the strict mode and suddenly you are getting MySQL errors left and right!
So how the !@#$ do I disable it??
It's relatively easy. You just remove anything with "strict" in it from the sql_mode flag in your my.cnf.
Oh wait... as we just discussed, we don't know where our my.cnf is thanks to Homebrew!
Well, hopefully that's what brought you here! I'm gonna tell you how to find Homebrew's my.cnf curtsy of this post on StackOverflow:
ls $(brew --prefix mysql)/support-files/my-*
(If using default brew settings, it'll probably return /usr/local/opt/mysql/support-files/my-default.cnf)
Run that in bash and it should output the path to any MySQL config files you have. The file (unless things change from the time I'm writing this) is actually my-default.cnf.
Go to that file, find sql_mode and you'll probably find a STRICT_TRANS_TABLES on that same line. Remove that and restart mysqld and you should be all good to go! No more seemingly random errors from supposedly stable code.