58

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?

3
  • Not a big mysql guru, but also the client has its own section in configuration file, could it be that the client config is rewriting the server default? Mar 4, 2015 at 8:24
  • Thx, this solved my problem in homebrew osx mysql sql_mode="NO_ENGINE_SUBSTITUTION" gist.github.com/fhferreira/dd8a2a24000a562c87ab Jan 4, 2016 at 0:29
  • NOTE above, this was a bug that was fixed in our recent update to MySQL 5.6.27. Jan 18, 2016 at 21:37

14 Answers 14

55

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.

5
  • 3
    There appears to be a lot of variation of what it is supposed to be, and what actually works, as evidenced by these answers :D sometimes underscore or dash, sometimes quoted. (facepalm) Nov 2, 2015 at 17:56
  • 2
    For me, I noticed that there were issues parsing when it wasn't under the [mysqld] section. Hope this helps somebody. Apr 4, 2017 at 19:52
  • 1
    The server wouldn't even start wihtout me using [mysql]
    – Joey Pinto
    Jul 22, 2017 at 12:30
  • Definitely needed the [mysql] in my "mysql.cnf"! Sep 10, 2017 at 20:20
  • In my case, I wrote the configuration in /etc/mysql/my.cnf file and restart MySQL service using command: sudo /etc/init.d/mysql restart, and it worked. I'm using MySQL 5.7.26 in Ubuntu 18.04. Jul 15, 2019 at 10:36
12

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.

4
  • We have replication on, and that's defined in this my.cnf file, so this is definitely the configuration that is being used (but I'll check that it's not ovewriting somewhere else. As I mentioned to Riad, the documentation says to use underscore version and quotes. So if your suggestion is correct, the docs are wrong. I'll try it. Thanks. Mar 5, 2015 at 18:05
  • This doesn't work for me. I updated my question with more troubleshooting. Mar 5, 2015 at 18:49
  • Hi I have same issue then i updated /etc/my.cnf file but after some days server rewritten my file and create backup of old file. How to fix this issue ? Mar 25, 2019 at 5:45
  • @YogeshSaroya MySQL server never writes in that file. If it changed, something else must have changed it, like your package manager or whatever. Please ask this in a separate question.
    – fancyPants
    Mar 25, 2019 at 8:08
11

It should be:

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

instead of

[mysqld]
sql_mode="NO_ENGINE_SUBSTITUTION"

then restart mysqld service.

2
  • Thanks. I'll try that (although I think I did that originally. The syntax is definitely confusing, because the documentation says it should be sql-mode="NO_ENGINE_SUBSTITUTION" with dash not underscore and quotes? 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) --documentation Mar 5, 2015 at 18:01
  • I updated my question with more troubleshooting. Neither of these options work. Mar 5, 2015 at 18:48
7

Woks fine for me on ubuntu 16.04. path: /etc/mysql/mysql.cnf

and paste that

[mysqld]
#
# * Basic Settings
#
sql_mode = "NO_ENGINE_SUBSTITUTION"
2
  • How it's different from other answers here?
    – valignatev
    Aug 14, 2016 at 7:46
  • Its under [mysqld] Apr 4, 2017 at 20:12
5

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.

5

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
4

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
3

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

4
  • We only have one cnf file (not the others) and have edited the /etc/mysql/my.cnf However, the question is, what is the value supposed to be? There are competing values (see syntaxes I mentioned in the question), and for us, none of them work. What syntax did you use? Oct 6, 2015 at 17:00
  • Thanks, per the details in my original question, I've tried that already, and when restarting mysql it doesn't stick. But it's good confirmation that this is probably the right setting, and there must be something else preventing these settings from taking effect. Oct 12, 2015 at 17:27
  • so you've already searched for other my.cnf in your system and that's really the only one? Oct 13, 2015 at 17:51
  • @Ivniebelschuetz yes, it's crazy. Searched many many times in every location, file name search, etc. And in my main /etc/mysql/my.cnf this definition only happens once. It's always possible my testing was incorrect and one of these actually did work, but I don't think so. Oct 13, 2015 at 17:55
2

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.

1

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 = "ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
1

in Ubuntu 20+ and MySQL 8+ add in

/etc/mysql/my.cnf

or in

/etc/mysql/conf.d/mysql.cnf

[mysqld]
sql_mode = ""
innodb_autoinc_lock_mode = 0

settings like this, no more working

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

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.

0

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.

-2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.