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

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.

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

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"
  • As mentioned I've already tried this. – Matthew Salsamendi Mar 6 '13 at 18:47
  • sql-mode="" works for me. Both in /etc/my.cnf and in /usr/my.cnf. – Brian Haak Apr 29 '16 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" 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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