9

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 ?

RolandoMySQLDBA
  • 171,728
  • 30
  • 293
  • 486
Pardis
  • 153
  • 1
  • 2
  • 9
  • Bad idea - check out the [documentation](https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html). Some of those settings are important. – Vérace Aug 05 '15 at 07: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 05 '15 at 16:26
  • I owe you an apology. I answered your earlier question (http://dba.stackexchange.com/a/101504/877) but never gave you a thorough answer. I hope the answer to this helps. – RolandoMySQLDBA Aug 05 '15 at 16:29

2 Answers2

8

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

RolandoMySQLDBA
  • 171,728
  • 30
  • 293
  • 486
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.

Sasha Pachev
  • 191
  • 1
  • 3