MySQL Bugs: #70062: sql_mode option file cannot set to null

Bug #70062	sql_mode option file cannot set to null
Submitted:	16 Aug 2013 18:38	Modified:	19 Aug 2013 14:47
Reporter:	Kelvin Mok	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	5.6.11-log	OS:	Linux (CentOS 5.7)
Assigned to:		CPU Architecture:	Any
Tags:	option, SQL_MODE

Description:
The default SQL Mode setting for 5.6.11-log when set shows STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
when you use SHOW GLOBAL VARIABLES;

In the option file my.cnf, if you attempt to set
sql-mode=''
to get it to be null with no SQL modes enabled, it does NOT take effect and reverts to as if the variable was not in the option file at all.

When you set sql-mode to something, it works as expected, but you cannot define a null value to expect it to use a null value.

This is needed for legacy applications that expect sql-mode to be null, this is especially needed if Bug #42034 is to be implemented.

How to repeat:
In the option file, set
sql-mode='STRICT_TRANS_TABLES'
and it works normally.

But with
sql-mode=''
the SQL Mode defaults to the version's default of STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

You must then use the SET command to change the behavior after start up.
SET GLOBAL sql_mode='';

Suggested fix:
When the sql-mode variable does not exist in the option file, use the compiled default for the sever version. When the sql-mode variable exist, use the setting defined in sql-mode even if the value is a null string.

Edit synopsis to proper format.

Hello Kelvin,

Thank you for the report.
I can not repeat described behavior with reported and latest GA version.
I just suspect that there could be multiple conf files and value is read from wrong file. Please check if this is the case.

// 5.6.11

// confirmed sql-mode is set to '' in conf file

[root@cluster-repo mysql-5.6.11]# cat my.cnf |grep 'sql-mode'
sql-mode=''
[root@cluster-repo mysql-5.6.11]#
[root@cluster-repo mysql-5.6.11]# bin/mysql -u root -p --port=3306 test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.11 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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      |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

^^ It is taking up the sql-mode value from conf file

// To confirm again, just changed sql-mode value to 'NO_DIR_IN_CREATE' and confirmed it is indeed taking from conf file

[root@cluster-repo mysql-5.6.11]# cat my.cnf |grep 'sql-mode'
sql-mode='NO_DIR_IN_CREATE'
[root@cluster-repo mysql-5.6.11]#
[root@cluster-repo mysql-5.6.11]# bin/mysqld_safe --defaults-file=./my.cnf  --user=mysql &
[1] 14168
[root@cluster-repo mysql-5.6.11]# 130818 10:49:31 mysqld_safe Logging to '/tmp/5611/cluster-repo.err'.
130818 10:49:31 mysqld_safe Starting mysqld daemon with databases from /tmp/5611

[root@cluster-repo mysql-5.6.11]# bin/mysql -u root -p --port=3306 test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.11 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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_DIR_IN_CREATE |
+---------------+------------------+
1 row in set (0.00 sec)

mysql>

// 5.6.13

// from option file..sql mode set to '' and confirmed it is indeed taking from conf file

[root@cluster-repo mysql-5.6.13]# cat my.cnf |grep 'sql-mode'
sql-mode=''
[root@cluster-repo mysql-5.6.13]#
[root@cluster-repo mysql-5.6.13]# bin/mysqld_safe --defaults-file=./my.cnf  --user=mysql &
[1] 13413
[root@cluster-repo mysql-5.6.13]# 130818 10:40:44 mysqld_safe Logging to '/tmp/69780/cluster-repo.err'.
130818 10:40:44 mysqld_safe Starting mysqld daemon with databases from /tmp/69780

[root@cluster-repo mysql-5.6.13]# bin/mysql -u root -p --port=3306 test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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      |       |
+---------------+-------+
1 row in set (0.00 sec)

// from option file..just changed sql-mode to 'ALLOW_INVALID_DATES' and confirmed it is indeed taking from conf file

[root@cluster-repo mysql-5.6.13]# cat my.cnf |grep 'sql-mode'
sql-mode='ALLOW_INVALID_DATES'
[root@cluster-repo mysql-5.6.13]#
[root@cluster-repo mysql-5.6.13]# bin/mysqld_safe --defaults-file=./my.cnf  --user=mysql &
[1] 12918
[root@cluster-repo mysql-5.6.13]# 130818 10:39:31 mysqld_safe Logging to '/tmp/69780/cluster-repo.err'.
130818 10:39:31 mysqld_safe Starting mysqld daemon with databases from /tmp/69780

[root@cluster-repo mysql-5.6.13]# bin/mysql -u root -p --port=3306 test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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      | ALLOW_INVALID_DATES |
+---------------+---------------------+
1 row in set (0.00 sec)

//  Works even when passed with mysqld --sql-mode='' 

[root@cluster-repo mysql-5.6.13]# bin/mysqld_safe --defaults-file=./my.cnf --sql-mode='' --user=mysql &
[1] 11909
[root@cluster-repo mysql-5.6.13]# 130818 10:33:47 mysqld_safe Logging to '/tmp/69780/cluster-repo.err'.
130818 10:33:47 mysqld_safe Starting mysqld daemon with databases from /tmp/69780

[root@cluster-repo mysql-5.6.13]# bin/mysql -u root -p --port=3306 test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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      |       |
+---------------+-------+
1 row in set (0.00 sec)

Thanks,
Umesh

Ah! You are right, there is an unwanted my.cnf file elsewhere being read.

Thank you for the assistance.