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.
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.