How to make sql-mode NO ENGINE SUBSTITUTION permanent in MySQL my cnf

Question

UPDATE FIXED 1 18 15  After we recently updated to MySQL 5 6 27  from the Ubuntu repo   this option now works  So this appears to have been a problem with the previous version of MySQL   ORIGINAL QUESTION  With a new upgrade to MySQL  5 6 20   updates and inserts fail unless I set sql-mode to NO ENGINE SUBSTITUTION   Thanks to the documentation  I can run the following from mysql terminal and that fixes the problem  temporarily    SET GLOBAL sql mode    NO ENGINE SUBSTITUTION   SET SESSION sql mode    NO ENGINE SUBSTITUTION      But the next time MySQL restarts  these settings are lost    So I have tried to make that permanent by editing  etc mysql my cnf  on my standard server running Ubuntu 12 04 5 LTS   and adding the config settings that the documentation says should be added    mysqld  sql-mode  NO ENGINE SUBSTITUTION    Alternative Syntaxes for Testing  Just for testing purposes  I have also tried the following formats  which do not cause errors when restarting MySQL  but they do not affect the setting      dash no quotes sql-mode NO ENGINE SUBSTITUTION   underscore no quotes sql mode NO ENGINE SUBSTITUTION   underscore and quotes sql mode  NO ENGINE SUBSTITUTION    Nothing works  After restart this setting is lost and I have to run the commands manually again from mysql terminal to make saving work again    Alternative Locations   I know  etc mysql my cnf is being referenced because we have replication defined in this file  and that is working  There is not another identical setting in this file that is overwriting it    I get a list of the config files that are being referenced by running this from the command line   mysqld --help --verbose   I see a line that reads   Default options are read from the following files in the given order   etc my cnf  etc mysql my cnf  usr etc my cnf    my cnf    This is the default location it  looks  for files  it doesn t mean that it actually found a file there  e g  my server doesn t have  etc my cnf   usr etc my cnf or    my cnf   So it looks like my config in  etc mysql my cnf is the only file mysql is referencing  and therefore this setting is not being overwritten    Logical Conclusion of Testing  Logically then  it seems the syntax is not correct or is being ignored for some other reason  Any other ideas

User · Answer

For me both keys for sql-mode worked  Whether I used    dash no quotes sql-mode NO ENGINE SUBSTITUTION   or    underscore no quotes sql mode NO ENGINE SUBSTITUTION   in the my ini file made no difference and both were accepted  as far as I could test it   What actually made a difference was a missing newline at the end of the my ini file   So everyone having problems with this or similar problems with my ini my cnf  Make sure there is a blank line at the end of the file   Tested using MySQL 5 7 27

User · Answer

My problem was that I had spaces in between the options on 5 7 20  Removing them so the line looked like   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

On Linux Mint 18 the default config file that has the sql-mode option set is located here     usr my cnf   And relevant line is   sql mode NO ENGINE SUBSTITUTION STRICT TRANS TABLES   So You can set there   If not sure what config file has such option You can search for it     sudo find   -iname   my cnf     And get a list    var lib dpkg alternatives my cnf  usr my cnf  etc alternatives my cnf  etc mysql my cnf fallback  etc mysql my cnf

User · Answer

Woks fine for me on ubuntu 16 04  path   etc mysql mysql cnf  and paste that   mysqld        Basic Settings   sql mode    NO ENGINE SUBSTITUTION

User · Answer

It should be    mysqld  sql mode NO ENGINE SUBSTITUTION STRICT TRANS TABLES   instead of    mysqld  sql mode  NO ENGINE SUBSTITUTION    then restart mysqld service

User · Answer

It was making me crazy also until I realized that the paragraph where the key must be is  mysqld  not  mysql  So  for 10 3 22-MariaDB-1ubuntu1  my solution is  in  etc mysql conf d mysql cnf  mysqld  sql mode    quot ERROR FOR DIVISION BY ZERO NO AUTO CREATE USER NO ENGINE SUBSTITUTION quot

User · Answer

The solution is pretty easy    Searched for it for a while and it turns out that you just have to edit 2 config-files     usr my cnf  etc mysql my cnf   in both files you ll have to add    mysqld      sql mode NO ENGINE SUBSTITUTION   At least  that s what s working for 5 6 24-2 deb sury org precise 2

User · Answer

I am running WHM 10 2 15-MariaDB   To permanently disable strict mode first find out which configuration file our installation prefers  For that  we need the binary   s location     which mysqld  usr sbin mysqld   Then  we use this path to execute the lookup      usr sbin mysqld --verbose --help   grep -A 1  Default options   Default options are read from the following files in the given order   etc my cnf  etc mysql my cnf    my cnf   We can see that the first favored configuration file is one in the root of the etc folder but that there is a second  cnf file hidden  -    my cnf  Adding the following to the    my cnf file permanently disabled strict mode for me  needs to be within the mysqld section     mysqld  sql mode NO ENGINE SUBSTITUTION   I found that adding the line to  etc my cnf had no effect at all apart from sending me crazy

User · Answer

Your server may read a different my cnf than the one you re editing  unless you specified it when starting mysqld     From the MySQL Certification Study Guide      The search order includes two general option files   etc my cnf and    MYSQL HOME my cnf  The second file is used only if the MYSQL HOME   environment variable is set  Typically  you seet it to the MySQL   installation directory   The mysqld safe script attempts to set   MYSQL HOME if it is not set before starting the server     The   option file search order also includes    my cnf  that is the home   directory   This isn t an especially suitable location for server   options   Normally  you invoke the server as mysql  or as root   with a --user mysql option  The user-specific file read by the   server would depend on which login account you invoke it from    possibly leading to inconsistent sets of options being used     Another possibility is of course  that your sql-mode option gets overwritten further down in the same file  Multiple options have to be separated by    in the same line    P S   And you need the quotes  IIRC  Now that you ve tried it without quotes  I m pretty sure  you re editing the wrong file  since MySQL doesn t start when there s an error in the option file   P P S   Had a look at my config files again  there it s    mysqld  sql mode    NO ENGINE SUBSTITUTION    and it s working

User · Answer

If you re using mariadb  you have to modify the mariadb cnf file located in  etc mysql conf d    I supposed the stuff is the same for any other my-sql based solutions

User · Answer

Fixed  Server version  10 1 38-MariaDB - mariadb org binary distribution  Go to  C  xampp mysql bin open my ini in notepad and find  mysqld   line number 27  then after this line line no 28  just type  skip-grant-tables  save the file and then reload the phpmyadmin page It worked for me

User · Answer

Just to add my configuration to the mix  I m using MySQL 5 7 8 which has the same strict sql mode rules by default    I finally figured the following working in my  etc mysql my conf    mysqld  sql-mode  STRICT TRANS TABLES NO ENGINE SUBSTITUTION     i e  dash  not underscore and quotes around the value    I have NO other my conf files other than  etc mysql my conf There are some extra config includes being loaded from  etc mysql conf d  but they are blank    And that seems to work for me

User · Answer

For me it was a permission problem   enter    mysqld --verbose --help   grep -A 1  Default options     Warning  World-writable config file   etc mysql my cnf  is ignored   So try to execute the following  and then restart the server  chmod 644   etc mysql my cnf    It will give mysql access to read and write to the file

[mysql] How to make sql-mode="NO_ENGINE_SUBSTITUTION" permanent in MySQL my.cnf

Examples related to mysql