Why none of the sql_mode(s) are recognized by MySQL?

Question

When trying to launch MySQL from the terminal using mysql -u <username> -p<password> I get the following error:

mysql: [ERROR] unknown variable 'sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Out of the possible three places that MySQL is looking for the .cnf file (found them by running sudo /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"), only /etc/mysql/my.cnf exist. In it there is only a directory including !includedir /etc/mysql/conf.d/. There, in /etc/mysql/conf.d/mysql.cnf there are the following lines:

 [mysql]
 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Looked around for answers, couldn't find any. Tried, one by one, to rule out specific "bad mode" from the list of modes, they're all unrecognized.

Only by completely commenting out this line, I was able to login and launch MySQL.

Does anyone knows what the problem might be?

---

Platform and OS aspects:

• The MySQL version I am using is 5.7.17
• The operating system is Lubuntu version 16.04, 4.7.3-generic, x86_64

Answer 1

Your problem is very simple

You have the following

[mysql]
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 is not an option of the mysql client. That's why the error message is unknown variable.

Note how sql_mode and sql-mode do not appear in the mysql client options:

$ mysql --help | grep mode
                      Automatically switch to vertical output mode if the
                      work in batch mode. Disable with --disable-pager. This
  --ssl-mode=name     SSL connection mode.
  --ssl               Deprecated. Use --ssl-mode instead.
                      Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
                      also. Does not work in batch mode. Disable with
  --binary-mode       By default, ASCII '\0' is disallowed and '\r\n' is
                      \C and DELIMITER, in non-interactive mode (for input
                      expired password sandbox mode.
binary-mode                       FALSE

SOLUTION

sql_mode is an option for mysqld.

Simply change the group header to [mysqld]

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

You don't have to restart mysql. Just login and run

mysql> SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

GIVE IT A TRY !!!