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

This problem scuppered me for a while as well. None of the answers so far addressed the original problem but I believe mine does so I'll post it in case it helps anyone else.

I have MySQL (from mysql.com) Community Edition 5.7.10 installed on OS X 10.10.3

In the end I created a /etc/mysql/my.cnf with the following contents:-

[mysqld]  sql_mode=NO_ENGINE_SUBSTITUTION 

After restarting the server a SHOW VARIABLES LIKE 'sql_mode'; gave me:-

+---------------+------------------------+ | Variable_name | Value                  | +---------------+------------------------+ | sql_mode      | NO_ENGINE_SUBSTITUTION | +---------------+------------------------+ 1 row in set (0.00 sec) 

Finally, no strict mode!