Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

mysql

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?

like image 814
Chadwick Meyer Avatar asked Mar 04 '15 08:03

Chadwick Meyer


People also ask

How do I permanently change sql mode in MySQL?

If you are using Windows Operating System, check your directory my. cnf or my. ini file. sql_mode="TRADITIONAL".

How do I enable sql mode?

Enable SQLCMD Scripting by Default To turn SQLCMD scripting on by default, on the Tools menu select Options, expand Query Execution, and SQL Server, click the General page, and then check the By default open new queries in SQLCMD Mode box.


2 Answers

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.

like image 171
Mikepote Avatar answered Oct 19 '22 18:10

Mikepote


It should be:

[mysqld] sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

instead of

[mysqld] sql_mode="NO_ENGINE_SUBSTITUTION" 

then restart mysqld service.

like image 39
Riad Avatar answered Oct 19 '22 17:10

Riad