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

change sql mode phpmyadmin
mysql 8 set sql_mode
how to check sql mode in mysql
turn off mysql strict mode phpmyadmin
php mysql set sql_mode
sql_mode=only_full_group_by
set sql_mode=no_auto_value_on_zero
mysql sql_mode restart

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?

MySQL 8.0 Reference Manual :: 5.1.11 Server SQL Modes, cnf (Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by commas. To clear the SQL mode explicitly, set it to an  To change the SQL mode at runtime, set the global or session sql_mode system variable using a SET statement: SET GLOBAL sql_mode = ' modes '; SET SESSION sql_mode = ' modes '; Setting the GLOBAL variable requires the SYSTEM_VARIABLES_ADMIN privilege (or the deprecated SUPER privilege) and affects the operation of all clients that connect from that time on.

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"

MySQL 5.7 Reference Manual :: 5.1.10 Server SQL Modes, contain default options or values that will be loaded on startup (when the server is first started). 1 row in set (0.00 sec) Copy this string, then exit the MySQL prompt. mysql> exit; Bye Now, ensure the SQL Mode is set permanently. Add or remove the SQL modes from the string you gathered in the last step in your favorite text editor. You'll need to edit /etc/my.cnf and restart MySQL or MariaDB to ensure the change takes effect. # nano /etc/my.cnf

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.

Location of MySQL Configuration File my.cnf or my.ini on Windows , line, save the file and restart the MYSQL57 service. Via the MySQL command line client, I am trying to set the global mysql_mode: SET GLOBAL sql_mode = TRADITIONAL; This works for the current session, but after I restart the server, the sql_mode goes back to its default: '', an empty string. How can I permanently set sql_mode to TRADITIONAL? If relevant, the MySQL is part of the WAMP package

How to turn on/off MySQL strict mode in localhost (xampp)?, You can set the default SQL mode (for mysqld startup) with the --sql-mode option. Using the statement SET [GLOBAL|SESSION] sql_mode=' modes ' , you can change the settings from within a connection, either locally to the connection, or to take effect globally. To find my.cnf on Windows system, first open the command prompt with the help of shortcut key Windows + R (run). The snapshot is as follows − Type “services.msc” on command prompt and press ENTER as shown in the following screenshot − Now, a new wizard will open. The snapshot is as follows − Now, search for MySQL. The snapshot is as

A.3 MySQL 8.0 FAQ: Server SQL Mode, Setting the SQL mode permanently. Open the MySQL configuration file my.cnf ( my.ini on Windows) in a text editor. In this example, we use the  I have upgraded my system and have installed MySql 5.7.9 with php for a web application I am working on. I have a query that is dynamically created, and when run in older versions of MySql it works

How to change the SQL mode in MySQL – Plesk Help Center, The default value of the variable sql_mode in MySQL 8 is as shown below SET GLOBAL sql_mode = "STRICT_TRANS_TABLES On my system having Ubuntu 18.04 LTS and MySQL 8, I found this file at /etc/ mysql /my.cnf  MySQL master slave replication: Submitted: 22 Jun 15:37: Modified: 23 Jun 5:45 provide my.cnf for master and slave, script to create initial data and script that