Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
Can confirm having the same issue (and solution) with openSuse 13.1. Thanks! –  davil Jun 17 at 9:57

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"
share|improve this answer
    
As mentioned I've already tried this. –  Matthew Salsamendi Mar 6 '13 at 18:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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