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

[19 Jan 2015 10:12] Simon Mudd
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.
[19 Jan 2015 12:43] Shane Bester
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?
[20 Jan 2015 7:03] Simon Mudd
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
[20 Jan 2015 10:31] Valeriy Kravchuk
What about infamous /usr/my.cnf? I wonder if you have it and if it re-defined sql_mode.
[20 Jan 2015 18:04] Sveta Smirnova
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'
[20 Jan 2015 23:35] Simon Mudd
[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.
[21 Jan 2015 11:17] Umesh Shastry
///

####### 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
[21 Jan 2015 11:20] Umesh Shastry
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
[21 Jan 2015 11:53] Umesh Shastry
Also, pls see - http://bugs.mysql.com/bug.php?id=71600
[21 Jan 2015 15:42] Simon Mudd
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.
[26 Jan 2015 12:22] Simon Mudd
bug#71600 seems to explain this problem.