setting global sql mode in mysql

Question

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 preffered  I have different users trying to update the database with different UNC values and insted od setting the session mode to  NO BACKSLASH ESCAPES   I though it would make sense to et a gloabl mode for this  Does this make sense   Please let me know   Thanks

User · Answer

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    quot NO BACKSLASH ESCAPES STRICT TRANS TABLE NO AUTO CREATE USER NO ENGINE SUBSTITUTION quot    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

User · Answer

set global sql mode  NO BACKSLASH ESCAPES STRICT TRANS TABLE NO AUTO CREATE USER NO ENGINE SUBSTITUTION

User · Answer

I resolved it   the correct mode is    set global sql mode  NO BACKSLASH ESCAPES STRICT TRANS TABLE NO AUTO CREATE USER NO ENGINE SUBSTITUTION

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Access the database as the administrator user  root maybe    Check current SQL mode  mysql gt  SELECT   sql mode    To set a new sql mode  exit the database  create a file  nano  etc mysql conf d  lt filename gt  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 gt  sudo service mysql stop mysql gt  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

User · Answer

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

User · Answer

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    quot  quot    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  -

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] setting global sql_mode in mysql

Examples related to mysql