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?

Uphroe

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.

Worsley

For reference, here's the documentation for the default sql_mode for MySQL 8: dev.mysql.com/doc/refman/8.0/en/… – Ghibelline

Remember to put it under the right section! [mysqld] – Modulus

Since there are conflicting usages in the examples on this page, it's worth noting that 'sql-mode' and 'sql_mode' both have the same effect and are both valid when used in MySQL config files in 8+ – Prostrate

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.

Susysuter

I can confirm that this works on Ubuntu 20.04 and MySQL 8.0. – Quackery

This was what worked for me on digital ocean after a long long search. Thanks man. You saved the day. – Illhumored

this worked on ubuntu 22.04 as well, for mysql 8 – Armilda

Perfect. Works on CentOS 9 running MySQL 8. Slight differences with config files location /etc/my.cnf.d/ and restart command systemctl restart mysqld.service – Under

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"

Approver

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"

Pled

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.

Spectacled

Check your my.cnf file for redundant lines that set the sql_mode. The last line in the file that sets that variable takes priority. So it might seem like changes to the file do nothing, what is really happening might be that your change is overridden by a later line. – Worsley

There is some limitations to the behaviour of sql_mode in latest Linux & MySQL8.x environments. – Fipple

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.

Uphroe

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"

Confucian

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"

Refugiorefulgence