Set sql_mode “blank” after upgrading to MySQL 5.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&gt; 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 !!!