https://torbjornzetterlund.com/wp-content/uploads/2013/08/mysql-logo-960x600_c.png

Turn off MySQL strict mode


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 were blank ‘; and that the field type in the table was set to 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”:

1.  Look for the following line:

sql_mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

2.  Change it to:

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Then add

sql-mode="" (Blank)
  1. Restart the MySQL service.

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

Menu