I am trying to set sql_mode in MySQL but it throws an error. Command: set global sql_mode='NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLE','NO_AUTO_CREATE_USER','NO_ENGINE_SUBSTITUTION' Is this not the proper way to set multiple modes? What are the advantages of setting session and global modes? Which is preferred? I have different users trying to update the database with different UNC values and instead of setting the session mode to NO_BACKSLASH_ESCAPES , I though it would make sense to set a global mode for this. Does this make sense? Please let me know. Thanks.

Reputation: 6546

Setting global sql_mode in MySQL

I am trying to set sql_mode in MySQL but it throws an error.

Command:

set global sql_mode='NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLE','NO_AUTO_CREATE_USER','NO_ENGINE_SUBSTITUTION'

Is this not the proper way to set multiple modes?
What are the advantages of setting session and global modes?
Which is preferred?

I have different users trying to update the database with different UNC values and instead of setting the session mode to NO_BACKSLASH_ESCAPES, I though it would make sense to set a global mode for this. Does this make sense?

Please let me know.

Thanks.

Upvotes: 137

Answers (16)

mitsu

Reputation: 453

If you want a persistent setup on Windows, modify my.ini instead of my.cnf. It is usually located in a path like this: C:\ProgramData\MySQL\MySQL Server 8.0

If there is a sql-mode line, change that line to this. If there is no line, add one.

sql-mode='NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLE','NO_AUTO_CREATE_USER','NO_ENGINE_SUBSTITUTION'

Note that it is sql-mode, not sql_mode. Hyphens, not underscores. After editing my.ini, restart the MySQL service to reflect the changes.

Upvotes: 0

vdidxho

Reputation: 355

To do this in Azure, go to the MySQL resource in the portal, server parameters, search sql_mode. Then open the drop down and remove the selection for the parameter in question. Finally click save.

Upvotes: 0

cyberrspiritt

Reputation: 946

Updating this for users Using MAMP PRO {works with MAMP users too}. Because I seem to have got stuck on finding a solution for this, but people recommended I should edit the my.cnf file in the /Applications/MAMP/tmp/mysql/my.cnf folder which does not work because it gets reset after every restart of mysql server.

Referring this document:

The configuration file “my.cnf” of MySQL can be found here: “/Applications/MAMP/tmp/mysql/my.cnf”. Please note: Editing this file does NOT work as it will be overwritten every time MySQL is restarted by MAMP PRO with a “my.cnf” file that is created from the MySQL template. You must edit this template (menu File > Open Template > MySQL (my.cnf) > 5.7.30) to modify the MySQL configuration. Manually adding “my.cnf” files to other locations is not recommended. Every configuration aspect can be handled with the MySQL template.

Once this is done, add the following in the my.cnf file:

[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

Restart the Mysql Server. That should do the trick.

Upvotes: 1

Cristiana Chavez

Reputation: 11519

In my case mysql and ubuntu 18.04

I set it permanently using this command

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

Append the line after the configuration. See example highlighted in the image below.

sql_mode = ""

Note :You can also add different modes here, it depends on your need NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

See Available sql modes reference and Documentation

Then save. After saving you need to restart your mysql service, follow the command below:

sudo service mysql restart

Hope this helps :-)

Upvotes: 3

Ankit Jindal

Reputation: 4030

Check the documentation of sql_mode

Method 1:

Check default value of sql_mode:

SELECT @@sql_mode //check current value for sql_mode

SET GLOBAL sql_mode = "NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

Method 2:

Access phpmyadmin for editing your sql_mode

Login on phpmyadmin and open localhost
Top on Variables present on the top in menu items and search out for sql mode
Click on edit button to modify sql_mode based on your requirements
Save the changes

Restart server after executing above things

Upvotes: 5

Cobra_Fast

Reputation: 16061

I just had a similar problem where MySQL (5.6.45) wouldn't accept sql_mode from any config file.

The solution was to add init_file = /etc/mysql/mysql-init.sql to the config file and then execute SET GLOBAL sql_mode = ''; in there.

Upvotes: 1

Eric Korolev

Reputation: 743

Access the database as the administrator user (root maybe).

Check current SQL_mode

mysql> SELECT @@sql_mode;

To set a new sql_mode, exit the database, create a file

nano /etc/mysql/conf.d/<filename>.cnf

with your sql_mode content

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

Restart Mysql

mysql> sudo service mysql stop
mysql> sudo service mysql start

We create a file in the folder /etc/mysql/conf.d/ because in the main config file /etc/mysql/my.cnf the command is written to include all the settings files from the folder /etc/mysql/conf.d/

Upvotes: 7

Vineet Kumar

Reputation: 353

For Temporary change use following command

set global sql_mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

For permanent change : go to config file /etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf and add following lines then restart mysql service

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

Upvotes: 7

Serhii Popov

Reputation: 3804

For someone who googling this error for MySQL 8.

MySQL 8.0.11 remove the 'NO_AUTO_CREATE_USER' from sql-mode.

MySQL 5.7: 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 deprecated. MySQL 8.0.11: 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.

Taken from here

So, your sql_mode can be like this:

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Or if you're using Docker you can add next command to docker-compose.yml

  mysql:
    image: mysql:8.0.13
    command: --sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    ports:
      - 13306:${MYSQL_PORT}

Upvotes: 31

ASHOK MANGHAT

Reputation: 35

If someone want to set it only for the current session then use the following command

set session sql_mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Upvotes: 0

Chadwick Meyer

Reputation: 7331

BTW, if you set globals in MySQL:

SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

This will not set it PERMANENTLY, and it will revert after every restart.

So you should set this in your config file (e.g. /etc/mysql/my.cnf in the [mysqld] section), so that the changes remain in effect after MySQL restart:

Config File: /etc/mysql/my.cnf

[mysqld] 
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

UPDATE: Newer versions of Mysql (e.g. 5.7.8 or above) may require slightly different syntax:

[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

Make sure that there is a dash between sql-mode not an underscore, and that modes are in double quotes.

Always reference the MySQL Docs for your version to see the sql-mode options.

Upvotes: 286

Shadab Khan Zed

Reputation: 14

set global sql_mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Upvotes: -3

shashikant parmar

Reputation: 47

In my case i have to change file /etc/mysql/mysql.conf.d/mysqld.cnf change this under [mysqld]

Paste this line on [mysqld] portion

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Upvotes: 1

Devraj Gupta

Reputation: 339

Copy to Config File: /etc/mysql/my.cnf OR /bin/mysql/my.ini

[mysqld]
port = 3306
sql-mode=""

MySQL restart.

Or you can also do

[mysqld]
port = 3306
SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

MySQL restart.

Upvotes: 18

Yashrajsinh Jadeja

Reputation: 1809

Setting sql mode permanently using mysql config file.

In my case i have to change file /etc/mysql/mysql.conf.d/mysqld.cnf as mysql.conf.d is included in /etc/mysql/my.cnf. i change this under [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"

just removed ONLY_FULL_GROUP_BY sql mode cause it was causing issue.

I am using ubuntu 16.04, php 7 and mysql --version give me this mysql Ver 14.14 Distrib 5.7.13, for Linux (x86_64) using EditLine wrapper

After this change run below commands

sudo service mysql stop
sudo service mysql start

Now check sql modes by this query SELECT @@sql_mode and you should get modes that you have just set.

Upvotes: 41

JPro

Reputation: 6546

I resolved it.

the correct mode is :

set global sql_mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Upvotes: 39

Setting global sql_mode in MySQL

Answers (16)

Related Questions