How to set sql_mode in my.cnf in MySQL 8?

Question

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?

Answer 1

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.

Answer 2

Step 1. Check sql mode:

mysql -u root -p -e "SELECT @@GLOBAL.sql_mode;"

Step 2. Create a new configuration file under the /etc/mysql/conf.d/ directory:

sudo nano /etc/mysql/conf.d/disable_strict_mode.cnf 

Enter the text below on the editor:

[mysqld]

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Step 3. Restart MySQL:

sudo service mysql restart

Step 4. Confirm the change:

mysql -u root -p -e "SELECT @@GLOBAL.sql_mode;"

NO_AUTO_CREATE_USER SQL mode has been removed in MySQL 8.0, please check the reference manual for the full list of SQL modes.

Answer 3

Assuming that "restart safe" just means permanent, the syntax is:

sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

From Setting the SQL Mode:

To set the SQL mode at server startup, use the --sql-mode="modes" option on the command line, or sql-mode="modes" in an option file such as my.cnf (Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by commas.

If it doesn't work for your, perhaps you're placing it under the wrong section. For server settings that needs to be [mysqld], as in:

[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"

Answer 4

I just find that the option is not compatible with 'NO_AUTO_CREATE_USER' in my.cnf. That may be conflict with some setting.

The following line works for me in MySQL 8.

[mysqld] sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Answer 5

In recent versions of Ubuntu/Debian, in my case I am modifying the file /lib/systemd/system/mysql.service with:

ExecStart=/usr/sbin/mysqld --sql-mode=NO_ENGINE_SUBSTITUTION

After that, then only execute:

systemctl daemon-reload
systemctl restart mysql

Changes to *.cnf files do nothing.

Answer 6

I've not found a way around the problem using my.cnf. To be mysqld restart safe, I need to avoid having to do:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

The only way I found to get around this is to set an environment variable:

sudo systemctl set-environment MYSQLD_OPTS="--sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
sudo systemctl restart mysqld

Better solutions welcomed.

Answer 7

Editing my.cnf was not working with MySQL 8. So I developed a workaround. I am using MySQL 8 in Ubuntu 20.04

I created a file /etc/mysql/mysqlmode.sql:

SET GLOBAL sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

Next, I edited the /etc/mysql/my.cnf and added these lines of code at the end:

[mysqld]
init-file="/etc/mysql/mysqlmode.sql"

Answer 8

As for MacOs Catalina, I use MysqlWorkbench to switch the "persist" checkbox off in "Server/Status and../ =>System Variables and search sql_mode" Without that action, it ignores my.cnf settings/

that is mine: [mysqld] sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"