• 131
• 255
• 14

Turn off MySQL strict mode

by bernt & torsten

I recently run into a problem with a new server, some features of the application I was using was not working anymore.

Finding the problem

The application I’m using is built with CodeIgniter, in CodeIgniter, there is an error logging feature, so I switch that one to see what type of error I was getting.

To my surprise I was getting SQL errors on Insert and Updates, looking closer I saw that the SQL statement for two specific fields was blank ‘; and that the field type in the table was set to an integer.

Researching

I googled to find out what the issue could be, and that’s when I found a statement that the default value for MySql for 5.6 is set to strict mode.

I checked the new server, and the MySql on that server is version 5.6, the fix of removing the strict mode is straight forward. Make the following changes in the “my.ini/my.cnf”:

Look for the following line:

sql_mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Change it to:

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Then add

sql-mode="" (Blank)

• Restart the MySQL service.
• You could also do the following, it’s just temporarily.

mysql> SET GLOBAL sql_mode = '';

If you using this method, you would need to add it each time you restart MySQL or server reboot.