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.

Malva

Answer 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.

Malva

That should actually be set global sql_mode='' :-) – Alyshaalysia

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. – Musing

Answer 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!

Commiserate

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. – Cabot

this did not help me, – Bellyband

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. – Whitford

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 🤔 – Mantellone

this helped - mysql 8.0.21 on Mac 10.15.5. – Moderate

Answer 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

Redress

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 – Bringingup

This solved for me. The alternative was updating legacy code already slated for replacement to include default values in table creation. – Brittni

The same problem was on Debian 7 Wheeze – Shiite

Answer 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.

Ganister

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. – Malva

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 for a nicely documented example file. – Ganister

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 – Malva

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 – Ganister

Answer 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

Gonfanon

Answer 6

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.

Lucky

This works for me on Mac OS Mojave. Even with Maria DB – Vaulted

Answer 7

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!

Symons

That didn't work for me. How did you configure it to look for the .my.cnf file in your home directory? – Jellyfish