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 ?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.6 Documentation, sql_mode is default is
NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESin 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
If you run
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 ?
STEP 01 : Comment out line 28 of
STEP 02 : Set sql_mode by hand
mysql> SET GLOBAL sql_mode = '';
THAT'S IT !!!
STEP 01prevents restarts of mysqld from changing sql_mode.
STEP 02sets sql_mode now, so restarting mysqld is not needed immediately
With 5.7.13 (and possibly other 5.7 variants), the following trick works:
and restart the server. If you do not want to restart, you can just do:
set global sql_mode='ALLOW_INVALID_DATES'
mysqld forgets all of its pedantic defaults and behaves like default-configured 5.5.