New Mac

El Capitan 10.11.2


I have to work with a database that has default dates "0000-00-00" but MySQL is in strict mode and chokes on them.

I can't find a way to switch off strict mode. I have [mysqld] sql-mode=""; in ~/my.cnf, but this makes no difference. I wonder if something is resetting it further up the food chain but I have no idea where to look. I have tried /usr/local/mysql/my.cnf. I have tried with the files called .my.cnf. The OS won't let me create /usr/my.cnf (an Apple security feature apparently).

Where else might I look; what else might I try?

Also some websites say sql_mode (underscore, not hyphen); which is correct?


  • use set sql_mode='' it is session level variable, use set global sql_mode='' to set is globally..hope it helps Commented Jan 19, 2016 at 15:35

2 Answers 2


It turns out that the problem was ending the lines in my.cnf with semicolons. Leave them off, and /etc/my.cnf is read and stops strict mode.


You can start mysql with strace and look for my.cnf. It will try a couple and open the one which is present. It was described in more details in an other answer: https://stackoverflow.com/questions/580331/determine-which-configuration-file-is-being-used

Changing it on the fly is also easy since sql_mode is a dynamic parameter you can set it in mysql console.

show global variables like 'sql_mode';
set global sql_mode='...';  -- change whatever you need here

More on the modes: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.