Bug #72583 SQL Mode shows deprecated settings even when not used
Submitted: 8 May 2014 18:32 Modified: 8 May 2014 19:02
Reporter: Dimitriy A Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.6.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE, SQL_MODE

[8 May 2014 18:32] Dimitriy A
Description:
As of MySQL 5.6.17, NO_ZERO_IN_DATE, NO_ZERO_DATE, and ERROR_FOR_DIVISION_BY_ZERO are deprecated. Adding them to sql_mode generates a warning.

However, after removing deprecated options from sql_mode in my.cnf and restarting the server, the options still show up.

How to repeat:
Updated sql_mode in my.cnf:
sql_mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

After server restart:
show global variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Suggested fix:
sql_mode variable should no longer show the deprecated options, since copying the variable's values and putting them into my.cnf file will result in warning message.
[8 May 2014 19:02] Miguel Solorzano
Thank you for the bug report.