How to set sql_mode in my.cnf in MySQL 8?
mysql, mysql-8.0, sql-mode
Solution
The SQL mode `NO_AUTO_CREATE_USER` was removed in MySQL 8.0, and it's no longer recognized.
https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-deprecations says:
The following features related to account management are removed:
- Using `GRANT` to create users. Instead, use `CREATE USER`. Following this practice makes the `NO_AUTO_CREATE_USER` SQL mode immaterial for `GRANT` statements, so it too is removed.
Change your sql_mode to "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION". I tested this on my sandbox instance of 8.0.11 and it worked.
Either spelling of sql-mode or sql_mode are both fine.
Using quotes or omitting quotes are both fine.
Problem
I'm running MySQL 8.0.11 community version. I need to set sql_mode to exclude ONLY_FULL_GROUP_BY in my.cnf so that it's restart safe. I tried the following variants: ``` sql_mode= STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" ``` They all fail in the same manner whether the variable is named 'sql_mode' or 'sql-mode': ``` mysqld --verbose --help | grep "sql[-_]mode" 2018-06-19T15:22:51.667734Z 0 [ERROR] [MY-011071] [Server] /usr/sbin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode' --sql-mode=name Syntax: sql-mode=mode[,mode[,mode...]]. See the manual 2018-06-19T15:22:51.675498Z 0 [ERROR] [MY-010119] [Server] Aborting ``` sql-mode It would seem that mysqld process my.cnf and converts 'sql_mode' or 'sql-mode' to 'sql_mode', which then it rejects! The question is how to get around this?