Set sql_mode “blank” after upgrading to MySQL 5.6

Question

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 ?

Answer 1

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

Answer 2

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.