How to get rid of STRICT SQL mode in MySQL

Question

This is a follow up to this question MYSQL incorrect DATETIME format

How to get rid of STRICT_TRANS_TABLES once and for all?

mysql --help reports the following configs:

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

$ ls  /etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
ls: /Users/pain/.my.cnf: No such file or directory
ls: /etc/mysql/my.cnf: No such file or directory
ls: /usr/local/etc/my.cnf: No such file or directory
/etc/my.cnf

$ cat /etc/my.cnf
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION

But this doesn't help. I have some legacy code and each time I reboot the computer I have to launch mysql and change sql_mode.

Update

So I gave up on Homebrew-installed MySQL and downloaded it from from mysql.com. But that didn't help either. Following the answers here: How to fix `unknown variable 'sql-mode=ANSI'`? I have tried different variations of /etc/my.cnf: [mysql], [mysqld], sql_mode, sql-mode – nothing helped.

Answer 1

I tried every answer I could find on this issue using MySQL 5.7 on Mac OS 10.12 and ultimately got strict mode turned off not because of the location of my.cnf, which can presumably be in any of the places that MySQL says it checks, but thanks to a UNIX permissions issue.

I used MySQL Workbench 6.2.3.12313 to create my.cnf initially. This caused two possible problems: first, it set the option to "sql-mode" instead of "sql_mode", and it made the file (located in /etc) readable and writable only for root. MySQL does not run as root when you install it the way I did, from the binary package on the MySQL web site--it runs as _mysql. So the _mysql user needs to be able to read /etc/my.cnf, or wherever you put it. In order for that to work, you need to run:

sudo chmod o+r /etc/my.cnf

and for good measure you may also want to run:

sudo chmod g+r /etc/my.cnf

Then make sure to restart MySQL. (I have found that this works best through the System Preferences MySQL panel on Mac OS; using the command line is kind of messy and MySQL Workbench's functionality simply doesn't work.) So long as you have an sql_mode setting in my.cnf that does not involve strict mode, strict mode should be off.