7

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 '15 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. – RolandoMySQLDBA Aug 5 '15 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. – RolandoMySQLDBA Aug 5 '15 at 16:29
6

PROLOGUE

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.

ROOT CAUSE

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
  • NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES in 5.6.6 and into GA

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

[mysqld]

# 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

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

See line 28 ?

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

SOLUTION

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

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

STEP 02 : Set sql_mode by hand

mysql> SET GLOBAL sql_mode = '';

THAT'S IT !!!

EPILOGUE

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

GIVE IT A TRY !!!

| improve this answer | |
  • On 5.6.12, sql_mode = seems give blank setting, whereas no mention of sql_mode gives NO_ENGINE_SUBSTITUTION. – Rick James Aug 5 '15 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 '15 at 22:46
  • I get a blank setting when using sql_mode = ''. – Rick James Aug 5 '15 at 22:48
5

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

in my.cnf:

sql_mode=ALLOW_INVALID_DATES

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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