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?

score 51 · Answer 1 · answered Jan 02 '19 at 18:03

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.

For reference, here's the documentation for the default sql_mode for MySQL 8: https://dev.mysql.com/doc/refman/8.0/en/faqs-sql-modes.html#faq-mysql-what-default-mode — jzsf-sz, Sep 01 '20 at 03:13

Domenico · Answer 2 · 2020-12-21T23:38:01.477

12

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.

edited Dec 21 '20 at 23:38

answered Dec 21 '20 at 21:17

Domenico

531
4
5

3

I can confirm that this works on Ubuntu 20.04 and MySQL 8.0. – mvidovic Jan 17 '21 at 12:14
2

This was what worked for me on digital ocean after a long long search. Thanks man. You saved the day. – Chidozie Duru May 04 '22 at 00:21
this worked on ubuntu 22.04 as well, for mysql 8 – Vinod Kumar Jan 11 '23 at 18:52
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` – camslice Apr 20 '23 at 13:17

score 9 · Answer 3 · answered Jan 02 '19 at 14:21

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"

score 3 · Answer 4 · edited Jul 08 '20 at 00:41

3

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.

Example execution

edited Jul 08 '20 at 00:41

piraces

1,320
2
18
42

answered Jul 07 '20 at 17:23

Marek Wesołowski

29
2

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. – Bill Karwin Jul 07 '20 at 17:26
There is some limitations to the behaviour of sql_mode in latest Linux & MySQL8.x environments. – Marek Wesołowski Jul 07 '20 at 17:44

score 2 · Answer 5 · answered Jan 02 '19 at 14:09

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.

score 2 · Answer 6 · edited Oct 03 '20 at 16:36

2

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"

edited Oct 03 '20 at 16:36

jnovack

7,629
2
26
40

answered Oct 03 '20 at 15:01

gmurimi

29
2

score 1 · Answer 7 · answered Nov 30 '20 at 02:23

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"

score -1 · Answer 8 · answered Sep 29 '20 at 17:36

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"

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

8 Answers8

Linked

Related