MySQL Bugs: #69743: wrong default sql

Bug #69743	wrong default sql_mode in doc
Submitted:	15 Jul 2013 8:34	Modified:	23 Jul 2013 13:33
Reporter:	martin fuxa	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.6	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
imho default sql_mode is now (in 5.6.12)
NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
but I cant't test it now

changed in 5.6.8 RC
https://blogs.oracle.com/supportingmysql/entry/mysql_server_5_6_default

but DOC says
http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html
The default SQL mode in MySQL 5.6.6 and later is NO_ENGINE_SUBSTITUTION

http://dev.mysql.com/doc/refman/5.6/en/faqs-sql-modes.html#qandaitem-B-3-1-7
As of MySQL 5.6.5, the default SQL mode is NO_ENGINE_SUBSTITUTION. Before 5.6.5

btw 5.6.5 OR 5.6.6?

How to repeat:
make new install 5.6.12 and run
show variables like 'sql_mode';

if STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION 
 doc is wrong
else
 sorry for report
fi

Hello Martin,

I did a quick test, works as expected and as documented.

// Conf details

[root@ushastry mysql-5.6.12-release]# more my.cnf 
[mysqld]

basedir = /home/ushastry/mybuilds/mysql-5.6.12-release
datadir = /tmp/5612
port = 3306
[root@ushastry mysql-5.6.12-release]# bin/mysqld_safe --defaults-file=my.cnf --user=root &

[root@ushastry mysql-5.6.12-release]# bin/mysql -u root --port=3306 --protocol=tcp
..
..
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.6.12-debug |
+--------------+
1 row in set (0.00 sec)

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

Also, http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sql_mode

The default SQL mode in MySQL 5.6.6 and later is NO_ENGINE_SUBSTITUTION; in MySQL 5.6.5 and earlier, it was empty (no modes set). 

>= 5.6.6, Default is NO_ENGINE_SUBSTITUTION
<= 5.6.5, Default is ''

But in the template files, however the default my-new.cnf/my-default.cnf file sql_mode contains "sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES"

// 

# more my-new.cnf |grep sql_mode
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

Thanks,
Umesh

I found some testing server, and I can't approve your test!

rm -rf /etc/my.cnf /var/lib/mysql/
(and I double-check root home for local cnf)
rpm -i MySQL-server-5.6.12-2.el6.x86_64.rpm
# client + shared + -shared-compat are already installed, all 5.6.12
mysql_install_db
chown -R mysql:mysql /var/lib/mysql/ ; chmod go+x /var/lib/mysql/
/etc/init.d/mysql start

mysql -u root

show variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |

select version();
+-----------+
| version() |
+-----------+
| 5.6.12    |

This is at
CentOS release 6.4 (Final)
Linux ho13 2.6.32-279.19.1.el6.x86_64 #1 SMP Wed Dec 19 07:05:20 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux

First case was another Centos 5 server (= another RPM) installed/upgraded in the following
Jun 05 21:41:28 Installed: MySQL-server-5.6.11-2.rhel5.x86_64
Jul 09 21:53:23 Installed: MySQL-server-5.6.12-1.rhel5.x86_64

all are official RPM builds.

Hello,

That behavier maybe caused /usr/my.cnf which is generated by mysql_install_db.

http://dev.mysql.com/doc/refman/5.6/en/mysql-install-db.html

This is described behavier but very unkind, I thought.
Would you click "Affects Me" following feature request?

http://bugs.mysql.com/bug.php?id=68643

Regards,

Hello Martin,

The reason why my test(server built from source and didn't include anything in my.cnf and let it default to pickup) were different than yours because of my.cnf generated by mysql_install_db during installation..

[ushastry@cluster-repo setups]$ sudo rpm -ivh --force MySQL-server-5.6.12-2.el6.x86_64.rpm
Preparing...                ########################################### [100%]
   1:MySQL-server           ########################################### [100%]
[ushastry@cluster-repo setups]$ sudo rpm -ivh MySQL-server-5.6.12-2.el6.x86_64.rpm

[ushastry@cluster-repo setups]$ cat /usr/my.cnf|grep sql_mode
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

This behavior is documented in http://dev.mysql.com/doc/refman/5.6/en/mysql-install-db.html

Please see - Bug #68643 sql_mode is unkind in my.cnf created by mysql_install_db

confirmed as write tsubasa tanaka, maybe duplicate to #68643.

But I have right, after default installation sql_mode is 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
no matter of MySQL-server defaults or /usr/my.cnf.
Who care about 100 lines of installation verbose output.

Maybe in DOC would be
After default installation, config file is created as /usr/my.cnf with
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
what you say for this?

I agree that we need some more information/clarification on this in the manual to avoid confusion over default sql_mode value and with that of changed value after rpm etc installation.

Also, http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sql_mode

As per manual - "The default SQL mode in MySQL 5.6.6 and later is NO_ENGINE_SUBSTITUTION; in MySQL 5.6.5 and earlier, it was empty (no modes set)" 

>= 5.6.6, Default is NO_ENGINE_SUBSTITUTION
<= 5.6.5, Default is ''

But, as per http://dev.mysql.com/doc/refman/5.6/en/mysql-install-db.html

"The exception is that the file changes the sql_mode system variable from its default of NO_ENGINE_SUBSTITUTION to also include STRICT_TRANS_TABLES..."

A couple comments:

* The change in sql_mode default occurred in 5.6.6. I'll correct the FAQ entry that says 5.6.5.

* "Default value" means the default as the server considers it without any settings that might be made through command-line options or my.cnf files. That kind of setting is not a default, it's a *change* from the default.

Nevertheless, it could be clearer that the default might be changed by settings written to a my.cnf file by an installation-related program (which makes it appear that the default differs from the built-in default). I'll work on this.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

* Fixed FAQ entry version
* Updated http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html and related sections to point out that installation programs might change the default by writing a setting to my.cnf.

See also Bug #71600.