MySQL Bugs: #75551: sql_mode not respected properly in 5.6.15

Bug #75551	sql_mode not respected properly in 5.6.15
Submitted:	19 Jan 2015 10:12	Modified:	26 Jan 2015 12:22
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	5.6.15	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	SQL_MODE

Description:
sql_mode on 5.6.15 does not seem to be as defined in the documentation.

When setting sql_mode = NO_ENGINE_SUBSTITUTION in /etc/my.cnf on startup the server seems to end up running with this setting: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION which is not expected.

A similar server running 5.6.17 seems to be ok. I don't see any bug reports for this issue in the 5.6.16 or 5.6.17 change logs but I do see other comments about sql_mode in the 5.6.17 change log, so assume the issue was fixed then.

I run a script which automatically reconfigures the running configuration of MySQL based on /etc/my.cnf. This script normally fixes the setting so the issue had not been noticed, but on the server concerned that script was (deliberately) not running.

Given 5.6.17 seems to be have correctly this bug report is just for people who may also come into the problem and be somewhat surprised by it.

Noticed in this rpm: MySQL-server-5.6.15-1.el6.x86_64

How to repeat:
[root@myhost ~]# grep sql_mode /etc/my.cnf
sql_mode = NO_ENGINE_SUBSTITUTION
[root@myhost ~]# mysql -e "SELECT @@sql_mode"
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
[root@myhost ~]# /etc/init.d/mysql  restart
Shutting down MySQL.............                           [  OK  ]
Starting MySQL...............                              [  OK  ]
[root@myhost ~]# mysql -e "show status like 'uptime'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 22    |
+---------------+-------+
[root@myhost ~]# grep sql_mode /etc/my.cnf
sql_mode = NO_ENGINE_SUBSTITUTION
[root@myhost ~]# mysql -e "SELECT @@sql_mode"
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
[root@myhost ~]# mysql
...
root@myhost [(none)]> set global sql_mode = 'NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

root@myhost [(none)]> select @@global.sql_mode;
+------------------------+
| @@global.sql_mode      |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

root@myhost [(none)]>

Suggested fix:
The issue seems to be fixed. This report is just a FYI for anyone using this specific version of MySQL.

I cannot repeat on 5.6.15:

./bin/mysqld_safe  --no-defaults --sql_mode='NO_ENGINE_SUBSTITUTION' &

mysql> select @@global.sql_mode;
+------------------------+
| @@global.sql_mode      |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

Maybe a duplicate entry in my.cnf (sql-mode vs sql_mode), or caps.
Or a rogue my.cnf in another path?

Shane, this was set via /etc/my.cnf having the value:

[me@myhost ~]$ grep -3 sql_mode /etc/my.cnf
sort_buffer_size = 1M
# Per thread settings - end

sql_mode = NO_ENGINE_SUBSTITUTION

table_definition_cache = 3000
table_open_cache = 3000
[my@myhost ~]$ 

The running config of this server (with some details slightly modified to hide real hostnames/paths) is:

[me@myhost ~]$ ps auwx | grep mysqld
root      2472  0.0  0.0 108304  1672 ?        S    Jan15   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/path/to/datadir --pid-file=/path/to/datadir/myhost.pid
mysql     3994 88.5 55.8 192705508 110689624 ? Sl   Jan15 5918:47 /usr/sbin/mysqld --basedir=/usr --datadir=/path/to/datadir --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --open-files-limit=20000 --pid-file=/path/to/datadir/myhost.pid --socket=mysql.sock

What about infamous /usr/my.cnf? I wonder if you have it and if it re-defined sql_mode.

Thank you for the feedback.

Please provide output of 

sudo -u mysql /usr/sbin/mysqld --verbose --help 2> /dev/null | grep -B 1 '/etc/my.cnf'

[root@myhost ~]# sudo -u mysql /usr/sbin/mysqld --verbose --help 2> /dev/null | grep -B 1 '/etc/my.cnf'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
[root@myhost ~]# cat /etc/my.cnf
[mysqld]
datadir      = /var/lib/mysql
socket       = mysql.sock

sql_mode = NO_ENGINE_SUBSTITUTION

[mysqld_safe]
log-error = /var/log/mysqld.log
pid_file = /var/lib/mysql/mysqld.pid

[mysql]
no-auto-rehash
default_character_set = utf8
prompt = \u@myhost [\d]>\_

[client]
socket = /var/lib/mysql/mysql.sock
[root@myhost ~]# cat /etc/mysql/my.cnf
cat: /etc/mysql/my.cnf: No such file or directory
[root@myhost ~]# cat /usr/etc/my.cnf
cat: /usr/etc/my.cnf: No such file or directory
[root@myhost ~]# cat ~/.my.cnf
cat: /root/.my.cnf: No such file or directory
[root@myhost ~]#

So no, it doesn't look like a stray my.cnf file is to blame here.

///

####### Confirmed sql_mode settings in /etc/my.cnf and restarted mysql server

[root@ushastry ushastry]# /etc/init.d/mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL..                                           [  OK  ]
[root@ushastry ushastry]# more /etc/my.cnf 
[mysqld]
basedir=/usr
datadir=/var/lib/mysql/
sql_mode = NO_ENGINE_SUBSTITUTION

[root@ushastry ushastry]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.17 MySQL Community Server (GPL)

.

mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

//

root@ushastry ushastry]# sudo -u mysql /usr/sbin/mysqld --verbose --help 2> /dev/null | grep -B 1 '/etc/my.cnf'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
[root@ushastry ushastry]# ls -l /etc/my.cnf 
-rw-r--r--. 1 root root 44 Jan 21 16:19 /etc/my.cnf
[root@ushastry ushastry]# ls -l /etc/mysql/my.cnf
ls: cannot access /etc/mysql/my.cnf: No such file or directory
[root@ushastry ushastry]# ls -l /usr/etc/my.cnf 
ls: cannot access /usr/etc/my.cnf: No such file or directory
[root@ushastry ushastry]# ls -l ~/.my.cnf
ls: cannot access /root/.my.cnf: No such file or directory

####### 
####### Checked if there are any other my.cnf
####### 
[root@ushastry ushastry]# find / -name my.cnf
/usr/share/mysql-test/suite/ndb/my.cnf
/usr/share/mysql-test/suite/rpl/extension/bhs/my.cnf
/usr/share/mysql-test/suite/rpl/my.cnf
/usr/share/mysql-test/suite/ndb_rpl/my.cnf
/usr/share/mysql-test/suite/ndb_team/my.cnf
/usr/share/mysql-test/suite/federated/my.cnf
/usr/share/mysql-test/suite/ndb_binlog/my.cnf
/usr/share/mysql-test/suite/rpl_ndb/my.cnf
/usr/share/mysql-test/suite/ndb_big/my.cnf
/usr/my.cnf
/etc/my.cnf

####### 
####### existing /usr/my.cnf
####### 

[root@ushastry Downloads]# more /usr/my.cnf
# 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 

####### 
####### Will modify this file and see if this is the case
####### 

[root@ushastry Downloads]# 
[root@ushastry Downloads]# grep sql_mode /usr/my.cnf
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
sql_mode=NO_ENGINE_SUBSTITUTION 

// restarted mysql server

[root@ushastry Downloads]# /etc/init.d/mysql restart
Shutting down MySQL....                                    [  OK  ]
Starting MySQL..                                           [  OK  ]

####### 
####### Confirm settings
####### 

[root@ushastry ushastry]# mysql -u root -p
.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show global variables like 'sql_mode';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.01 sec)

In my tests - /usr/my.cnf was culprit, after modifying that file it works as expected

Hi Simon,

Could you please check whether /usr/my.cnf exists in your environment?
In my tests it caused issues as you can see in my previous note.

Thanks,
Umesh

Also, pls see - http://bugs.mysql.com/bug.php?id=71600

Hi. I need to be absolutely sure but yes, it does appear that this is related.
Thanks for the headsup on it in bug#71600. I'll do a further check shortly, but it may take a short while to be able to confirm this.

bug#71600 seems to explain this problem.