Bug #85940 MySQL 5.6.36 changes default sql_mode

• Submitted: 13 Apr 2017 22:16
• Modified: 7 May 2017 1:15
• Reporter: Zach Barahal
• Status: Verified
• Category: MySQL Server: Options
• Severity: S3 (Non-critical)
• Version: 5.6.36
• OS: Debian
• CPU Architecture: Any
• Tags: SQL_MODE, strict

[13 Apr 2017 22:16] Zach Barahal

Description:
MySQL 5.6.36 has sql_mode NO_ENGINE_SUBSTITUTION by default.

5.6.35 has sql_mode STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION by default.

We expect DB settings to remain consistent between patch versions. 

How to repeat:
Create a clean instance of 5.6.35 and run SELECT @@sql_mode;
Create a clean instance of 5.6.36 and run SELECT @@sql_mode;

Observe the difference.

We reproduced using docker

docker pull mysql:5.6.36
docker pull mysql:5.6.35

We also verified that the docker repo https://github.com/docker-library/mysql is using the latest version of MySQL 5.6

Suggested fix:
The behavior should made consistent with 5.6.35. We assume that this behavior changing on a patch version is not intentional.

[14 Apr 2017 5:23] MySQL Verification Team

Windows binaries not affected:

c:\tmp\mysql-5.6.36-winx64>bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

c:\tmp\mysql-5.6.35-winx64>bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

[7 May 2017 1:10] MySQL Verification Team

root@debian871:/home/miguel# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

[7 May 2017 1:15] MySQL Verification Team

Thank you for the bug report.