After upgrading MySQL from 5.5 to 5.6, some our app face to problem and need set sql_mode to blank to solve this issue. I added sql_mode = '' to my.cnf but there was no effect on the mysql setting.

How do I keep the sql_mode blank ?

  • Bad idea - check out the documentation. Some of those settings are important.
    – Vérace
    Aug 5, 2015 at 7:54
  • You get a +1 for this because many developers have been mystified by this. Thank you for posting this question about sql_mode. Aug 5, 2015 at 16:26
  • I owe you an apology. I answered your earlier question (dba.stackexchange.com/a/101504/877) but never gave you a thorough answer. I hope the answer to this helps. Aug 5, 2015 at 16:29

2 Answers 2



Someone asked the same thing of me in my organization because everyone was using MySQL 5.5. All DB servers was upgraded over the past 8 months to MySQL 5.6. Some client applications were being affected by sql_mode change as well.


I just found out why what you did does not work and the workaround is very simple.

According to MySQL 5.5 Documentation, sql_mode default is a blank sting.

According to MySQL 5.6 Documentation, sql_mode is default is

  • blank string in MySQL 5.6.5 and back

OK, I hope you are sitting down.

This is the lazy way Oracle implemented sql_mode in MySQL 5.6: There is an additional my.cnf file.

If you run

cat /usr/my.cnf

you will see the following

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html


# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M


See line 28 ?



STEP 01 : Comment out line 28 of /usr/my.cnf


STEP 02 : Set sql_mode by hand

mysql> SET GLOBAL sql_mode = '';



  • STEP 01 prevents restarts of mysqld from changing sql_mode.
  • STEP 02 sets sql_mode now, so restarting mysqld is not needed immediately


  • On 5.6.12, sql_mode = seems give blank setting, whereas no mention of sql_mode gives NO_ENGINE_SUBSTITUTION.
    – Rick James
    Aug 5, 2015 at 22:45
  • 5.6.6 and 5.6.8 changed the defaults. 5.7.5 will change the default again.
    – Rick James
    Aug 5, 2015 at 22:46
  • I get a blank setting when using sql_mode = ''.
    – Rick James
    Aug 5, 2015 at 22:48

With 5.7.13 (and possibly other 5.7 variants), the following trick works:

in my.cnf:


and restart the server. If you do not want to restart, you can just do:

set global sql_mode='ALLOW_INVALID_DATES'

then mysqld forgets all of its pedantic defaults and behaves like default-configured 5.5.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.