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.

Solution 1

So in the end I removed the MySQL Server I got from the mysql.com, reinstalled it via Homebrew and had to edit

/usr/local/Cellar/mysql/5.6.xx/my.cnf

Where I could comment out the darned STRICT_TRANS_TABLES.

However this doesn't explain why the default config overrides the one from /etc/my.cnf, but I spent too much time on this already as it is. And by the way I am still not sure what to do with the mysql.com provided distribution.

Solution 2

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!

Solution 3

On Centos 6.5 i had to edit /usr/my.cnf and set (even though /etc/my.cnf existed and bindings were successfully set there

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION

package was from:

mysql-community-client.x86_64      5.6.16-1.el6            @mysql56-community

Solution 4

According to MySQL Strict Mode on OS X the problematic setting is actually at /usr/local/mysql/my.cnf and can be commented out to stop this behavior.

Solution 5

Now you can`t set sql_mode to empty string, actual query is:

SET @@GLOBAL.sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

MySQL 5.7.16

View more solutions