3

After upgrading to MySQL 5.5 from the Remi-DB (CentOS 6) we've been getting an issue where every reboot our SQL server will set itself back to strict mode:

mysql> SELECT @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+

Then I run the command:

    mysql> SET GLOBAL sql_mode="";
Query OK, 0 rows affected (0.00 sec)

And that works fine for the session, but on restart this change is lost (I've also tried setting "SET SESSION" sqlmode as well.

I'd also like to note that I've tried this in the my.cnf files as well.

The only viable solution is to code an init file to run the SET GLOBAL sql_mode on restart but that's cumbersome, annoying, and unreasonable, there's got to be a way to make this work right off the bat.

I've tried a reinstall of the SQL server to no avail. (I did however pull back in the data from a backup of course as this is a production server.)

4 Answers 4

10

This bit me too on Percona Server v5.5.30-30.1-log.

See: http://bugs.mysql.com/bug.php?id=68643

and check for a /usr/my.cnf or similar file ($MYSQL_HOME/my.cnf) that might have been created by mysql_install_db.

This was overriding our sql_mode setting in all our other config files.

2
  • 1
    Can confirm having the same issue (and solution) with openSuse 13.1. Thanks!
    – davil
    Jun 17, 2014 at 9:57
  • Centos 7.2 and mysql 5.6 had the same proble. Please check that /usr/my.cnf Apr 21, 2016 at 11:36
3

Add this in your my.cnf and restart MySQL. Make sure there is no other line after that to overwrite it.

Replace sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

with

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

sometimes adding below lines might work too. Try above first and if it doesn't work try these.

sql-mode=""

or

sql-mode="TRADITIONAL"
2
  • As mentioned I've already tried this. Mar 6, 2013 at 18:47
  • sql-mode="" works for me. Both in /etc/my.cnf and in /usr/my.cnf. Apr 29, 2016 at 11:31
0

I know this is old, but in case anyone needs it:

I was getting this with MySQL Server 5.6 on Windows 7 x64.

Turns out it loads a my.ini file here:

C:\ProgramData\MySQL\MySQL Server 5.6\my.ini

Even though MySQL was installed and runs from here:

C:\Program Files\MySQL\MySQL Server 5.6

Changing the sql mode in ProgramData ini to the following, then restarting MySql solved the issue:

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 
0

If you disable mysql strict mode using below command, it will get revert after the server restart.

set global sql_mode='';

To set/disable it permanently, you have to edit the value in mysql conf file.

/etc/mysql/my.cnf or /etc/my.cnf based on your OS.

Find the following line:

sql_mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Replace it with the line below: If the line is not found, insert the line under the [mysqld] section (if there is no [mysqld] section, pls create it).

[mysqld] 
sql_mode= ""

Restart the MySQL service for the change to take effect.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .