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.

score 42 · Answer 1 · answered Dec 23 '15 at 00:42

42

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!

answered Dec 23 '15 at 00:42

Paul Warren

2,411
1
15
22

4

Same thing happened on mine, Mac OS X, El Capitan. sql_mode only worked when set in /etc/mysql/my.cnf. I had to create the folder and file too. – Chiko Feb 18 '16 at 11:08
this did not help me, – Lazarus Thurston Aug 24 '17 at 06:02
1

simply creating /etc/my.cnf file worked. By default, the OS X installation does not use a my.cnf, and MySQL just uses the default values. – sadiq May 08 '18 at 10:38
This worked for me too. I made it a symlink to my `~/.my.cnf` file which it wouldn't read. Can't believe this is still an issue. Or perhaps it tries to read from the home folder of the `_mysql` user, since it's running as that user – ar34z Jun 22 '18 at 13:23
this helped - mysql 8.0.21 on Mac 10.15.5. – Tirath Nov 01 '20 at 10:57

score 14 · Accepted Answer · answered Mar 08 '14 at 05:52

14

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.

answered Mar 08 '14 at 05:52

firedev

20,898
20
64
94

7

That should actually be ```set global sql_mode=''``` :-) – Thomas Feb 25 '16 at 14:49
2

Warning: using the `set global` query to set the sql_mode doesn't persist across MySQL server restarts. You're better off following the advice [above](https://stackoverflow.com/a/34426883/703200). – Chris Bartley Sep 11 '17 at 19:01

score 9 · Answer 3 · answered Mar 14 '14 at 10:41

9

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

answered Mar 14 '14 at 10:41

vd1008

448
4
5

This was the case on a fresh install (WHM) CentOS 6.7 with MariaDB 5.5.49. The config file that was overwriting `/etc/my.cnf` was `/usr/my.cnf` and had the line `sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES` – William Isted May 10 '16 at 17:40
This solved for me. The alternative was updating legacy code already slated for replacement to include default values in table creation. – Mike Morris - MBXSW May 24 '16 at 17:56
The same problem was on Debian 7 Wheeze – Vedmant Feb 06 '17 at 15:43

score 6 · Answer 4 · answered Dec 13 '13 at 22:28

6

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.

answered Dec 13 '13 at 22:28

ThatOneDude

1,516
17
20

Not exactly: `mysql --help | grep /my` gives the following list: `/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf` And I don't have any of these files, so I would like to know the exact syntax for creating one. – firedev Dec 14 '13 at 11:26
You should be able to use `strace -f -e trace=open mysql 2>&1 | grep -i cnf` to see which cnf file you are actually using. After that, you can either edit the one it's using (if it already contains a sql_mode directive) or create a new one at any of the searched for locations that contains the `sql_mode` directive inside the `[mysqld]` section. See [mysql configuration file sample](http://www.fromdual.com/mysql-configuration-file-sample) for a nicely documented example file. – ThatOneDude Dec 14 '13 at 22:21
Thanks for the link. I am on Mac, don't have strace. However here is my `/etc/my.cnf`: `[mysqld] sql_mode=NO_ENGINE_SUBSTITUTION` and it doesn't work, `SHOW VARIABLES` returns `sql_mode STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION` – firedev Dec 16 '13 at 03:18
1

Hello Nick, did you use homebrew to install this on your Mac? If so, I'd have you check the homebrew install directory for my.cnf files as well. This might be something like `/usr/local/Cellar/mysql/5.X.XX/my.cnf` – ThatOneDude Jan 07 '14 at 02:31

score 4 · Answer 5 · answered Nov 24 '16 at 12:47

4

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

answered Nov 24 '16 at 12:47

Aleksandr Sobakar

41
3

score 3 · Answer 6 · answered Apr 18 '17 at 12:44

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.

This works for me on Mac OS Mojave. Even with Maria DB – Tiger developer Jun 04 '19 at 20:30 — Tiger developer, Jun 04 '19 at 20:30

score 0 · Answer 7 · answered Jul 26 '16 at 14:32

0

On Mac OS X El Capitan i created a file .my.cnf in the user home dir and set the settings for mysql under [mysqld] and then restarted mysql. Worked fine!

answered Jul 26 '16 at 14:32

Kjell

832
7
11

That didn't work for me. How did you configure it to look for the .my.cnf file in your home directory? – Kudit Jan 30 '17 at 19:03

How to get rid of STRICT SQL mode in MySQL

7 Answers7

Linked